Can you do this with access 2000?

[vince99]

Is there a way of adding a drop don box to a query in order to select criteria?


Thanks

 

[ghubbell]

Sorry, no Vince, there isn't that I have ever found. You could however build a form based on the query, Add a combo box there with a default value, and use it to modify your criteria with a simple line in its "after update" event in VB:

Me.Requery Gord
ghubbell@total.net

 

[superspeed]

Yes, there is a way and I do it frequently. You have to modify the properties of the drop down list. Here is an example of a subroutine where the user has to pick two colors for an item. It is executed on activation of the color selection form: (the commented code is for my own debugging and use). Stmt (0) is the finished selection string. Te other STMT variables I use to build the parts of the string. When I coded the program, the first thing I did was copy the select statment from the properties box so that it would be correct.


Private Sub setup_SO_color_selection()
' --------------------------------------------------------------------------------------------------------
' This rountine sets up the select statment for the colors to restrict to the manufactuere on the screen
' --------------------------------------------------------------------------------------------------------
Dim stmt(5) As String
Dim q As String
Dim i As Integer
q = "'"

stmt(1) = "SELECT [MR Finishes Query3].[ID], [MR Finishes Query3].[Manufacturer], [MR Finishes Query3].[Group], [MR Finishes Query3].[Grade], [MR Finishes Query3].[Color], [MR Finishes Query3].[Description] FROM [MR Finishes Query3]"
stmt(2) = " where [MR Finishes Query3].[Manufacturer] = " + q + Me.Manufacturer + q
stmt(3) = " And [MR Finishes Query3].[Group]= " + q + Me.Finish_Group_1 + q
stmt(4) = " And ([MR Finishes Query3].[Grade] = " + q + Me.Grade_Level + q
stmt(5) = " OR [MR Finishes Query3].[Grade] = '*')"
stmt(0) = stmt(1) + stmt(2) + stmt(3) + stmt(4) + stmt(5)

' i = MsgBox(stmt(0), vbInformation, "debug&quot
[SO Pick Color 1].RowSource = stmt(0)

' Setting stmts 0, 2 and 4 are redundant as they do not change
' stmt(1) = "SELECT [MR Finishes Query3].[ID], [MR Finishes Query3].[Manufacturer], [MR Finishes Query3].[Group], [MR Finishes Query3].[Grade], [MR Finishes Query3].[Color], [MR Finishes Query3].[Description] FROM [MR Finishes Query3]"
' stmt(2) = " where [MR Finishes Query3].[Manufacturer] = " + q + Me.Manufacturer + q
stmt(3) = " And [MR Finishes Query3].[Group]= " + q + Me.Finish_Group_2 + q
' stmt(4) = " And [MR Finishes Query3].[Grade] = " + q + Me.Grade_Level + q
' stmt(5) = " OR [MR Finishes Query3].[Grade] = '*')"
stmt(0) = stmt(1) + stmt(2) + stmt(3) + stmt(4) + stmt(5)
[SO Pick Color 2].RowSource = stmt(0)

End Sub