after update populate field from a query

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

Guest

I have 2 fields ingredient1 wich is a combo box to choose an ingredient
from a table of ingredients called material. I also have ingredient cost
table where we update the cost of the ingredients. I have a query of the
inredientcost tabel that list each ingredient with the LAST cost from the
ingredient cost table. in the after update event on the combo box I would
like to populate the ingcost1 field with the corresponding value from this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
 
Isn't your combo box on a form? I would include a column in the combo box
that is the "LAST" cost. You can then add code in the After Update event of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first column.
 
Yes it is and I did that same exact thing and it works fine, thanks for your
reply. But I have just come across a flaw in the logic. On my form I choose
an ingredient for ingredient 1 and it populates ingcost1 with the lastcost
for that ingredient lets say in january it is $1.00. This cost goes in the
fiels on my production table for that day and is used in calculating cost per
day , month , and per unit. Now in Feb there is a price increase so all
records for feb will have lets say $2.00. Will all the records prior to the
increase stay at 1.00 or will they now get changed to 2.00.
--
Bart


Duane Hookom said:
Isn't your combo box on a form? I would include a column in the combo box
that is the "LAST" cost. You can then add code in the After Update event of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first column.

--
Duane Hookom
MS Access MVP
--

Bart said:
I have 2 fields ingredient1 wich is a combo box to choose an ingredient
from a table of ingredients called material. I also have ingredient cost
table where we update the cost of the ingredients. I have a query of the
inredientcost tabel that list each ingredient with the LAST cost from the
ingredient cost table. in the after update event on the combo box I would
like to populate the ingcost1 field with the corresponding value from this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
Click to expand...
Click to expand...
 
If you use the after update event to place the Current/Last cost in another
field, then when the costs change, the store value in "another" field will
not change. However, if you update the combo box, the code would update the
"another" field again with the current, last cost.

If you don't want this to happen, you might use code like:

If Nz(Me.txtCost,0) <> 0 Then
Me.txtCost = Me.cboIngredient1.Column(x)
End If

--
Duane Hookom
MS Access MVP
--

Bart said:
Yes it is and I did that same exact thing and it works fine, thanks for
your
reply. But I have just come across a flaw in the logic. On my form I
choose
an ingredient for ingredient 1 and it populates ingcost1 with the lastcost
for that ingredient lets say in january it is $1.00. This cost goes in
the
fiels on my production table for that day and is used in calculating cost
per
day , month , and per unit. Now in Feb there is a price increase so all
records for feb will have lets say $2.00. Will all the records prior to
the
increase stay at 1.00 or will they now get changed to 2.00.
--
Bart


Duane Hookom said:
Isn't your combo box on a form? I would include a column in the combo box
that is the "LAST" cost. You can then add code in the After Update event
of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first
column.

--
Duane Hookom
MS Access MVP
--

Bart said:
I have 2 fields ingredient1 wich is a combo box to choose an
ingredient
from a table of ingredients called material. I also have ingredient
cost
table where we update the cost of the ingredients. I have a query of
the
inredientcost tabel that list each ingredient with the LAST cost from
the
ingredient cost table. in the after update event on the combo box I
would
like to populate the ingcost1 field with the corresponding value from
this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
Click to expand...
Click to expand...
Click to expand...
 
sorry about slow response, had to leave town.
Ok, If You say if I update the combo box in my form the values will change.
is that only if i go back to the earlier dates and update . and this code
you wrote will prevent that from happening correct.

THank you for your patience.
--
Bart


Duane Hookom said:
If you use the after update event to place the Current/Last cost in another
field, then when the costs change, the store value in "another" field will
not change. However, if you update the combo box, the code would update the
"another" field again with the current, last cost.

If you don't want this to happen, you might use code like:

If Nz(Me.txtCost,0) <> 0 Then
Me.txtCost = Me.cboIngredient1.Column(x)
End If

--
Duane Hookom
MS Access MVP
--

Bart said:
Yes it is and I did that same exact thing and it works fine, thanks for
your
reply. But I have just come across a flaw in the logic. On my form I
choose
an ingredient for ingredient 1 and it populates ingcost1 with the lastcost
for that ingredient lets say in january it is $1.00. This cost goes in
the
fiels on my production table for that day and is used in calculating cost
per
day , month , and per unit. Now in Feb there is a price increase so all
records for feb will have lets say $2.00. Will all the records prior to
the
increase stay at 1.00 or will they now get changed to 2.00.
--
Bart


Duane Hookom said:
Isn't your combo box on a form? I would include a column in the combo box
that is the "LAST" cost. You can then add code in the After Update event
of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first
column.

--
Duane Hookom
MS Access MVP
--

