Search as you type VBA (1 Viewer)

vent · Jun 27, 2017

Hi everyone,

My goal is to make a search bar in a split form. For now, I want to keep it simple and have the datasheet filter by organization/agency name. Here's the query I have so far:

Code:

SELECT AgencyINFO.[Organization Entity/Agency Name(Legal Name)], AgencyINFO.[Subsidary Name], AgencyINFO.[Expiry Date], AgencyINFO.[Insurance Expiry Date], AgencyINFO.[WSIB Employer Declaration Complete?], AgencyINFO.Prov, AgencyINFO.[Postal Code], AgencyINFO.[Contact Name], AgencyINFO.Position, AgencyINFO.Email, AgencyINFO.[Phone/Extension], AgencyINFO.[Program(s)], AgencyINFO.Address, AgencyINFO.Prov, AgencyINFO.City
FROM AgencyINFO
WHERE [Organization Entity/Agency Name(Legal Name)] LIKE "*" &  Forms!splitAgencySearch!SrchText & "*"

I tried typing it out in the search bar but nothing comes up. Thoughts?

Thank you!

jdraw · Jun 27, 2017

Suggest you post a zip of your database. Show us what you have done.

I find your naming quite contrary to most recommended best practices.
eg.
No embedded spaces and no special characters in field or object names.

vent · Jun 27, 2017

jdraw said:
Suggest you post a zip of your database. Show us what you have done.

I find your naming quite contrary to most recommended best practices.
eg.
No embedded spaces and no special characters in field or object names.

Right now it's very messy but I forgot to mention, here is the VBA associated with the search text box

Code:

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

Previously this worked on a list box, but since this new task involves using a datasheet (split form) then I tried doing the same thing. The list box in my last example was called SearchResults So I'm guessing the problem is in:

Code:

'Me.SearchResults = Me.SearchResults.ItemData(1)
    'Me.SearchResults.SetFocus

Basically I made a query and turned that into a split form.

Uncle Gizmo · Jun 27, 2017

If you want more control over your SQL statements, you could look at building them in VBA. If that's of Interest you might find my YouTube playlists on the subject useful here:- "MS Access - Building Search Criteria"

vent · Jun 27, 2017

Never mind guys, I got it working. Turns out it was a matter of correctly naming the variables in the vba. Thanks as always!

Search as you type VBA (1 Viewer)

vent

Registered User.

jdraw

Super Moderator

vent

Registered User.

Uncle Gizmo

Nifty Access Guy

vent

Registered User.

Similar threads

Users who are viewing this thread