combo box as filter for form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CS Hayes - 16 Nov 2006 18:52 GMT

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

Signature

Chris Hayes
Still a beginner

Reply to this Message

Ken Snell (MVP) - 16 Nov 2006 19:48 GMT

Put the variable outside the quotes:

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

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

Signature

Ken Snell
<MS ACCESS MVP>

> What is wrong with my code?
>
[quoted text clipped - 6 lines]
>
> End Sub

Reply to this Message

kingston - 16 Nov 2006 19:50 GMT

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?
>
[quoted text clipped - 6 lines]
>
>End Sub

Reply to this Message

fredg - 16 Nov 2006 19:50 GMT

> What is wrong with my code?
>
[quoted text clipped - 6 lines]
>
> End Sub

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

CS Hayes - 16 Nov 2006 21:01 GMT

yes, all solutions worked.

Here is what I didn't understand:

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

is this necessary for all string values?

Signature

Chris Hayes
Still a beginner

> > What is wrong with my code?
> >
[quoted text clipped - 17 lines]
> Me.FilterOn = True
> End If

Reply to this Message

fredg - 16 Nov 2006 22:24 GMT

> yes, all solutions worked.
>
[quoted text clipped - 3 lines]
>
> is this necessary for all string values?

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message