find if tempVars value matches with a value in a subform

[boboivan]

Hello everyone!



I need to hide 2 subforms in a form, if the current user is not listed in the 3rd query subform type.
The name of the current user is in TempVars("CurrentUser").

I've tried so far in On Current form section:

If DLookup("UserName", "Subform3", "UserName=" & TempVars("CurrentUser") & "") = True Then
   Me.Subform1.Visible = True
   Me.Subform2.Visible = True
Else
   Me.Subform1.Visible = False
   Me.Subform2.Visible = False
End if

but with no luck.

Any ideas?
Thx a lot!

[orange]

Please tell us more about

the 3rd query subform type

What exactly is Subform3?

Detail syntax for DLookup()

good luck

[boboivan]

Subform3 is a list of users made from a query, that can see and edit the current record.

[orange]

Please show the SQL of the query Subforms3.
What is the value of TempVars("currentuser")?

but with no luck.

Are you getting an error from Access?

[boboivan]

yes! I get an error msgbox from Access. I guess the error is because it should be a table name there, not a Subform name, as far as I could see. In this case, maybe is not a good idea to use dlookup function. What else I could use?
The value of the TempVars("currentuser") is the name of the user that is signed in at that moment. It is a variable value.
The query is made in order to filter the users who can see and edit each specific record in the form.

[orange]

Please post the SQL for query Subform3?

Also add a line

Debug.Print TempVars("CurrentUser") before your If statement
so we can see the value that Access sees.

See what gets printed to the immediate window.

[boboivan]

the SQL is

[orange]

It appears that there may be a missing space before FROM

SELECT StructuriDeInterventieEvenimente.StructuraDeInterv entie, DefinireEvenimente.Eveniment, EvenimenteLunare.EvenimentLunar, StructuriDeInterventieEvenimente.IDevenimentFROM (StructuriDeInterventieEvenimente INNER JOIN DefinireEvenimente ON StructuriDeInterventieEvenimente.IDeveniment = DefinireEvenimente.IDeveniment) INNER JOIN EvenimenteLunare ON DefinireEvenimente.IDeveniment = EvenimenteLunare.EvenimentLunar
GROUP BY StructuriDeInterventieEvenimente.StructuraDeInterv entie, DefinireEvenimente.Eveniment, EvenimenteLunare.EvenimentLunar, StructuriDeInterventieEvenimente.IDeveniment
ORDER BY StructuriDeInterventieEvenimente.IDeveniment;


Formatted SQL

SELECT StructuriDeInterventieEvenimente.StructuraDeInterventie
    ,DefinireEvenimente.Eveniment
    ,EvenimenteLunare.EvenimentLunar
    ,StructuriDeInterventieEvenimente.IDeveniment
FROM (
    StructuriDeInterventieEvenimente INNER JOIN DefinireEvenimente ON
    StructuriDeInterventieEvenimente.IDeveniment = DefinireEvenimente.IDeveniment
    )
INNER JOIN EvenimenteLunare ON 
    DefinireEvenimente.IDeveniment = EvenimenteLunare.EvenimentLunar
GROUP BY StructuriDeInterventieEvenimente.StructuraDeInterventie
    ,DefinireEvenimente.Eveniment
    ,EvenimenteLunare.EvenimentLunar
    ,StructuriDeInterventieEvenimente.IDeveniment
ORDER BY StructuriDeInterventieEvenimente.IDeveniment;

[boboivan]

I've just copied it and pasted it. It seems like it didn't take the space there. It is a space, of course.

[orange]

Yes, DLookup is expecting a table or query name -not a subform.

Any update based on the Debug.Print TempVars("CurrentUser")

[boboivan]

Any update based on the Debug.Print TempVars("CurrentUser")

I don't know what you mean. The TempVars("CurrentUser") value is the name of the user that is loged in. It is a variable value.

On the other hand, I don't know why is so difficult for access to lookup for a value in a subform. It seems like a pretty decent thing to do
By the way, the subform has now the Record Source a table, not a query.

[boboivan]

I could find a solution for my problem.

If (TempVars("CurrentUser") = DLookup("Table/QueryFieldNameToLookUp", "Table/QueryName", "IDrecordInTheTable/Query=" & "IDrecordWhereIsTheSubform3" & " And [FieldNameOfSubform3] = '" & TempVars("CurrentUser") & "'")) Then ...