Hi im currently having a problem with taking data from a field in a database and then using it within the form, when supplied with a telephone number by inputting in
to a text box, the number needs to be checked against the database and then if a record matches the telephone number the Name in the record shoudl be inserted into another text box.

I am using visual basic 6 and microsoft access 2000

At the moment i am getting an error of "the connection cannot be used to perform this operation"

Dim CON As Connection
Dim rs As New Recordset
Dim SQLname As String

Private Sub cmd_details_Click()

rs.Open "Select * from customer, CON, adOpenKeyset, adLockOptimistic,"

SQLname = "Select name from Customer where telephone='" & (txtphone.Text)
txtname.Text = SQLname

End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

End Sub

thanks in advance

try this


SQLname = "Select name from Customer where telephone=' " & nameoftxtbox & "' "

put this on your rs.open even or

like this

rs.source = "Select name from Customer where telephone=' " & nameoftxtbox & "' "
rs.activeconnection = CON
rs.open

your SQLname is a query thats needs to be open,

I can see how using the new SQL string was improved however when implementing it in this manner

rs.Source = "Select name from Customer where telephone=' " & txtphone.Text & "' "
rs.ActiveConnection = CON
rs.Open

I am getting an error of the ooperation is not allowed when the object is ope

Thanks for the help thus far.

have you remove your 1st rs.open event?
and replace it with the 1 gave u


if thats the error , then remove it
the system cannont open two recordset with the same name,

Try this:

Dim CON As new ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQLname As String

Private Sub cmd_details_Click()


SQLname = "Select name from Customer where telephone='" & (txtphone.Text)

rs.Open sqlName, CON, adOpenKeyset, adLockOptimistic

txtname.Text = Rs!name
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

End Sub

Reference ADODB in your project

Try this...

Dim CON As new ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQLname As String

Private Sub cmd_details_Click()


SQLname = "Select name from Customer where telephone='" & txtphone.Text & "'"

rs.Open sqlName, CON, adOpenKeyset, adLockOptimistic, 1

txtname.Text = rs.fields("name").value
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection      'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb"  'connection string to the database

If rs.State = adStateOpen Then rs.Close
gSql = "Select name from Customer where telephone='" & txtphone.Text & "'"
rs.Open gSql, CON, adOpenKeyset, adLockOptimistic, 1

txtname.Text = rs.fields("name").value
End Sub

Private Sub form_initialize()

Set CON = New ADODB.Connection 'set the database connection
CON.Open "Provider=Microsoft.jet.oledb.4.0; Data Source=" & App.Path & "\test.mdb" 'connection string to the database

End Sub

This article has been dead for over six months. Start a new discussion instead.