combo box as filter for form

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

Guest

What is wrong with my code?


Private Sub cmbFilterStatus_AfterUpdate()
Dim rslt As String
rslt = Me.cmbFilterStatus.Value
Me.filter = "me.st2status = rslt"
Me.FilterOn = True
Me.Recordset.Requery

End Sub
 
Put the variable outside the quotes:

Assuming rslt is numeric:
Me.filter = "me.st2status =" & rslt

If it's text:
Me.filter = "me.st2status ='" & rslt & "'"
 
Try this:

Private Sub cmbFilterStatus_AfterUpdate()
Dim rslt As String
rslt = Me.cmbFilterStatus.Value
Me.filter = "[st2status] = '" & rslt & "'"
Me.FilterOn = True
Me.Recordset.Requery

End Sub
 
What is wrong with my code?

Private Sub cmbFilterStatus_AfterUpdate()
Dim rslt As String
rslt = Me.cmbFilterStatus.Value
Me.filter = "me.st2status = rslt"
Me.FilterOn = True
Me.Recordset.Requery

End Sub
Click to expand...

Do you really need to requery?

Try:
Me.filter = "me.st2status = '" & rslt & "'"

Even simpler to just use:
Private Sub cmbFilterStatus_AfterUpdate()
Me.filter = "me.st2status = '" & Me.cmbFilterStatus & "'"
Me.FilterOn = True
End If
 
yes, all solutions worked.

Here is what I didn't understand:

the syntax of { '" & [control] & "' }

is this necessary for all string values?
 
yes, all solutions worked.

Here is what I didn't understand:

the syntax of { '" & [control] & "' }

is this necessary for all string values?
Click to expand...

Yes.
A variable value must be concatenated into the string, and if it is a
text variable it must also be surrounded with single (or double
quotes).

Let's assume the value selected in the combo box was Jones.
Your expression:
Me.filter = "me.st2status = rslt"
Results in
Me.filter = "me.st2status = rslt"
literally rsit (not the value of rslt).

Me.Filter = "me.st2status = '" & rslt & "'"
results in
Me.Filter = "me.st2status = 'Jones'"
the value of rslt.

If the value is Number datatype, then ou do not surround the value
with quotes.
If rslt's value is 3 (as a number, not as a text 3), then
Me.filter = "me.st2status = " & rslt
results in
Me.filter = "me.st2status = 3"
Note that it still must be concatenated into the string.

Hope this is clear.
 
Back
Top