Populating a field based on THREE drop down lists....

[Guest]

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN

 

[Guest]

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

 

[Guest]

The fields are actually number fields...as the separate IDs for each lookup
table is an autonumber field. Is there an alternative way of looking up the
unit cost then? Or should I switch all the IDs to text, including the
autonumber fields?

Thanks!

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN

 

[Guest]

Ok - I changed my fields to text and I entered the following code into my
"UnitCost" field:

=DLookUp("[UnitCost]","tblPCAUnitCosts","CombinedID = '" & Me.ItemID &
Me.TypeID & Me.RepairID & Me.Unit & "'")

I am getting an error - #name? So i'm not sure what i'm doing wrong...any
suggestions?

THanks!
MN

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN

 

[Guest]

You could have left the the combined fields as numeric and simply removed the
apostrophes but what I suspect is the problem is the name combinedID. This
was just a name I used in my example. You need to give it the field name in
your tblPCAUnitCosts table that is the primary key of all the 3 fields
combined.
Also you said there were 3 fields combined and in your statement below you
have a 4th - Me.Unit ?

Ok - I changed my fields to text and I entered the following code into my
"UnitCost" field:

=DLookUp("[UnitCost]","tblPCAUnitCosts","CombinedID = '" & Me.ItemID &
Me.TypeID & Me.RepairID & Me.Unit & "'")

I am getting an error - #name? So i'm not sure what i'm doing wrong...any
suggestions?

THanks!
MN

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN

 

[Guest]

Dennis,

Oh, I see. So in other words, concatenate all the field names ? How would
I go about doing that so it goes in place of "CombinedID?"
UnitIDTypeIDRepairIDUnit ....something like that? (yes, there is a 4th field
part of the primary key too - i almost forgot)

Thanks Dennis!

You could have left the the combined fields as numeric and simply removed the
apostrophes but what I suspect is the problem is the name combinedID. This
was just a name I used in my example. You need to give it the field name in
your tblPCAUnitCosts table that is the primary key of all the 3 fields
combined.
Also you said there were 3 fields combined and in your statement below you
have a 4th - Me.Unit ?

Ok - I changed my fields to text and I entered the following code into my
"UnitCost" field:

=DLookUp("[UnitCost]","tblPCAUnitCosts","CombinedID = '" & Me.ItemID &
Me.TypeID & Me.RepairID & Me.Unit & "'")

I am getting an error - #name? So i'm not sure what i'm doing wrong...any
suggestions?

THanks!
MN

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

:

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN

 

[Guest]

Your initial request said you have a table with every combination of the
concatenated values as the primary key. It is the name of that field in that
table that you use instead of my CombinedID name.

Dennis,

Oh, I see. So in other words, concatenate all the field names ? How would
I go about doing that so it goes in place of "CombinedID?"
UnitIDTypeIDRepairIDUnit ....something like that? (yes, there is a 4th field
part of the primary key too - i almost forgot)

Thanks Dennis!

You could have left the the combined fields as numeric and simply removed the
apostrophes but what I suspect is the problem is the name combinedID. This
was just a name I used in my example. You need to give it the field name in
your tblPCAUnitCosts table that is the primary key of all the 3 fields
combined.
Also you said there were 3 fields combined and in your statement below you
have a 4th - Me.Unit ?

Ok - I changed my fields to text and I entered the following code into my
"UnitCost" field:

=DLookUp("[UnitCost]","tblPCAUnitCosts","CombinedID = '" & Me.ItemID &
Me.TypeID & Me.RepairID & Me.Unit & "'")

I am getting an error - #name? So i'm not sure what i'm doing wrong...any
suggestions?

THanks!
MN

:

Assuming Item, Type & Repair are text fields, set the control source property
of the unit cost field to

=DLookup("[Unit Cost]","Table Name","CombinedID = '" & Me.ItemCombo &
Me.TypeCombo & Me.RepairCombo & "'")

:

First of all, is this possible?

To explain further, I have 3 drop down lists (Item, Type and Repair)....the
user selects a value for each one. Then based on the combination of those
values, I would like the "unit cost" to be automatically populated onto my
form based on a separate table - that has the itemID, typeID and repairID as
the primary key. I have the unit cost for EACH POSSIBLE combination of those
3 ids in that lookup table.

Any advice on how to do this?

Thanks a bunch!
~MN