How can i create a query thats lists options?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that I want to be able to query by having a drop down list
or a list where a user can select more than one value or posibly both. Can
anyone help?
 
I have a database that I want to be able to query by having a drop down list
or a list where a user can select more than one value or posibly both. Can
anyone help?
Click to expand...

What do you want to do with these values once they're selected? You
can't put multiple values into one field!

It sounds like you need two Tables in a one-to-many relationship, and
a Subform to edit them, rather than trying to get tricky with a combo
box!

John W. Vinson[MVP]
 
JP-

We've got something that kinda does that. With a form that pops up with
three controls, a user is able to select from a list that populates based on
a prior selection. Essentially, the values of the second combobox are
populated based on the selection made in the first combo box:

ProjectTypeComboBox (gets project types from an existing SQL query)
ProjectNameComboBox (gets values based on what ProjectTypeComboBox is)
CommandButton (for running it all in the end)
----------------------------------------------------------------------------------------------
for the ProjectNameComboBox, we put an [Event Procedure] in it's "After
Update" Event. this, of course opens the VB scripting app. here is our code
for that procedure:

Private Sub ProjectTypeCombo_AfterUpdate()
Dim strSQL As String

strSQL = ProjectTypeCombo.Value

If strSQL = "Ship" Then
ProjectCombo.RowSourceType = "Table/Query"
ProjectCombo.RowSource = "Q-Ships"
ProjectCombo.BoundColumn = 1
End If

If strSQL = "Show" Then
ProjectCombo.RowSourceType = "Table/Query"
ProjectCombo.RowSource = "Q-Shows"
ProjectCombo.BoundColumn = 1
End If

If strSQL = "Project" Then
ProjectCombo.RowSourceType = "Table/Query"
ProjectCombo.RowSource = "Q-Projects"
ProjectCombo.BoundColumn = 1
End If

If strSQL = "Other" Then
ProjectCombo.RowSourceType = "Table/Query"
ProjectCombo.RowSource = "Q-Other"
ProjectCombo.BoundColumn = 1
End If

End Sub
------------------------------------------------------------------------------------------------
my CommandButton runs this procedure OnClick:

Private Sub Go_SearchProjects_Click() '<-- my commandButton

Dim SQLString As String
Dim TextObj As String

TextObj = ProjectCombo.Value
TextObj = Trim(TextObj)

SQLString = "ProjectName =" & " '" & TextObj & "'"

DoCmd.OpenForm "F-Projects", , , SQLString

End Sub
 
Back
Top