Using =DLookUp for multiple fields (1 Viewer)

browninaz

Ugly Data Hoarder
Local time
Today, 07:49
Joined
Oct 26, 2012
Messages
88
Hello Everyone,

I have a multiple items form that displays purchases for a certain area. The purchases include items from two tables, with two different numbers. I have just learned how to use the =DLookUp function, but I would like to eliminate multiple controls on each line by using one text box control to list whatever item is listed. For example, on one line I have an equipment number, and on another line I have an office supplies number. Instead of having two text box controls per line with two different =DLookUp, I would only like to have one that displays whatever the line item happens to be.

This is the function I am using for one particular field:

=DLookUp("[EquipmentName]","[tblEquipment]","[EquipmentNum]=" & [Forms]![frmPurchasesOfficeOne]![EquipmentNum])

Hopefully I am making sense of all this.

Thanks to all who know more than me.
 

browninaz

Ugly Data Hoarder
Local time
Today, 07:49
Joined
Oct 26, 2012
Messages
88
Thanks Mark for the response,

I have tblPurchaseDetails, which contains the foreign key from tblEquipment, the foreign key from tblSupplies, and the foreign key from tblOffices. I then created a query from tblPurchaseDetails, which contain only numbers in the fields from tblEquipment, tblSupplies, and tblOffices. Instead of displaying the numbers for those fields in each record on my form, I would like to display the actual name of the equipment or supplies, which ever was purchased. If the record indicates only equipment purchased (which it should), I would like the text box to only display the name of the equipment. If the record indicates only supplies purchased (which it should), I would like the text box to display only the name of the supplies. I am trying to eliminate one blank text box control per record on my form.
 

MarkK

bit cruncher
Local time
Today, 07:49
Joined
Mar 17, 2004
Messages
8,178
And the 3 tables Equipment, Supplies, and Offices, do they have very different fields? Is there overlap?

Typically I would want the PurchaseDetail table to have a foreign key link to a single table, maybe tblProduct, since in respect to a purchase we want to be able to abstract whatever we purchase as a single type of thing (a purchasable), regardless of its subtype. Ideally would could create a product table with a field that contains the distinction Equipment, Supply, and Office as three values in a single field, rather than use those distinctions to separate that data by table.

If those three types of things do have considerably different fields, I would still likely prefer to have a single product table, and then have the multiple links from the product table to my product subtypes, rather than try and implement that directly from PurchaseDetail.

Does that make sense?
 

browninaz

Ugly Data Hoarder
Local time
Today, 07:49
Joined
Oct 26, 2012
Messages
88
Thanks again Mark,

I will have to take a closer look at my table structure and relationships, and try and conceptualize the advice that you just gave me.
 

Users who are viewing this thread

Top Bottom