Help me solve this problem please!!!

[johno]

here is the prob, i want whichever is selected, whether CustomerId, Name, or Surame to be displayed in a list, upon displayin in the list i want to select the result of the query and the corresponding information should come up with some other information. firstly it is displayin in the list, but i want when i select any of them from a list all the info on the person comes up.

if i select customer id, when i select it it should display customer id, name, and surname
and so on for the other querries.





If Combo2.Text = "CustomerID" Then
List2.Clear
sql = "SELECT CustomerID FROM Customer"

rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

While rs.EOF = False
List2.AddItem rs!CustomerID
rs.MoveNext
Wend
rs.Close

ElseIf Combo2.Text = "Name" Then
List2.Clear
sql = "SELECT DISTINCT Name from Customer"

rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

While rs.EOF = False
List2.AddItem rs!Name
rs.MoveNext

Wend
rs.Close
ElseIf Combo2.Text = "Surname" Then
List2.Clear


sql = "SELECT DISTINCT Surname from Customer"

rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

While rs.EOF = False
List2.AddItem rs!Surname
rs.MoveNext

Wend
rs.Close
End If

that was the query, now here is what i use to display the information i want


Private Sub List2_Click()

Text7.Text = List2.List(List2.ListIndex)
sql = "SELECT DISTINCT * FROM Customer WHERE " & Combo2.Text & "='" & Text7.Text & "'"
rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

Set Text6.DataSource = rs
Text6.DataField = "CustomerID"
Set Text5.DataSource = rs
Text5.DataField = "Name"
Set Text4.DataSource = rs
Text4.DataField = "Surname"
List2.Enabled = False

End Sub


this is the error message that i get "Cannot include Memo, OLE, or Hperlink Object when you select unique values(Address)."

the error points to this line:
rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

what could be the prob?

[MadHacker]

Johno did you check the data type that you have for the field call (ADDRESS)
make sure it has the same data type like the one in your database

[johno]

i saw that but im not requesting the address field. im accessing the customer id, name and surname so i dont know why it giving me that message.

[johno]

hacker it worked i changed the data type from memo to text and now its working. bless up. but i never figured that it would have given me an error seen that im not requesting info from that field.

[johno]

i have encountered another error but in a different section. here is it, i have an admistrator area where i can modify, create, delete "normal" users username and passwords. i have a combo box where i select from , when i make a selection to edit or add a user this is the error message i get: "The connection cannot be used to perform this operation. it is either closed or invalid in this context."

here is the code

Private Sub cmdAddd_Click()
Call connectDB


This is for the add button

rs.Open "Users", conn, adOpenStatic, adLockOptimistic, adCmdTable

Set txtUserName.DataSource = rs
txtUserName.DataField = "Username"
Set txtPassword.DataSource = rs
txtPassword.DataField = "password"
rs.AddNew

End Sub

This is the form load code:

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set ro = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\db.mdb"
Set ro = New ADODB.Recordset
sql = "SELECT Username FROM Users"
ro.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText
While ro.EOF = False
Combo1.AddItem ro!UserName
ro.MoveNext
Wend
ro.Close
End Sub


This is code for selecting from the combo box

Private Sub Combo1_Click()
Call connectDB
sql = "SELECT DISTINCT * FROM Users WHERE Username='" & Combo1.Text & "'"
rs.Open sql, conn, adOpenStatic, adLockOptimistic, adCmdText

Set txtUserName.DataSource = rs
txtUserName.DataField = "Username"
Set txtPassword.DataSource = rs
txtPassword.DataField = "password"
End Sub


how can this error be solved

[MadHacker]

i have encountered another error but in a different section. here is it, i have an admistrator area where i can modify, create, delete "normal" users username and passwords. i have a combo box where i select from , when i make a selection to edit or add a user this is the error message i get: "The connection cannot be used to perform this operation. it is either closed or invalid in this context."

here is the code

Private Sub cmdAddd_Click()
Call connectDB


This is for the add button

rs.Open "Users", conn, adOpenStatic, adLockOptimistic, adCmdTable

Set txtUserName.DataSource = rs
txtUserName.DataField = "Username"
Set txtPassword.DataSource = rs
txtPassword.DataField = "password"
rs.AddNew

End Sub

This is the form load code:

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set ro = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\db.mdb"
Set ro = New ADODB.Recordset
sql = "SELECT Username FROM Users"
ro.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText
While ro.EOF = False
Combo1.AddItem ro!UserName :)
JOHNO YOU WANT A BOX whats with the exclamation sign
ro.MoveNext
Wend
ro.Close
End Sub


This is code for selecting from the combo box

Private Sub Combo1_Click()
Call connectDB
sql = "SELECT DISTINCT * FROM Users WHERE Username='" & Combo1.Text & "'"
rs.Open sql, conn, adOpenStatic, adLockOptimistic, adCmdText

Set txtUserName.DataSource = rs
txtUserName.DataField = "Username"
Set txtPassword.DataSource = rs
txtPassword.DataField = "password"
End Sub


how can this error be solved
try this tell me how it go
Combo1.AddItem rs.Fields("Username").value

[Virus_NCU]

let us see your connectDB procedure.
make sure that you always close the connection when you done with it.
or condition the connection:
if MyConnection.State = ConnectionState.Closed then
Myconnection.Open
'else it is already open so move on
end if

[johno]

here is the function u asked for!!

Function connectDB()

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open App.Path & "./db.mdb"
End Function

[johno]

hacker when i tried ur solution this was the message i got
"Object variable or With block variable not set"

[MadHacker]

try it with the connection name infront of it

Maybe I need to just create a little demo of that thing for ma self and see if I can help ya out

[johno]

hacker i got it to work. my connectDb function was the problem.
should have been the following

Function connectDB()

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection 'changed to conn
cn.Provider = "Microsoft.Jet.OLEDB.4.0" 'changes to conn
cn.Open App.Path & "./db.mdb" 'changed to conn
End Function


thanks for the previous help. im gonna do some more debugging. if i find anymore look out for me.

[johno]

got 90 odd percent on this project. and passed the overall course with flying colors, bless up all who helped.

[MadHacker]

so whe you seh you a send on some money :) or too guiness (cold)

[johno]

wen me dun me degree me set u up.