Dlookup help

[gmc2k2]

Hi,
I need some help in regards to a dlookup problem I have, I have a switchboard which has 2 buttons, I'm trying to set-up the "Admin" Button to only load if a user is an admin. i've setup a table (tblUsers) which has 3 fields (UserID,Displayname and Group) the userid field is based on the default value fOSUserName() function, the Display name is the users real name (just for display purposes) and the Group will have either Admin or User as the value.

i've got a code which i'm trying to use on click of the admin button but keep getting a runtime error '2471'
the expression you entered as a query parameter produced this error: 'h06849962'

h06849962 is the admin's UserID and i'm trying to allow them to access the form as the group value is "Admin"

the code i'm using is:

Private Sub openadmin_Click()
If DLookup("Group", "tblUsers", "UserID =" & fOSUserName) <> Admin Then
MsgBox "You have insufficient rights to open the Admin Form"
Cancel = True
End If

i found the above code on the net and can't seem to get it to work, any help would be appreciated

 

[namliam]

The userid will most probably be a text field then??
If so.... you need to put quotes around your string lookup, like so:

DLookup("Group", "tblUsers", "UserID =""" & fOSUserName & """")
or
DLookup("Group", "tblUsers", "UserID ='" & fOSUserName & "'")

 

[gmc2k2]

thank you so much namliam saved me a headache
i've now implemented a form opening in datasheet view for admin's to add users

Private Sub openadmin_Click()
If DLookup("Group", "tblUsers", "UserID =""" & fOSUserName & """") <> "Admin" Then
MsgBox "You have insufficient rights to open the Admin Form"
Else: DoCmd.OpenForm "frmUsers", acFormDS
End If

End Sub

 

[namliam]

Save yourself another headache in the future, ID, though short for Identifier in general use is a number field, most PK's are numbers even auto numbers....
If you have this userid which is the system username.... call it UserName, not UserID

 

[gmc2k2]

thank you so much namliam will take that on board.
i have another question along the same lines...

i also have some code which runs onload event:

Private Sub Form_Load()
Dim intStore As Integer

intStore = DCount("[ref]", "[jobs]", "[compdate] <=Now() AND [Complete] =0 ")

If intStore = 0 Then
Exit Sub
Else
If MsgBox("There's " & intStore & " job(s) for today" & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have jobs...") = vbYes Then
DoCmd.OpenForm "Reminders", acNormal
Else
Exit Sub
End If
End If
End Sub

But it loads jobs for every user regardless if it belongs to them, what i would like to do is combine fOSUserName with this so it checks the table of jobs and only returns those matching fOSUserName with those belonging to them if that makes sense?

 

[pr2-eugin]

Add that to your code too ! Also you have some code that needs to be changing !

Private Sub Form_Load()
    Dim intStore As Integer

    intStore = DCount("[ref]", "[jobs]", "[compdate] [COLOR=Red][B]<= " & Format(Date, "\#mm\/dd\/yyyy\#") & " [/B][/COLOR]AND [Complete] = 0[COLOR=Red][B] AND UserID = '" & fOSUserName & "'"[/B][/COLOR])

    If intStore <> 0 Then
        If MsgBox("There's " & intStore & " job(s) for today" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You Have jobs...") = vbYes Then DoCmd.OpenForm "Reminders", acNormal
    End If
End Sub

 

[namliam]

The now() should work I guess shouldnt it Paul???

Otherwize for the UserID *grmbl to that name* like Paul suggested above or with my double "" solution instead '

 

[pr2-eugin]

The now() should work I guess shouldnt it Paul???

It should and it will. The only problem I feel is that the field name 'compdate' suggests that it holds just date, so make sense to use/compare against Date() over Now(). Unless the Date/Time field actually has the whole Date and Time stored and its Format is set to Short Date. Then Now can be used. If that's the case, I would suggest the use of DateValue function too. As there might be a bit of bizzare result !

? Now()
30/01/2014 14:32:56 
? #30/01/2014 15:35:00# <= Now()
False
? DateValue(#30/01/2014 15:35:00#) <= Date()
True

Otherwize for the UserID *grmbl to that name* like Paul suggested above or with my double "" solution instead '

I would prefer using "".

 

[gmc2k2]

thank you both so much, it all worked a treat one more thing to do and then i can start using my DB

 

[namliam]

I definately know the difference between Now and Date

Just wondering on why you are creating the format in it instead of replacing the <= Now() to <= Date()