DLookup Not Working

[jovuco]

Can anyone solve my mystery? I can't figure out why the "parts" and "price" field aren't being filled in when I enter in the parts#.

Here are the two tables involved:
Parts_List - contains the lookup info; PartsNo, Parts, Price.

tblParts - holds the information pulled from the Parts_lIst table. This contains the fields; Parts#, Parts, Price.

This is my OnChange Event Procedure for the "Parts#" field of my "tblParts subform":

Private Sub Parts_Change()
Dim varPrice, varParts As Variant
varPrice = DLookup("Price", "Parts_List", "Parts# =[PartsNo]&quot
varParts = DLookup("Parts", "Parts_List", "Parts# =[PartsNo]&quot
If (Not IsNull(varPrice)) Then Me![Price] = varPrice
If (Not IsNull(varParts)) Then Me![Parts] = varParts
End Sub

My Row Source Type is a table/query, and the Row Source is the "Parts_List" table, which is where I hold the list of PartsNo, Parts, and Price.

The control source (for the PartsNo selection from the Parts_List table) is "Parts#".

I hope that makes sense and someone can save me from staring at this for another hour.

jo

 

[Rohdem]

First, you have to concatenate the value [PartsNo] into the string like this:

varPrice = DLookup("Price", "Parts_List", "Parts# =" &[PartsNo])


Another thing that jumps out at me immediately is the # sign in your field name. Because you have that, you need to put all references to the field in brackets [], i.e.

varPrice = DLookup("Price", "Parts_List", "[Parts#] =" &[PartsNo])

You should try to avoid putting the # sign in field names because it is a special character in Access used to denote a date.

Next, is the field [parts#] a text or number field? If it is a text field, you need single quotes around your value, i.e.

varPrice = DLookup("Price", "Parts_List", "[Parts#] =' " &[PartsNo] & " ' &quot

Do a search in the help menu on dlookup and look at the examples for more guidance.

Mike Rohde
"I don't have a god complex, god has a me complex!"

 

[jovuco]

Thanks. I tried all your suggestions, and renamed the "Parts#" name to "PartsNum" but still no go.

I am already using the DLookup in another database, so I know the general set up of the statement is correct, but still no go.

Is there anything else I'm overlooking. I have a feeling it's something very basic or easy to fix.

jo

 

[jregier]

I have a question either of you could probably answer as you seem to know this function fairly well. I have the dlookup returning correct values, but repeated many times over for each record in the query - ie: returns {"RECORD1" "BRWN TPSL" }
{"RECORD1" "BRWN TPSL" }
{"RECORD1" "BRWN TPSL" } etc then starts the second record.

 

[Rohdem]

jovuco, If you want, you can e-mail the database to me and I'll look at it, if you have/can get it into Access 97 format.

programmer@marshallengines.com


jregier, I'm not really sure what your question is.
Mike Rohde
"I don't have a god complex, god has a me complex!"

 

[jovuco]

Thanks, I emailed it to you. jo