Dlookup based on bound control lookup combo box

[Udaman]

I have a form called [BrakesExpress1QueryForm] where I have a text box [txtFrontRotors] that I want to put a Dlookup formula in the Control Source properties.

I am looking for the field [FrontRotorPart] from table [BrakePartsGroup] based on [AssetGroup]. In the table [BrakePartsGroup] is the field called [AssetGroup] which is a lookup to another table called [AssetGroups]. Because this is a lookup the data format is a number. So I am trying to match up [AssetGroup] on my form to [AssetGroup] in my table [BrakePartsGroup].

So here is my code that returns an #Error

=DLookUp("[FrontRotorPart]","[BrakePartsGroup]","[AssetGroup]='" & [Forms]![BrakesExpress1QueryForm]![AssetGroup] & "'")

So I thought maybe it's because it's a number data format, so I adjusted my code:

=DLookUp("[FrontRotorPart]","[BrakePartsGroup]","[AssetGroup]=" & [Forms]![BrakesExpress1QueryForm]![AssetGroup])

This now returns a value of 575.

Is my problem related to the data formats from my lookups in my table? Or am I just missing something simple?

 

[Udaman]

I forgot to mention that the [FrontRotorPart] is also a lookup to the [Parts] table, which therefore has a number data format. I wasn't sure if this was part of the problem, but doesn't hurt to mention it.

 

[Udaman]

So in playing with it some more, I was able to use the code to lookup a cost value, and it worked. So my formula appears to be correct, it's just not converting the number to text. I have this issue on another post in this forum that I wasn't able to get resolved. So if I can find a fix for this here, it will provide me with a fix for my other post too. I keep researching, but if anyone knows the fix, please feel free to save me some time.

 

[Udaman]

Well, I didn't find the fix, but I did find a work around. What's happening is in my tables, I use the lookup wizard to provide me with a list of items to choose from. This is fine in some cases, but in this particular case, it's causing more problems than it helps. So instead I broke those relationships, and reset the data format to short text, and I just typed in the value I wanted instead of choosing from a list. Now all my Dlookups work just fine. Perhaps someday I'll figure out how to make it work with lookups, but for today I move forward with what works.