Coding a Search for data 1

[PALman]

I have a VB6 form which when opened connects to an Access 2000 database named CustomerDetails and displays all the fields of the first record in TextBoxes. Using the Data Control Bar on the form I am able to move to next previous last and first records.
However I need to code a ControlButton to search for a record by asking the user to type in the value of a field. For example, to run a search for a Customer, I want the user to type in a CustomerName and have the code look for that CustomerName in the Customer field of the already open recordset and have the form display that record with its fields for that Customer.
The ADO connection has already been made by opening of the form and the recordset is already visible.
Any help is much appreciated.

 

[vbSun]

The idea can be like this. (you have options here, as there are more than one way to do it)

1. Open a connection to the database.
2. Write an SQL Statement which will search for the keyed-in-value in the required table. (You will have to append the keyed-in-value to your SQLs WHERE condition)
3. Declare a recordset
4. Execute the query (use connection.execute/recordset.open) and assign it to the recordset. This will capture the return data.
5. If the recordset's BOF and EOF properties are true, you dont have any records returned.
6. Close and Destroy the recordset.

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[SBerthold]

>of the already open recordset
use the ado recordset's Find method

 

[PALman]

Thanks guys or all the helpful pointers. Being new to VB6 and more used to VBA, I really could do with a coded example of how to search and display a record and all its fields by taking user input (a field value such as a customer name or a job number)and searching the correct field in the recordset/Access table for that input.
As I said already, I have made connection with the database and table in the database, all I really need to know is how to search and display another record in the same table. This is the code I have which opens the database and recordset ok but lacks the power of searching the fields (TextBoxes)...

Dim Conn As ADODB.Connection
Dim rsEnq As New ADODB.Recordset
Option Explicit

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
End Sub

Private Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean)
'This is where you would put error handling code
'If you want to ignore errors, comment out the next line
'If you want to trap them, add code here to handle them
MsgBox "Data error event hit err:" & Description
End Sub

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
datPrimaryRS.Caption = "Record: " & CStr(datPrimaryRS.Recordset.AbsolutePosition)
End Sub

Private Sub datPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean

Select Case adReason
Case adRsnAddNew
Case adRsnClose
Case adRsnDelete
Case adRsnFirstChange
Case adRsnMove
Case adRsnRequery
Case adRsnResynch
Case adRsnUndoAddNew
Case adRsnUndoDelete
Case adRsnUndoUpdate
Case adRsnUpdate
End Select

If bCancel Then adStatus = adStatusCancel
End Sub

Private Sub cmdAdd_Click()
On Error GoTo AddErr
datPrimaryRS.Recordset.AddNew

Exit Sub
AddErr:
MsgBox Err.Description
End Sub

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr
With datPrimaryRS.Recordset
.Delete
.MoveNext
If .EOF Then .MoveLast
End With
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub

Private Sub cmdRefresh_Click()
'This is only needed for multi user apps
On Error GoTo RefreshErr
datPrimaryRS.Refresh
Exit Sub
RefreshErr:
MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr

datPrimaryRS.Recordset.UpdateBatch adAffectAll
Exit Sub
UpdateErr:
MsgBox Err.Description
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

'AND THIS IS WHERE I NEED HELP AT COMMAND BUTTON
'TO CARRY OUT SEARCH FUNCTION...
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim searchvar As String
searchvar = InputBox("Enter the Cusomer Name you wish to find")

'I BELIEVE NEXT LINE IS WRONG SYNTAX. db is causing error...
rsEnq.Open "Select * from MyTable where [Customer] like '" & searchvar & "%'", db

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Any help is much appreciated
Thanks

 

[Golom]

Presumably datPrimaryRS.Recordset is the recordsource for your text boxes and doing the search in a separate recordset, successful or not, won't reposition datPrimaryRS.Recordset. You may need something like

With datPrimaryRS.Recordset
   Dim bMark As Variant
   bMark = .Bookmark
   .MoveFirst
   .Find "[Customer] LIKE '" & searchvar & "%'" 
   If .EOF Then
      MsgBox "Customer " & searchvar & " not found."
      .Bookmark = bMark
   End If
End With

You need to do the MoveFirst because Find searches from the current position to the end of the file.

 

[SBerthold]

Depending if using bound controls, or recordset events, or other actions when the current record changes, use a recordset clone, directly off of the original recordset, to do the search with (Find) so the current record position doesn't change when a matching record is not found (If this isn't the case, then a clone isn't needed for that).
When/if one is found, then set the bookmark of the orig. rs to that of the clone.

 

[PALman]

Thanks Golom,
Your ten lines of code is just what I was looking for. Program has now the search facility I require.
I shall use your code to search different fields for various data.
Thanks a million!

 

[PALman]

Thanks SBerthold for your help. Please see Golom's post which has my program/code up and running.
Thanks again

 

[Golom]

Just to make things more robust ... you may want to

SearchVar = Replace(Trim$(SearchVar), "'", "''")
SearchVar = Replace(SearchVar, "[", Chr$(6))
SearchVar = Replace(SearchVar, "]", Chr$(7))
SearchVar = Replace(SearchVar, Chr$(6), "[[]")
SearchVar = Replace(SearchVar, Chr$(7), "[]]")
SearchVar = Replace(SearchVar, "_", "[_]")
SearchVar = Replace(SearchVar, "%", "[%]")
SearchVar = Replace(SearchVar, "*", "[*]")

before you do the search.

There are several characters that can be interpreted as mask characters of one sort of another and, if they exist in the input string, they will result in either invalid SQL or a search that doesn't find what you expect it to find.

The above just modifies those characters to avoid such problems.

 

[PALman]

Thanks Golom,
I shall add your latest code/post to my search coding.
Thanks again.