Solved: SendKeys to use the 'Find and Replace' form

[Eric58132]

Hi All,

I'm trying to use SendKeys to automate the search functions on a form in a database I'm building (to later give away and hopefully wipe my hands clean of).

On my form I have a text box where the user types in what they are searching for, and then a combo box listing out the names of the available controls you can search within. With these two selections, I have written the following code:


Dim SearchVariable As String
SearchVariable = Me.SearchCriteria.Value

Me.[SearchBox].SetFocus
SendKeys "^(c)", 1
If SearchVariable = "Street Name" Then
Me.[Street Name].SetFocus
ElseIf SearchVariable = "Job Number" Then
Me.[Job Number].SetFocus
ElseIf SearchVariable = "Maximo Number" Then
Me.[Maximo Number].SetFocus
Else: Me.[Town].SetFocus
End If

SendKeys "^(f)", 1
SendKeys "^(v)", 1
SendKeys "%(f)", 1


Where SearchCriteria is the combo box and SearchBox is the text box. My problem is that when I run this code, the "Look In" portion of the find and replace form defaults to the form, and not the object that I have selected based upon the 'If' statement in the code above. I have tried to use {TAB} within my SendKeys code but for some reason tab doesn't work? I was trying to use tab in this format: SendKeys "{TAB}",1 but I wasn't receiving any sort of response when running the code.

Any suggestions on how I can fix this problem would be extremely helpful. Thanks!

[HiTechCoach]

First I would avoid using sendkeys for anything. It has known issuess/bugs. It is also very unreliable and easy to "break."

Why not just filter the form?

[HiTechCoach]

Example:


Me.Filter = Chr(34) & "[" & Me.SearchCriteria &"] = " & Me.SearchBox & Chr(34)
Me.FilterOn = True


Warning **** Air Code - Not Test *****

Posted from my Palm Pre Phone

[CreganTur]

You can control filtering directly via VBA- just lookup the Filter method- you're placing a lot of overhead on the system by using SendKeys and opening your program to possible vulnerabilities.

[Eric58132]

thank you for the replies coach and cregan, I will go at it from the filter standpoint

[HiTechCoach]

You're welcome.

Glad we could assist.

[Eric58132]

ok I have to resurrect this thread. I was able to get my code to work as long as I'm searching within text fields, but when it is a number field I'm searching on, I am asked to enter a parameter. My code looks as follows:


Dim SearchVariable As String
SearchVariable = Me.SearchCriteria.Value
If SearchVariable = "Street Name" Then
Me.Filter = "[Street Name] Like '*" & Me.Searchbox & "*'"
Me.FilterOn = True
ElseIf SearchVariable = "Job Number" Then
Me.Filter = "[Job Number] Like '*" & Me.Searchbox & "*'"
Me.FilterOn = True


In this instance, the "Street Name" option works, but the "Job Number" one does not.

Any ideas?

[HiTechCoach]

What is the name of the parameter that it is asking for?

Is the field in the form's record source?