Password on button in access

Question

I am designing an order form and I would like to restrict access to submit the form to certain employees. In the employees table, I have their name, their password (which is different for each employee), and different check box fields that denote what that employee can do. So for example, Employee A might have a password of Password and have the field 'Can_Submit_Request' checked. I have tried the following code which I found and modified when looking into this earlier:

If Me.txPassword = DLookup("Password", "Employee", "Employee_ID='" & YourEmployee_ID & "'") Then
    If Nz(DLookup("Can_Submit", "Employee", "Employee='" & YourEmployee_ID & "'") , False) = True Then
        '/ continue on to the Submit process
    Else
        Msgbox "You do not have permission to submit"
    End If
Else
    Msgbox "Invalid Password"
End If

This received an error of "Data type mismatch in criteria expression", and admittedly I don't really know what the criteria is doing, especially the YourEmployee_ID part. I'm very new to Access and VBA and have tried looking up how to use DLookup to no avail, so any help with this would be greatly appreciated.

Answer 1

I'm a little rusty with VBA, but "Data type mismatch in criteria expression" is saying that somewhere in the code there is a data type (Boolean, integer, etc...) that isn't what the database is expecting. I.e. if Employee_ID is a number/integer datatype (check the table design if you're in access), then you'll need to have to specify that the variable prompted is an integer in VBA.

Here's another article that helps understand datatypes a bit: http://www.informit.com/articles/article.aspx?p=712186&seqNum=3

Can you show where in your code you specify where you prompt the user for the "YourEmployee_ID" variable?

Answer 2

If Employee_ID is a number type field do not use apostrophe delimiters. You use Employee_ID in one expression but Employee in the other. Can_Submit is a Yes/No field?

If Me.txPassword = DLookup("Password", "Employee", "Employee_ID=" & YourEmployee_ID) 
    If Not Nz(DLookup("Can_Submit", "Employee", "Employee_ID=" & YourEmployee_ID), False) Then
        MsgBox "You do not have permission to submit"
    End If
Else
    MsgBox "Invalid Password"
End If