'Search code used again and again to search on any field

Option Explicit

Public sql As String

Public retid As String

Dim recCustomers As Recordset

'make this [frmSearch] form invisible

Private Sub cmdCancel_Click()

Me.Visible = False

retid = ""

End Sub

'Display the customer with Cust_no as Id in the frmCustomer form

Private Sub cmdOK_Click()

On Error Resume Next

retid = recCustomers.Fields("Cust_no")

Me.Visible = False

'Call Back function in frmCustomer form

frmCustomer.Back retid

End Sub

Private Sub Form_Load()

GDBOpen ("C:\Program Files\Microsoft Visual Studio\VB98\carrental.mdb")

Set recCustomers = GDBRecordset("select * from customers")

loadcontrols

End Sub

Private Sub loadcontrols()

Dim i As Integer

Dim j As Integer

If Not recCustomers.EOF Then

recCustomers.MoveLast

End If

If Not recCustomers.BOF Then

recCustomers.MoveFirst

End If

'FGCustomers is Flex Grid Control

FGCustomers.Rows = recCustomers.RecordCount + 1

FGCustomers.Cols = 7

'Fill the cells of the Flex Grid

If recCustomers.RecordCount > 0 Then

With FGCustomers

For i = 1 To recCustomers.RecordCount

On Error Resume Next

For j = 0 To FGCustomers.Cols - 1

.Col = j

.Row = i

If IsNull(recCustomers(j)) Then

.Text = ""

Else

.Text = recCustomers(j)

End If

Next j

recCustomers.MoveNext

Next i

recCustomers.MoveFirst

End With

End If

End Sub

'Re-do the query each time txtSearch text box changes

Public Sub txtSearch_Change()

On Error Resume Next

Dim rs As Recordset

Dim str As String

str = sql & " '" & txtSearch.Text & "*'"

recCustomers.Close

Set recCustomers = GDBRecordset(str)

loadcontrols

End Sub

'The most important and subtle function

Static Function Search(ByVal txt As String) As String

On Error Resume Next

Dim frm As New frmSearch

frm.sql = txt

frm.Show vbModal

Search = frm.retid

End Function