How to get a combo box for query criteria

[Hulas]

Guys, I spend hours trying to solve this problem on my own but no progress. I am using a query and right now I have "Enter ID Number" in the criteria box. But, it is not possible to remember all the id's, so what I want is a combo box in the message box, that will still ask the user "Enter ID Number" but instead of having a blank box I will have a drop down list of all the id's so that I can scroll down and just pick my id instead of bothering to remember them. Please help.

Hulas

[JConsulting]

Guys, I spend hours trying to solve this problem on my own but no progress. I am using a query and right now I have "Enter ID Number" in the criteria box. But, it is not possible to remember all the id's, so what I want is a combo box in the message box, that will still ask the user "Enter ID Number" but instead of having a blank box I will have a drop down list of all the id's so that I can scroll down and just pick my id instead of bothering to remember them. Please help.

Hulas

that's not possible. What you should do though, is put that on a form, and have your user select from the combo box then run the query. The criteria in the query would then change to forms!Yourform!YourCombo

As long as the bound field for the combo box contins the ID number, you're golden.

J

[LeoK]

what JConsulting said is true, it would just be easier to design a form, with the ID number as a combo box. You can add all the fields that relates to that ID number in the same form. If you do this in the Form wizard on Access 2003 the code for that ID combo is written for you, so that whenever you click on anything on the list the other fields relating to that combo box will automatically update.
The code should look something like this, located in the properties of the combo box in the "Event" tab and on the "After Update" row.

Private Sub List19_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EVENT_ID] = " & Str(Nz(Me![List19], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I am designing a member type database and I do find that this kind of form is very good for finding/view/editing members information.