Automatically get Value from a table based on values entered on fo

[PPCO]

I have 4 fields on a form: [Activityname], [Itemname], [UOM], and
[Equipmentname]. I would like to create a text or combo box that based on
what the user enters in those 4 fields, automatically populates a field from
a separate table called [tblgoalbyequipment].[goallevel]. I see numerous posts
on here that would seem to answer the question, but not having much luck.
Thanks!

 

[Duane Hookom]

Is that all you are willing to tell us?
How would you do this manually?
Can we assume there is more than one record in tblgoalbyequipment?
Do you want a random goallevel or one based on some logic that only you know?
Do you expect to simply display the value or do you have a good reason for
storing a value that can be calculated?

 

[PPCO]

Oops, sorry. In [tblgoallevel] there are related fields that match
[activityname], [itemname], [UOM], and [equipmentname]. There are multiple
records in that table. So when a user fills in the 4 fields above on the form
(which goes to [tblproduction], I wanted to put a field on the form that
would automatically populate with the related goal level from [tblgoallevel].
Hope that's more clear; thanks alot!

Is that all you are willing to tell us?
How would you do this manually?
Can we assume there is more than one record in tblgoalbyequipment?
Do you want a random goallevel or one based on some logic that only you know?
Do you expect to simply display the value or do you have a good reason for
storing a value that can be calculated?

--
Duane Hookom
Microsoft Access MVP

I have 4 fields on a form: [Activityname], [Itemname], [UOM], and
[Equipmentname]. I would like to create a text or combo box that based on
what the user enters in those 4 fields, automatically populates a field from
a separate table called [tblgoalbyequipment].[goallevel]. I see numerous posts
on here that would seem to answer the question, but not having much luck.
Thanks!

 

[Duane Hookom]

I expect you would need code in the Before Update of the form something like:

Me.txtGoalLevel = DLookup("GoalLevel","tblGoalLevel","[ActivityName]='" & _
Me.txtActivityName & "' AND [ItemName]='" & Me.txtItemName & _
"' AND UOM = '" & Me.txtUOM & "' AND EquipmentName = '" &
Me.txtEquipName & "'")

This all depends on your exact field and control names as well as their data
types. The above code suggests all fields are text and that none of the text
values will include an apostrophe in them.
--
Duane Hookom
Microsoft Access MVP

Oops, sorry. In [tblgoallevel] there are related fields that match
[activityname], [itemname], [UOM], and [equipmentname]. There are multiple
records in that table. So when a user fills in the 4 fields above on the form
(which goes to [tblproduction], I wanted to put a field on the form that
would automatically populate with the related goal level from [tblgoallevel].
Hope that's more clear; thanks alot!

Is that all you are willing to tell us?
How would you do this manually?
Can we assume there is more than one record in tblgoalbyequipment?
Do you want a random goallevel or one based on some logic that only you know?
Do you expect to simply display the value or do you have a good reason for
storing a value that can be calculated?

--
Duane Hookom
Microsoft Access MVP

I have 4 fields on a form: [Activityname], [Itemname], [UOM], and
[Equipmentname]. I would like to create a text or combo box that based on
what the user enters in those 4 fields, automatically populates a field from
a separate table called [tblgoalbyequipment].[goallevel]. I see numerous posts
on here that would seem to answer the question, but not having much luck.
Thanks!