I have 2 fields ingredient1 wich is a combo box to choose an
ingredient
from a table of ingredients called material. I also have ingredient
cost
table where we update the cost of the ingredients. I have a query of
the
inredientcost tabel that list each ingredient with the LAST cost from
the
ingredient cost table. in the after update event on the combo box I
would
like to populate the ingcost1 field with the corresponding value from
this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
Click to expand...
Click to expand...
Click to expand...
 
If you update the combo box and the cost value in the text box "txtCost" is
0 or null then the value from the combo box column will be updated.
--
Duane Hookom
MS Access MVP
--

Bart said:
sorry about slow response, had to leave town.
Ok, If You say if I update the combo box in my form the values will
change.
is that only if i go back to the earlier dates and update . and this code
you wrote will prevent that from happening correct.

THank you for your patience.
--
Bart


Duane Hookom said:
If you use the after update event to place the Current/Last cost in
another
field, then when the costs change, the store value in "another" field
will
not change. However, if you update the combo box, the code would update
the
"another" field again with the current, last cost.

If you don't want this to happen, you might use code like:

If Nz(Me.txtCost,0) <> 0 Then
Me.txtCost = Me.cboIngredient1.Column(x)
End If

--
Duane Hookom
MS Access MVP
--

Bart said:
Yes it is and I did that same exact thing and it works fine, thanks for
your
reply. But I have just come across a flaw in the logic. On my form I
choose
an ingredient for ingredient 1 and it populates ingcost1 with the
lastcost
for that ingredient lets say in january it is $1.00. This cost goes in
the
fiels on my production table for that day and is used in calculating
cost
per
day , month , and per unit. Now in Feb there is a price increase so
all
records for feb will have lets say $2.00. Will all the records prior
to
the
increase stay at 1.00 or will they now get changed to 2.00.
--
Bart


:

Isn't your combo box on a form? I would include a column in the combo
box
that is the "LAST" cost. You can then add code in the After Update
event
of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first
column.

--
Duane Hookom
MS Access MVP
--

I have 2 fields ingredient1 wich is a combo box to choose an
ingredient
from a table of ingredients called material. I also have ingredient
cost
table where we update the cost of the ingredients. I have a query
of
the
inredientcost tabel that list each ingredient with the LAST cost
from
the
ingredient cost table. in the after update event on the combo box I
would
like to populate the ingcost1 field with the corresponding value
from
this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane, this problem is solved, thanks for your help.. more questions
later. thanks a bunch.
--
Bart


Duane Hookom said:
If you update the combo box and the cost value in the text box "txtCost" is
0 or null then the value from the combo box column will be updated.
--
Duane Hookom
MS Access MVP
--

Bart said:
sorry about slow response, had to leave town.
Ok, If You say if I update the combo box in my form the values will
change.
is that only if i go back to the earlier dates and update . and this code
you wrote will prevent that from happening correct.

THank you for your patience.
--
Bart


Duane Hookom said:
If you use the after update event to place the Current/Last cost in
another
field, then when the costs change, the store value in "another" field
will
not change. However, if you update the combo box, the code would update
the
"another" field again with the current, last cost.

If you don't want this to happen, you might use code like:

If Nz(Me.txtCost,0) <> 0 Then
Me.txtCost = Me.cboIngredient1.Column(x)
End If

--
Duane Hookom
MS Access MVP
--

Yes it is and I did that same exact thing and it works fine, thanks for
your
reply. But I have just come across a flaw in the logic. On my form I
choose
an ingredient for ingredient 1 and it populates ingcost1 with the
lastcost
for that ingredient lets say in january it is $1.00. This cost goes in
the
fiels on my production table for that day and is used in calculating
cost
per
day , month , and per unit. Now in Feb there is a price increase so
all
records for feb will have lets say $2.00. Will all the records prior
to
the
increase stay at 1.00 or will they now get changed to 2.00.
--
Bart


:

Isn't your combo box on a form? I would include a column in the combo
box
that is the "LAST" cost. You can then add code in the After Update
event
of
the combo box to update a text box bound to the Cost field.

Me.txtCost = Me.cboIngredient1.Column(x)

x is the column number from the combo box where 0 would be the first
column.

--
Duane Hookom
MS Access MVP
--

I have 2 fields ingredient1 wich is a combo box to choose an
ingredient
from a table of ingredients called material. I also have ingredient
cost
table where we update the cost of the ingredients. I have a query
of
the
inredientcost tabel that list each ingredient with the LAST cost
from
the
ingredient cost table. in the after update event on the combo box I
would
like to populate the ingcost1 field with the corresponding value
from
this
query. I tried something like this.

Me!IngCost1 = Query![material] !Cost

Thanks
Bart
Click to expand...
Click to expand...
Click to expand...
 
Back
Top