Access search form forces a parameter box

Question

I have a search form, linked to a single table showing number of employees based on location, which is chosen from two combo boxes (State and Office)

I run a search using the code below:

Private Sub command90_Click()

Dim strsearch As String
Dim Task As String
'Check if a keyword entered or not
If IsNull(Me.office.Value) Or IsNull(Me.state.Value) Then
  MsgBox "Please select an office", vbOKOnly, "Keyword Needed"
  Me.office.BackColor = vbYellow
  Me.state.BackColor = vbYellow
  Me.state.SetFocus
Else
strsearch = Me.office.Value


'Task = "SELECT * FROM table1 WHERE ((officename Like ""*" & strsearch & "*""))"


   Task = "SELECT * FROM table1 WHERE officename = Me.office.Value"
   Me.RecordSource = Task
   Me.office.BackColor = vbWhite
   Me.state.BackColor = vbWhite
End If

Form!office.SetFocus

End Sub

Before, I used LIKE * in order to find numbers of employees, but ran into trouble when offices had similar names.(Commented out above with the ')

So in order to return exact results, I decided to use combo boxes, and restrict search terms to the combo box value. However now a box shows up saying "enter parameter", which if entered exactly, does return the exact results, but loses the convenience of combo boxes.

How can I make it so the enter parameter box does not show up,and instead the search runs using only the combo box values?

Gustav Gustav · Accepted Answer · 2018-02-13T07:49:48

Concatenate the value:

Task = "SELECT * FROM table1 WHERE officename = '" & Me!office.Value & "'"

More Information:

MSDN (Microsoft) : Everything About Using Parameters from Code
Allen Browne's answer here : Using Form Control values in Queries

Access search form forces a parameter box

1 Answers

Concatenate the value:

More Information: