Open specific based on return from SQL statement

[anb001]

Open specific form based on return from SQL statement

I'm trying to get some code put together, so a specific form will open, based on a users 'AccessLevel'. However it won't work.

This is the code I have so far:

Private Sub Form_Timer()

Dim strSecurityLevel As String
Dim LID As Variant
LID = GetUser()   'this get users loginID

strSecurityLevel = "SELECT tblUserDetails.SecurityLevel from tblUserDetails WHERE LoginID = '" & LID & "'"

If strSecurityLevel = 4 Then
    DoCmd.Close
    DoCmd.OpenForm "frmSecurity_4"
ElseIf strSecurityLevel = 3 Then
    DoCmd.Close
    DoCmd.OpenForm "frmSecurity_3"
ElseIf strSecurityLevel = 2 Then
    DoCmd.Close
    DoCmd.OpenForm "frmSecurity_2"
Else
    DoCmd.Close
    DoCmd.OpenForm "frmSecurity_1"
End If

End Sub

Thanks.

 

[mdlueck]

However it won't work.

What does the code do? Have you turned on the debugger and stepped through the code yet?

 

[anb001]

Yes, I have tried the debugger.

At the first line in the IF statement, it just states "type mismatch".

Basically, the SQL statement should find out which SecurityLevel the user has, and depending on that level (called 1, 2 3 or 4), the corresponding form should open.

/Anders

 

[Addyman]

You have defined the strSecurityLevel variable as a string - shouldn't it be Integer?

If not, and it is a string, then your statements should be:

If strSecurityLevel = "4" Then....

I suspect it is a numeric data value though (I would hope so).

 

[Addyman]

Also, rather than using numerous 'If...then...elseif' statements I would stongly advise you read up on using 'Select Case' - much easier and tidier for this kind of example.

 

[spikepl]

Acces does not automagically convert string to query results. You have written a string and expect it somehow to run a query. Nothing happens, because you did not tell access to do anything with it.

Besides, using a query is not advisable here. Use the DLookup function. Look
it up in the help file.

In the future skip "it doesn't work" because that means nothign to anyone not watching your screen. Always say what you expected to happen, what did in fact happen, and what errors occurred (with FULL text of error message) and where.

 

[anb001]

It seems like using the DLookUp function got it working, and it is a biy more tidt using Select Case.

Thanks all.