Populate with the latest Data

[Guest]

I have a table for material cost called [Material]
I have a form called [Production] that populates my production table
in the form i have a combo box called "ingredient1" with a row source set
like this
SELECT Material.ID, Material.MaterialName, Material.Cost FROM Material;
and in the AfterUpdat Event I have this which populates the "IngCost1" Text
box with the rate of the ingredient from the combo box.
Now the problem the ingredient rates will be updated throught the year and
when we fill out the form i would like the most recent cost for ingredient 1
to populate the Textbox IngCost1. material tabel has fields "date" ,
"MaterialName", "Cost", "ID"

Thanks
Bart

 

[Guest]

Try setting the combo box's After Update event to:
IngCost1 = Me.ComboBoxName.Column(2)
This assumes that the cost is in the third column of the row source query.
The first column is Column(0), the second Column(1), etc. Bind IngCost1 to a
field in a table (your Production table?) if you need to retain the cost that
was in effect at the time the record was created.

 

[Guest]

I have that code in the after update already. It populates the text box ok
but does not populate with the lates cost
Me!IngCost1 = Forms![Production]!Ingredient1.Column(2)
am i missing something?
Thanks

 

[Guest]

When you say "populates" do you mean for a new record or for an existing
record? Will old records keep the cost that was in effect at the time the
record was created? When do you change the cost relative to when you create
the record? When you look at the row source query in datasheet view does it
show the correct price? You may need to requery the control, depending on
when you update the cost. That would be DoCmd.Requery "IngCost1" added to
the After Update event for the combo box, but that will only work if the row
source query shows the correct information to start with.

I have that code in the after update already. It populates the text box ok
but does not populate with the lates cost
Me!IngCost1 = Forms![Production]!Ingredient1.Column(2)
am i missing something?
Thanks

I have a table for material cost called [Material]
I have a form called [Production] that populates my production table
in the form i have a combo box called "ingredient1" with a row source set
like this
SELECT Material.ID, Material.MaterialName, Material.Cost FROM Material;
and in the AfterUpdat Event I have this which populates the "IngCost1" Text
box with the rate of the ingredient from the combo box.
Now the problem the ingredient rates will be updated throught the year and
when we fill out the form i would like the most recent cost for ingredient 1
to populate the Textbox IngCost1. material tabel has fields "date" ,
"MaterialName", "Cost", "ID"

Thanks
Bart

 

[Guest]

Yes Bruce, Each Day a new record is generated using the production form.
This data and the cost form materials must stay with the record. at lets say
6 months the cost for cement goes up all production records from that date
should reflect the new cost without changing the cost on the previous
records. the table for materials will have two records in it for cement one
with the old cost and one with the new cost.
And no the data view does not reflect the new prices .

When you say "populates" do you mean for a new record or for an existing
record? Will old records keep the cost that was in effect at the time the
record was created? When do you change the cost relative to when you create
the record? When you look at the row source query in datasheet view does it
show the correct price? You may need to requery the control, depending on
when you update the cost. That would be DoCmd.Requery "IngCost1" added to
the After Update event for the combo box, but that will only work if the row
source query shows the correct information to start with.

I have that code in the after update already. It populates the text box ok
but does not populate with the lates cost
Me!IngCost1 = Forms![Production]!Ingredient1.Column(2)
am i missing something?
Thanks

I have a table for material cost called [Material]
I have a form called [Production] that populates my production table
in the form i have a combo box called "ingredient1" with a row source set
like this
SELECT Material.ID, Material.MaterialName, Material.Cost FROM Material;
and in the AfterUpdat Event I have this which populates the "IngCost1" Text
box with the rate of the ingredient from the combo box.
Now the problem the ingredient rates will be updated throught the year and
when we fill out the form i would like the most recent cost for ingredient 1
to populate the Textbox IngCost1. material tabel has fields "date" ,
"MaterialName", "Cost", "ID"

Thanks
Bart