Concatenate two DLookup in ApplyFilter

[GoToHell]

Hi,

I have a form bound to a table and I try to filter the reccords in the open form event.

My problem is that the field that supports the filter : "strCréateur" (creator in french) is a combination of to fields in an other table "strNom" (LastName) and "strPrénom" (LastName)

In addition to this I use DLookup to automaticaly get the Last and FirstName in the other table called "tblUsers" where they have to match environ("username") (I have access 2002).

I don't know if what I said is understandable so I'll let the code speak for itself

Here are my non working shots :


'FILTER IN ORDER TO DISPLAY ONLY THE RECCORDS WHERE THE USER IS CREATOR
Private Sub Form_Open(Cancel As Integer)
Dim Nom As String
Dim Prénom As String

Nom = DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") 'gets the Last name of the connected user and puts it in the variant Nom

Prénom = DLookup("[strPrénom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")'gets the First name of the connected user and puts it in the variant Prénom

DoCmd.ApplyFilter , "[strCréateur] = " & Nom & " " & Prénom

'Displays a message if there is no reccords found
If Not FormHasData(Form) Then
MsgBox "Your did not create any of the boxes in the database" & Err.Description, vbExclamation, "Créateur inexistant"
End If
​

End Sub



I also tried things like :

'Home made starts with
DoCmd.ApplyFilter , "[strCréateur] = " & DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") & "*"

'No variant declaration
DoCmd.ApplyFilter , "[strCréateur] = " & DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") & " " & DLookup("[strPrénom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")

And I tried to play with the "'" '" & "'" ="& = & &"... but didn't work


The weirdest part is that when I have this code in my form if I double click on the form in the database window nothing happens, not even an error message, I'm confused.

Thank you in advance for your suggestions.

 

[pbaldy]

See if this works:

DoCmd.ApplyFilter , "[strCréateur] = '" & Nom & " " & Prénom & "'"

 

[GoToHell]

No, I typed this :

Private Sub Form_Open(Cancel As Integer)

Dim Nom As String
Dim Prénom As String

Nom = DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")

Prénom = DLookup("[strPrénom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")

DoCmd.ApplyFilter , "[strCréateur] = '" & Nom & " " & Prénom & "'"

If Not FormHasData(Form) Then

MsgBox "Vous n'êtes pas Créateur des Actions référencées dans la base de données" & Err.Description, vbExclamation, "Créateur inexistant"

End If
End Sub
​

and it still doesn't work.

The thing is when I double click nothing shows up but if I remove the above part of the code it works fine (without filtering of course).

Usualy if the ApplyFilter doesn't find any match it goes through the FormHasData that shows a msgbx and then an empty form pops up, here nothing happens!!

 

[pbaldy]

Have you set a breakpoint to examine the variables and make sure they contain what you expect?

 

[GoToHell]

No matter where I put the break point the vb window doesn't show up. It still doesn't do anything when I double click on the form..

 

[pbaldy]

Can you post the db?

 

[GoToHell]

it's the form : frmModifActionsCréateur

I know that my code has to be improve (do all the Onerror goto) but there is work in progress..

It works when a single Dlookup (that brings back a long) is needed in the ApplyFilter: frmModifActionsResponsable.

 

[GoToHell]

Anyone has any idea?

 

[GoToHell]

I was cleaning my code : removing dead code and inserting On Err and it suddenly got to work? How is that?

Maybe someone has a good explanation to that kind of magic in order to conclude that thread!