Using Dlookup with Access97

[Paul1100]

Hi all, I hope someone can help me i have the following code and keep getting an error when running but i can seem to fine the right combinations to make it work. I'm a novice and am lost please help!!

Dim Answer As Variant
Dim Count As Variant
Answer = DLookup("[Security Code]", "AuthorizedUsertoOverRide", ([Security Code] = '" & [Forms].[OverRide Table].[Security Code] & "') And ("[EmployeeNumber]=[Forms].[OverRide Table].[EmployeeNumber]")
If IsNull(Answer) Then
Count = 0
MsgBox "Not Authorized To Override contact System Admin [" & Me.[EmployeeNumber] & "]"
Else
Count = 1
DoCmd.RunSQL "INSERT INTO[OverRide Table]([Product ID],[Employee Number],[Date])Select[Forms].[OverRide Table].[Date],[Forms].[OverRide Table].[Product ID],[Forms].[OverRide Table].[EmployeeNumber]FROM[AuthorizedUsertoOverRide]"
MsgBox "Record Saved [" & Me.[EmployeeNumber] & "]"
End If

Paul

 

[pbaldy]

Are those supposed to be 2 criteria? If so try:

DLookup("[Security Code]", "AuthorizedUsertoOverRide", "[Security Code] = '" & [Forms].[OverRide Table].[Security Code] & "' And [EmployeeNumber]= " & [Forms].[OverRide Table].[EmployeeNumber])

This has a multi-criteria example:

http://www.mvps.org/access/general/gen0018.htm

 

[bob fitz]

Try This:
Change:

And ("[EmployeeNumber]=[Forms].[OverRide Table].[EmployeeNumber]")

To:

And ("[EmployeeNumber]=" & [Forms].[OverRide Table].[EmployeeNumber])

 

[Paul1100]

i'm sorry yes this does have 2 set of criteria and i tried what you said but a get a compile error now

 

[Paul1100]

Bob i tried your suggestion and i get an error 438 object does not support this property or method

 

[pbaldy]

What's your code now, and where/what is the compile error?

 

[Paul1100]

Pbaldy the compile error states expected expression and if i hit ok it goes to the ' after security code =

 

[pbaldy]

Did you miss:

What's your code now

 

[Paul1100]

Pbaldy i don't understand your statement "did I miss:"

 

[pbaldy]

Did you miss that I asked you to post your revised code? It's hard to debug what you can't see.

 

[Paul1100]

oops I'm sorry and thanks for being so understanding here is the revised code
Answer = DLookup("[Security Code]", "AuthorizedUsertoOverRide", [Security Code] = '" & [Forms].[OverRide Table].[Security Code] & '" And [EmployeeNumber]= " & [Forms].[OverRide Table].[EmployeeNumber])

 

[pbaldy]

I posted that wrong and corrected it right away. It looks like you picked up the pre-edited version. Try what's in post 2.

 

[Paul1100]

Okay i tried it again an this time i get an error 438 object does not support this property or method!

Here is the revised code:

Answer = DLookup("[Security Code]", "AuthorizedUsertoOverRide", "[Security Code] = '" & [Forms].[OverRide Table].[Security Code] & "' And [EmployeeNumber]= " & [Forms].[OverRide Table].[EmployeeNumber])

 

[pbaldy]

On that line? Can you post the db here? That looks correct for a text and numeric field.

 

[Paul1100]

ok i think you want the whole database but i can't upload it my company has some kinda of firewall thing that errors me out. but you are right the empID is numeric and the security code is text

 

[pbaldy]

Hopefully Bob is still around and has an idea then. That looks correct to my eye, so I'm down to playing with it to find the problem.

 

[bob fitz]

That looks correct to my eye, so I'm down to playing with it to find the problem.

Me too, really . The only thing I can think of is if the data type of one or both the fields is not what we are assuming.
So, Paul1100:
Please open the table (in design view) that holds the two fields "Security Code" and "EmployeeNumber" and tell us the data type setting of each field. While you're there, also check the spelling. I notice that one has a space in the name but the other does not.

 

[ChrisO]

[Forms].[OverRide Table]

[OverRide Table] is not a Property of the Forms collection.

It should be:-
[Forms]![OverRide Table]

Chris.

 

[Paul1100]

ChrisO that is it such a small change can a big difference