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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 & "'")
 
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!

Dennis said:
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 & "'")

MacNut said:
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
Click to expand...
Click to expand...
 
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

Dennis said:
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 & "'")

MacNut said:
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
Click to expand...
Click to expand...
 
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 ?


MacNut said:
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

Dennis said:
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 & "'")

MacNut said:
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
Click to expand...
Click to expand...
Click to expand...
 
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!

Dennis said:
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 ?


MacNut said:
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

Dennis said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

MacNut said:
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!

Dennis said:
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 ?


MacNut said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top