Filter problem

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All
What wrong with this:

Private Sub Command11_Click()
Me.Filter = "[dr_work_competed] is Null"
Me.Filter = Me.Filter & [dr_work_gp].Value =
DLookup("[tblUser]![useractualname]", "[tblUser]",
"[tblUser]![UserName]=currentuser()")
Me.FilterOn = True
End Sub

I know that the problem lies with the 2nd part of the filter, because if I
'rem' it out the 1st part works fine.
I have tried putting single/double quotes, and apostrophies, around the 2nd
part, but the line keeps going red.
I know that the DLookup expression is OK because that is used elsewhere.

Hope someone can help.

Many thanks,
Leslie Isaacs
 
Hi Leslie,

Loaded your code into a module, the code is red as you stated:
The code below is black:

Private Sub Command11_Click()
Me.Filter = "[dr_work_competed] is Null"
Me.Filter = Me.Filter & [dr_work_gp].Value = _
DLookup("[tblUser]![useractualname]", "[tblUser]",
"[tblUser]![UserName]=currentuser()")
Me.FilterOn = True
End Sub

Something like this might be an alternative way to write the code you have
above:

Private Sub Command11_Click()
Dim strUser As String

strUser = DLookup("[tblUser]![useractualname]", _
"[tblUser]", "[tblUser]![UserName]=currentuser()")
Me.Filter = "[dr_work_competed] is Null and [dr_work_gp].Value = '" &
strUser & "'"
Me.FilterOn = True
End Sub

HTH -Linda
 
Hello Linda

Thank you for your reply.
Unfortunately I couldn't get either of your suggestions to work.

The first one doesn't show any errors, but the filter doesn't work - i.e.
doesn't return any records when it definitely should.

The second one seemed OK in the VBA editor, but when the button is clicked
the [dr_work_gp].Value seems not to be recognised - I get the 'Enter
Parameter Value' dialogue box, asking for [dr_work_gp].Value.

Very curious: hope you can help!

Thanks again
Les



Squirrel said:
Hi Leslie,

Loaded your code into a module, the code is red as you stated:
The code below is black:

Private Sub Command11_Click()
Me.Filter = "[dr_work_competed] is Null"
Me.Filter = Me.Filter & [dr_work_gp].Value = _
DLookup("[tblUser]![useractualname]", "[tblUser]",
"[tblUser]![UserName]=currentuser()")
Me.FilterOn = True
End Sub

Something like this might be an alternative way to write the code you have
above:

Private Sub Command11_Click()
Dim strUser As String

strUser = DLookup("[tblUser]![useractualname]", _
"[tblUser]", "[tblUser]![UserName]=currentuser()")
Me.Filter = "[dr_work_competed] is Null and [dr_work_gp].Value = '" &
strUser & "'"
Me.FilterOn = True
End Sub

HTH -Linda


Leslie Isaacs said:
Hello All
What wrong with this:

Private Sub Command11_Click()
Me.Filter = "[dr_work_competed] is Null"
Me.Filter = Me.Filter & [dr_work_gp].Value =
DLookup("[tblUser]![useractualname]", "[tblUser]",
"[tblUser]![UserName]=currentuser()")
Me.FilterOn = True
End Sub

I know that the problem lies with the 2nd part of the filter, because if I
'rem' it out the 1st part works fine.
I have tried putting single/double quotes, and apostrophies, around the 2nd
part, but the line keeps going red.
I know that the DLookup expression is OK because that is used elsewhere.

Hope someone can help.

Many thanks,
Leslie Isaacs
Click to expand...
Click to expand...
 
Back
Top