Using DLookup function to reference a table

[toadmaster]

I have a table called users and a field in that table called amountauthorized.

My users enter a figure in a text box called estp on a form. estp is either equal to or less than the amountauthorized.

I want the code to check the figure in estp and compare it to amountauthorized in the table and if the figure in estp is greater than amountauthorized display message "Figure over authorized limit".

The following is the code I have written but I keep getting expression expected error .

I am new to vba and would appreciate your help and guidance with this

Dim x As String
x => (estp)
x = DLookup("amountauthorized", "Users", "UserName = test")
If (estp) < 200 And text33 = "test" Then
MsgBox "Amount Over approval limit"
End Sub 
 

NOTE:
text33 is another user field in the form and is the same as UserName field in the table.

Thank you

[nico5038]

You're close, just combine and use concatenation to get a result like:

If Me.estp > DLookup("amountauthorized", "Users", "UserName = '" & Me.text33 & "'") Then
   MsgBox "Amount Over approval limit"
end if
 

Getting the idea ?

Nic;o)

[toadmaster]

Nic;

Thanks for the quick reply; however when I use your script I am getting the message box "Amount Over Approval Limit" pop up even though the figure in the estp text box on the form is less than amountauthorized(which is 200) in the table users.

Maybe I didn't explain myself well in the initial post. I want the script to look at the figure in estp(text field in the form) and compare it amountauthorized(which is 200 and in the users table).

The message box is supposed to pop up when the figure in estp is more than the amountauthorized.

I only threw in text33 as an extra check during the DLookup function(text33 is another text field which contains username and is exactly the same as the username field in the users table); call it the primary key field.

My main concern is to make sure the "if statement" works when estp is over the limit authorized in the amountauthorized field in the table.

I hope this explains it.

Thank you

[nico5038]

OK, guess we have a datatype problem.
1) Check that amountauthorized is a numeric field
2) Make the estp field also numeric or change "Me.estp" into "Val(Me.estp)"

Nic;o)

[toadmaster]

I changed estp to Val(Me.estp) and it works.

Thank you so much? You Rock!!!!!!!!!!!

[nico5038]

Perhaps better to set the estp field to numeric on the form, thus the user can't enter alphabetical characters.

Success with your application !

Nic;o)

[toadmaster]

I am also trying to use the DLookup function to check a table to see if a user is in there; if not refuse them access. The following is the code I am using but seems to just pop up the "Error Message" without really referencing the table

text33 = Environ("username")
Text69 = DLookup("[Forename]&' '&[Surname]", "Users", "[Username] = text33")
If text33 <> DLookup("[Username]", "Users", "[Username] = text33") Then
MsgBox "You Are Not Authorized To Use This Form"
Else
If Val(Me.estp) > DLookup("amountauthorized", "Users", "UserName = '" & Me.text33 & "'") Then
   MsgBox "Amount Over approval limit, Please Assign to Your Manager"
Else
With MailOutLook
 

[nico5038]

You just need to test the DLOOKUP returning Null as value, indicating a user isn't found.
Something like:

If IsNull(DLookup("[Username]", "Users", "UserName = '" & Me.text33 & "'")) Then
 

I did also change the WHERE part to get the username from the formfield Text33.

Tip:
Hope you'll understand when making this code why I always name my fields like "txtUsername"....

Nic;o)

[toadmaster]

This works, thank you so much