Searching

hello,

i am creating an application where i search for a phone number in a database created using access 2000. i am using ado. i have tried so many different thigns to get my search to work and have failed miserably. any help from anyone would be greatly appreciated.

thanks

Post what you have, somebody can help.

David Paulson

This could be an example, cnn is an open ADODB.Connection

set rst = cnn.execute("SELECT * FROM People WHERE PhoneNo = '555-5555'")



This returns all the records from the People table, where the PhoneNo is 555-5555

OR

set rst = new ADODB.Recordset
rst.open "People", cnn
rst.Filter = "PhoneNo='555-5555'"



Does the same as the first, but probably slower, cause this opens the entire table. This will be faster if you need to search multiple times

OR

set rst = new ADODB.Recordset
rst.open "People", cnn
rst.Find "PhoneNo='555-5555'"



The last one will go to the first mathing record, starting from the current position

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

Hello,

this is the code that i have so far(see below). It works. it finds the record. the only problem is once it finds it, i cannot browse other records when i hit NEXT or Previous.

I get an error message saying "The changes u requested to the table were not succesfull becasue they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicated data, remove the index, or redefine the index to permit duplicate entries and try again"


Private Sub Command5_Click()

'Search procedure for a Record

Dim sql As String
Dim msgretval As Integer

Dialog.Show vbModal
On Error GoTo errlabel

sql = "SELECT * FROM CUSTOMERS WHERE PHONE = '" & Dialog.Text1.Text & "';"
Unload Dialog
Set Dialog = Nothing
Set cnCustomer = New ADODB.Connection
cnCustomer.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\RESTAURANT\customer.mdb"

cnCustomer.Open
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open sql, cnCustomer, adOpenDynamic

If rsCustomer.BOF And rsCustomer.EOF Then
MsgBox "Customer NOT FOUND"
End If


Text1.Text = rsCustomer![firstname]
Text2.Text = rsCustomer![lastname]
Text3.Text = rsCustomer![phone]
Text4.Text = rsCustomer![address]
Text5.Text = rsCustomer![city]
Text6.Text = rsCustomer![State]
Text7.Text = rsCustomer![zip]
Text8.Text = rsCustomer![notes]
Exit Sub


The code for browsing the NEXT record is:

Private Sub Command6_Click()
'Module for browsing on to the next record
On Error GoTo errlabel
If Not Adodc1.Recordset.EOF Then
Adodc1.Recordset.MoveNext
End If
If Adodc1.Recordset.EOF And Adodc1.Recordset.RecordCount > 0 Then
Adodc1.Recordset.MoveLast
End If
Exit Sub

errlabel:
MsgBox Err.Description
End Sub

Thanks in advance

I'm sorta lost with your code. Have you created a recordset (rsCustomer) and using an adodc control also? Please explain to me what you are trying to accomplish with each.

David Paulson

thanks but i figured it out. i just had to unload and reload the form and it works.

thanks though. appreciate it.