Full Version: "Quick find" combo box control
UtterAccess Forums > Microsoft® Access > Access Forms
jheiland
Good evening,
I'm trying to add a combo box control to my form which, when a value is selected from the drop-down list, will update the form to the record with a matching control in the header of the form. I'm having trouble getting it to work. Here is the code behind the combo box control:
Private Sub Combo7_AfterUpdate()
Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone
MyRecSet.FindFirst "[Thera_Class] = " & Me!Combo7
End Sub
THere are examples of errors I'm getting when I select from the list the following:
1. Combo box selection: Acetaminophen Antidote
Run-time error 3077:
Syntax error (missing operator) in expression
2. Combo box selection: Antiglucocorticoid
Run-time error 3070:
The Mircrosoft Jet Database engine does not recognize 'Antiglucocorticoid'
as a valid field name or expression
3. Combo box selection: Anesthetic, Local
Run-time error: 3077
Syntax error (comma) in expression
From the error messages, it appears that there may be a problem with the expression in the FindFirst method - ie, 1st one with missing operator has space between the words in the selection, 3rd one with comma in expression has a comma in the selection.
The code is borrowed from the Access VBA Programming for Dummies book (2004) and adjusted (at least I think it is) to my situation. I've tried setting the MyRecSet variable to a Recordset type and have tried various combinations in the expression of the FindFirst method, all of which have not helped any. The table where the combo box is getting its values from has only one field in which all of the values are unique. Attached is a screenshot of some of the properties of the combo box. Any insight as to what I'm doing wrong would be greatly appreciated.
Thanks!
Jeff
HiTechCoach
If your combo box is return text then you need to us:
MyRecSet.FindFirst "[Thera_Class] = " & Chr$(34) & Me!Combo7 & Chr$(34)
Hope this helps...
jheiland
Hello! Thank you for your suggestion. I gave it a try but am still having no luck. The combo box will now accept the selected value (yes, it's text) and I'm no longer getting an error message, however, it doesn't take the form to the record indicated in the combo box. Any other thoughts?
Thanks again,
Jeff
HiTechCoach
You are missing this line of code:
If Not MyRecSet.EOF Then Me.Bookmark = MyRecSet.Bookmark
Use like this:
CODE
Private Sub Combo7_AfterUpdate()
Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone
MyRecSet.FindFirst "[Thera_Class] = " & Me!Combo7
If Not MyRecSet.EOF Then Me.Bookmark = MyRecSet.Bookmark
End Sub

Hope this helps...
jheiland
Thank you very much! It works perfectly now. I really appreciate your help!
eff
HiTechCoach
You're welcome/
Glad we could assist.
uarulez2.gif
jheiland
Sorry, but I just have to resurrect this one. I've applied the solution and have gotten it to work, but as I've looked at it and thought about it for the last several days, I've been wondering "How in the world does this work?!?"
I've not been able to find any published help on the syntax of the criteria for the .FindFirst method:
MyRecSet.FindFirst "[Thera_Class] = " & Chr$(34) & Me!Combo7 & Chr$(34)
Information I came across while searching in the object browser notes that the criteria portion is like an "SQL statement without the WHERE clause". However, it does not discuss nor even come close in the examples, to showing the format given above. In the solution you've provided, there are quotation marks around things that I thought would mean a literal string (apparently not in this case) and character functions returning quotation marks (34) surrounding a control identifier which I thought wouldn't have needed them. Also, why couldn't I have just used the actual quotation marks there instead of calling a function in the latter portion of the criteria?
Where in the world does one look for criteria syntax/formatting information for these SQL statements within code? Never in a million years would I have figured out how to format that criteria statement unless you had helped! As my 10-year old dauther likes to say: "I'm confuzzled!" I'm prepared to be appropriately embarrassed if the answer has been under my nose all along and I haven't been looking in the right spot.
Thanks again,
Jeff
R. Hicks
Basically the syntax posted:
CODE
MyRecSet.FindFirst "[Thera_Class] = " & Chr$(34) & Me!Combo7 & Chr$(34)

.. can be typed another way:
CODE
MyRecSet.FindFirst "[Thera_Class] = '" & Me!Combo7 & "'"

Both lines will do the same thing ...
The syntax is to locate a text datatpe.
The Chr(34) is the equivalent of the quote character ....
RDH
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.