Hi everyone
So here's the run down, I have a search box on a split form that filters a datasheet based on whatever the user types in. The vba for the reset button is as follows:
Code:
Option Compare Database
Option Explicit
Private Sub cmdReset_Click()
Me.txtSearch = ""
Me.SrchText = ""
DoCmd.Requery
Me.txtSearch.SetFocus
End Sub
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub SrchText_AfterUpdate()
Me.SrchText.Requery
End Sub
Private Sub txtSearch_Change()
'Create a string (text) variable
Dim vSearchString As String
vSearchString = txtSearch.Text
SrchText.Value = vSearchString
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If
'Me.SearchResults = Me.SearchResults.ItemData(1)
'Me.SearchResults.SetFocus
DoCmd.Requery
Me.txtSearch.SetFocus
If Not IsNull(Len(Me.txtSearch)) Then
Me.txtSearch.SelStart = Len(Me.txtSearch)
End If
End Sub
The form is based on a query and the search box looks specifically for agency name
and the SQL is
Code:
SELECT [AgencyINFO-main].Agency, [AgencyINFO-main].Subsidary, [AgencyINFO-main].ProgramCodes, [AgencyINFO-main].ProgramNotes, [AgencyINFO-main].Address, [AgencyINFO-main].City, [AgencyINFO-main].Prov, [AgencyINFO-main].PostalCode, [AgencyINFO-main].FirstName, [AgencyINFO-main].LastName, [AgencyINFO-main].Position, [AgencyINFO-main].ContactNotes, [AgencyINFO-main].OriginalEmail, [AgencyINFO-main].PhoneNumber, [AgencyINFO-main].PhoneNotes, [AgencyINFO-main].AAExpiryDate, [AgencyINFO-main].InsuranceExpiry, [AgencyINFO-main].InsuranceExpiryComments, [AgencyINFO-main].WSIBYesNo, [AgencyINFO-main].WSIBNotes
FROM [AgencyINFO-main]
WHERE ((([AgencyINFO-main].Agency) Like "*" & [Forms]![frmAgencyLookup]![SrchText] & "*"));
My question is, if I wanted to include one or more search box(es) where users type in a program code (e.g. 1312), what would the vba/sql look? I tried doing this in the SQL by simply adding Like "*" & [Forms]![frmAgencyLookup]![SrchText] & "*" under ProgramCodes' criteria. This did work however, I wasn't extracting all records and thus figured it's better to have separate searchboxes. Any thoughts and suggestions are always much appreciated!