DLookUp Returns #Error when trying to get values from Lookup Table

[dotNet]

Using Access 2010 on Windows Vista



I have a report called InventoryCard, A Table, NewMotorInventory, and a lookup table, Horsepowers. The Report's RecordSource is NewMotorInventory. I placed a textbox, tbHorsePower, on the report with ControlSource

=DLookUp(" [Horsepowers]![HorsePower]",
"[Horsepowers]","[NewMotorInventory].[HorsePower] = [Horsepowers].[HorsepowerID]")

This returns #Error in tbHorsePower.

However if I place another textbox, tbHP, on the report and set its ControlSource to HorsePower, tbHorsepower now shows the correct value from the Horsepowers table.

Can anyone explain why this is happening this way. To me it seems that neither of these two text boxes should have any effect on eachother.

[June7]

Data aggregate functions can slow performance. Instead, you should make the report's RecordSource a join of the two tables. This will make the related info available. That is a major concept of relational databases - relating data through common key fields.

[dotNet]

Thanks for the reply, that is the path i will take. However, I am still curious as to why one text box has an effect on the other.

[boblarson]

I agree with June7 as to the ultimate fix. But let's get the DLookup syntax fixed as you may need to use that sometime in the future for something.

This is wrong:
=DLookUp(" [Horsepowers]![HorsePower]",
"[Horsepowers]","[NewMotorInventory].[HorsePower] = [Horsepowers].[HorsepowerID]")

This would be the corrected version:

=DLookUp("[HorsePower]",
"[Horsepowers]","[HorsePowerID] = " & [HorsepowerID])

You don't need to give the table name in the field area because you already are telling it which table in the table argument. And you use the ID field to identify the record you want and you also need to keep the field that is on the current form which you want a match to in the DLookup outside of the quotes so it passes the value. Also, when using a DLookup you should use the NZ function to handle possible nulls if there isn't a match or else you will get an error.

=Nz(DLookUp("[HorsePower]",
"[Horsepowers]","[HorsePowerID] = " & [HorsepowerID]),0)

[June7]

Maybe this is what happens.

Anything within quotes is a literal string. The DLookup refers to the Horsepower table so it finds the field [Horsepower].[HorsepowerID] that is within the quotes in that table but it is set equal to "[NewMotorInventory].[HorsePower]", Access can't find this value, hence the #ERROR. Apparently, placing HorsePower in the second textbox allows Access to populate a value into the corresponding field of the expression. I am also surprised this works.

[dotNet]

Thanks for the help. I am making the Report's RecordSource a join as per your suggestions.