Multiple Criteria Dynamic Search

[Louislam]

Hi,
Since I am new here, I'd like to share a sample with you.

This is a dynamic search textbox I made after learning several examples, Interface is like this:

I thinks it's quite user-friendly a tool that you might consider to add into you own database.

you can search similar spelling records with maximum 3 criteria (I thinks 3 is enough for searching specific records) separated by symbol ";"(There is no space in between). Criteria will stroll through all fields of subform and return the result. Also the code maybe a bit clumsy since I have no time to refurbish it.

Example:
1 criterion: Ribbon
2 criteria: Ribbon;Cotton
3 criteria: Twill;Tape;3/8

Though, there is a drawback I noticed, Since I put the code to the textbox's On_Change event, every time I type, the cursor automatically fleets to end of the text, That's quite inconvenient in case that I want to revise the first two criteria.

I know the problem comes from below code:
Me.SearchFor.SelStart = Me.SearchFor.SelLength

Yet I have no idea how to fix it, but I know there must be a syntax to replace this.

Anyway, hope this can do you some use and I'd appreciate if anyone can help with this problem and better the code.
PS: The problem can be easily solved by putting the code to textbox's On_Enter or AfterUpdate Event, but that will lose its real time filtering function, rendering it less user-friendly.
View attachment Multiple_Criteria_Dynamic_Search.accdb

 

[Leon1davies]

Hello,

Thanks for your sample. I used something similar a while back and came across the same problem of the moving cursor, here's how I solved it.

Put the following in a module:

Public Function pfPositionCursor(ctl As Control, lngWhere As Long)
 
    Select Case ctl.ControlType
        Case AcControlType.acTextBox, AcControlType.acComboBox
 
            ctl.SelStart = lngWhere
            ctl.SelLength = 0
 
        Case Else
            'Do Nothing
    End Select
 
End Function

Then the last step of my query field change event I set the focus to the field and run the module

me.txtQuery.SetFocus
pfPositionCursor [txtQuery], len(strQuery & "")

txtQuery is the name of my search field and strQuery is the same thing but I capture if its null in VBA.

let me know if this makes sense and if it doesnt I will send you my sample