By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,651 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,651 IT Pros & Developers. It's quick & easy.

Using DLookup function to reference a table

P: 45
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

Expand|Select|Wrap|Line Numbers
  1. Dim x As String
  2. x => (estp)
  3. x = DLookup("amountauthorized", "Users", "UserName = test")
  4. If (estp) < 200 And text33 = "test" Then
  5. MsgBox "Amount Over approval limit"
  6. End Sub 
  7.  
NOTE:
text33 is another user field in the form and is the same as UserName field in the table.

Thank you
Jan 24 '10 #1

✓ answered by 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)

Share this Question
Share on Google+
8 Replies


nico5038
Expert 2.5K+
P: 3,072
You're close, just combine and use concatenation to get a result like:
Expand|Select|Wrap|Line Numbers
  1. If Me.estp > DLookup("amountauthorized", "Users", "UserName = '" & Me.text33 & "'") Then
  2.    MsgBox "Amount Over approval limit"
  3. end if
  4.  
Getting the idea ?

Nic;o)
Jan 24 '10 #2

P: 45
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
Jan 24 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 24 '10 #4

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

Thank you so much? You Rock!!!!!!!!!!!
Jan 24 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 24 '10 #6

P: 45
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
Expand|Select|Wrap|Line Numbers
  1. text33 = Environ("username")
  2. Text69 = DLookup("[Forename]&' '&[Surname]", "Users", "[Username] = text33")
  3. If text33 <> DLookup("[Username]", "Users", "[Username] = text33") Then
  4. MsgBox "You Are Not Authorized To Use This Form"
  5. Else
  6. If Val(Me.estp) > DLookup("amountauthorized", "Users", "UserName = '" & Me.text33 & "'") Then
  7.    MsgBox "Amount Over approval limit, Please Assign to Your Manager"
  8. Else
  9. With MailOutLook
  10.  
Feb 1 '10 #7

nico5038
Expert 2.5K+
P: 3,072
You just need to test the DLOOKUP returning Null as value, indicating a user isn't found.
Something like:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(DLookup("[Username]", "Users", "UserName = '" & Me.text33 & "'")) Then
  2.  
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)
Feb 1 '10 #8

P: 45
This works, thank you so much
Feb 2 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.