Dlookup not working

[Djblois]

I am using this code:

SaveSetting "PST", "CopyProd", "ProdNum", DLookup("[PC]", "WAVE3_INVN_DL", _
        "[LOT] = " & Forms!frmOpenLot!tbLotNum)

and I just keep getting this error, "The Expression you entered as a query parameter produced this error: 'ILR2360'"

ILR2360 is the Lot# that I am trying to open. The table name is correct and so are the field names (I checked them like 5 times). What else should I be looking at?

 

[DCrake]

SaveSetting "PST", "CopyProd", "ProdNum", DLookup("[PC]", "WAVE3_INVN_DL", _
"[LOT] = '" & Forms!frmOpenLot!tbLotNum & "'")

 

[pbaldy]

In the interests of teaching you how to fish:

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

 

[Djblois]

Thank you that fixed my issue. Now I am trying to Nest a dlookup inside of another dlookup.

Here is what I am trying and I am getting an error:

SaveSetting "PST", "CopyProd", "ProdNum", _
        DLookup("[CODE_EQ]", "WAVE3_PC_EQ", DLookup("[PC]", "WAVE3_INVN_DL", _
        "[LOT] = '" & Forms!frmOpenLot!tbLotNum & "'"))

 

[pbaldy]

It's probably pub time for David. The inner function looks okay, but will result in just a value for the outer one. Instead of "Field = Value" you'll simply have Value.

 

[Djblois]

pbaldy,

Than you for your help. However, I do not know what you mean by your response. Sorry for being such a heel.

 

[pbaldy]

The inner function has this as a criteria:

"[LOT] = '" & Forms!frmOpenLot!tbLotNum & "'"

which will evaluate to

Lot = 'abc'

The outer function only has the inner function in the criteria. The inner function will only return a value, so instead of

Lot = 'abc'

it will have

abc

Access doesn't know what field you want the value compared to (my guess is PC). Depending on data type, you may or may not need quotes around that value, as discussed on the link I posted.

 

[DCrake]

Paul

I spotted the first error and looked no further. It was clear that LOT was a string therefore it needed single quotes around it.

 

[pbaldy]

David, I think you correctly caught the only error there was at that point. This new problem came up in post 4.

 

[Djblois]

TY, I fixed that issue.

Now, I have one more thing. I am using an Append Query to copy records from one table to another. The first 3 fields I want to copy directly from first table but now I need to copy one field into the new table from the form that I had opened. How would I do this? Here is my SQL:

INSERT INTO tblSamplebyLot ( Ass_ProdNum, Spec_ID, Default_Value )
SELECT tblDefaults.Ass_ProdNum, tblDefaults.Spec_ID, tblDefaults.Default_Value
FROM tblDefaults
WHERE (((tblDefaults.Ass_ProdNum)=ProdToCopy()));

 

[pbaldy]

Try

SELECT tblDefaults.Ass_ProdNum, tblDefaults.Spec_ID, tblDefaults.Default_Value, Forms!FormName.ControlName As Whatever