using dlookup

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.

On Thu, 20 Jan 2005 17:09:03 -0800, bigdog
wrote:

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.

DLookUp is neither the only nor the best way to do this.

If you in fact want to store the rate in two different tables (if the
rate doesn't change then you should NOT do so), you can do this using
a Combo Box on the form. Simply use the rate table as the Row Source
of a combo box, and use the rate as the Bound Column and the Control
Source.

John W. Vinson[MVP]

John,
I have 2 tables one stores the cost of ingredients eg. cement cost = .123
these cost may or may not go up throught the year. then on a daily
production report i have a formula using cement as one of the ingredients and
would like my form to automatically fill in a text box with the latest cement
cost and have that value stored in daily production table for that particular
record on that day. I have tried to use dlookup but only return #name? error.


daily block![cemrate]=DLookUp("[unitcost]","[ingredients]","[material name]
= 'cement'")
daily block is a table has a field cemrate
ingredients is a table has a field unitcost
material name is a field in ingredients where there are 7 different records
stored each with a different name on of them being "cement
I have tried many different ways of doing this using dlookup no good results
thank for you help




"John Vinson" wrote:

On Thu, 20 Jan 2005 17:09:03 -0800, bigdog
wrote:

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.
DLookUp is neither the only nor the best way to do this.

If you in fact want to store the rate in two different tables (if the
rate doesn't change then you should NOT do so), you can do this using
a Combo Box on the form. Simply use the rate table as the Row Source
of a combo box, and use the rate as the Bound Column and the Control
Source.

John W. Vinson[MVP]

On Fri, 21 Jan 2005 03:41:03 -0800, "Bart"
wrote:

John,
I have 2 tables one stores the cost of ingredients eg. cement cost = .123
these cost may or may not go up throught the year. then on a daily
production report i have a formula using cement as one of the ingredients and
would like my form to automatically fill in a text box with the latest cement
cost and have that value stored in daily production table for that particular
record on that day. I have tried to use dlookup but only return #name? error.


I'd suggest, then, that you again don't need to use DLookUp at all.

Instead, use a tiny bit of VBA code. On your Form, have a Combo Box
(cboIngredient) based on a query of the ingredients table, and a
textbox (txtUnitCost) bound to the field for the current cost. Include
the field for the cost in the Combo's row source query. For instance,
the query might be something like

SELECT Ingredients.IngredientID, Ingredients.ProductName,
Ingredients.UnitCost FROM Ingredients ORDER BY ProductName;

In the AfterUpdate event of cboIngredient, put code like

Private Sub cboIngredient_AfterUpdate()
Me!txtUnitCost = cboIngredient.Column(2)
End Sub

to "push" the value in the third column of the combo (it's zero based)
into the bound textbox.

John W. Vinson[MVP]

John,
I have tried using the vbl code
in after updat i wrote this.
Private Sub material_name_1_AfterUpdate() Me!txtcem cost =
cboingredients.column2
End Sub
in the sub query it says this
SELECT ingredients.ID, ingredients.[material name], ingredients.unitcost
FROM ingredients ORDER BY [material name];
the combo box is workin ok, but the value in the text box is not updating
when the combo box value is changed. Tab order is correct.
I have 10 ingredients in the ingredient table on any given day there will be
up to 6 ingredients used. I was going to have six combo boxes and six text
boxes. text boxes bound to cost 1 thru 6 and combo boxes bound to ingredient
name 1 thru 6.
there may be an esier way to do this . I apreciate all the help you have
given me,
I am fairly new to access . thanks john

Bart

"John Vinson" wrote:

On Fri, 21 Jan 2005 03:41:03 -0800, "Bart"
wrote:

John,
I have 2 tables one stores the cost of ingredients eg. cement cost = .123
these cost may or may not go up throught the year. then on a daily
production report i have a formula using cement as one of the ingredients and
would like my form to automatically fill in a text box with the latest cement
cost and have that value stored in daily production table for that particular
record on that day. I have tried to use dlookup but only return #name? error.


I'd suggest, then, that you again don't need to use DLookUp at all.

Instead, use a tiny bit of VBA code. On your Form, have a Combo Box
(cboIngredient) based on a query of the ingredients table, and a
textbox (txtUnitCost) bound to the field for the current cost. Include
the field for the cost in the Combo's row source query. For instance,
the query might be something like

SELECT Ingredients.IngredientID, Ingredients.ProductName,
Ingredients.UnitCost FROM Ingredients ORDER BY ProductName;

In the AfterUpdate event of cboIngredient, put code like

Private Sub cboIngredient_AfterUpdate()
Me!txtUnitCost = cboIngredient.Column(2)
End Sub

to "push" the value in the third column of the combo (it's zero based)
into the bound textbox.

John W. Vinson[MVP]

help anyone, please


"bigdog" wrote:

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.

On Wed, 2 Feb 2005 09:35:12 -0800, "Bart"
wrote:

help anyone, please


"bigdog" wrote:

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.

I replied on January 20; perhaps it didn't reach your news server. To
repeat:

DLookUp is neither the only nor the best way to do this.

If you in fact want to store the rate in two different tables (if the
rate doesn't change then you should NOT do so), you can do this using
a Combo Box on the form. Simply use the rate table as the Row Source
of a combo box, and use the rate as the Bound Column and the Control
Source. To actually store the data, use the AfterUpdate event of the
combo to "push" the rate into the textbox:

Private Sub cboRate_AfterUpdate()
Me!Rate = cboRate.Column(1)
End Sub

Note that the Column property is zero based, so this would be the
second column of the combo box.

John W. Vinson[MVP]

OK, I have a tabel for ingredients ( several ingredients ) three fields
date name and cost
I have another table daily production where I am using a form to pull data
in from different tables along with input of daily information. then there
will be a report that does a daily produciton cost . This report will use the
cost of each ingredient for each specific day. the ingredient cost may go
up every month or may stay the same but for calculating purpose I thought
storing the ingredient cost on the daily production report would insure the
cost would be calculated at the correct rate each time. But that would be
storing the cost in two different tables. should i just leave that cost data
in the ingredient table. When I calculat a year to date average of daily
cost how will it effect my data.

"John Vinson" wrote:

On Wed, 2 Feb 2005 09:35:12 -0800, "Bart"
wrote:

help anyone, please


"bigdog" wrote:

in my form how can i have a field look up a rate in table 1 and have it put
it in table 2 record for wich my form is for.

I replied on January 20; perhaps it didn't reach your news server. To
repeat:

DLookUp is neither the only nor the best way to do this.

If you in fact want to store the rate in two different tables (if the
rate doesn't change then you should NOT do so), you can do this using
a Combo Box on the form. Simply use the rate table as the Row Source
of a combo box, and use the rate as the Bound Column and the Control
Source. To actually store the data, use the AfterUpdate event of the
combo to "push" the rate into the textbox:

Private Sub cboRate_AfterUpdate()
Me!Rate = cboRate.Column(1)
End Sub

Note that the Column property is zero based, so this would be the
second column of the combo box.

John W. Vinson[MVP]

On Wed, 2 Feb 2005 15:43:02 -0800, "Bart"
wrote:

OK, I have a tabel for ingredients ( several ingredients ) three fields
date name and cost
I have another table daily production where I am using a form to pull data
in from different tables along with input of daily information. then there
will be a report that does a daily produciton cost . This report will use the
cost of each ingredient for each specific day. the ingredient cost may go
up every month or may stay the same but for calculating purpose I thought
storing the ingredient cost on the daily production report would insure the
cost would be calculated at the correct rate each time. But that would be
storing the cost in two different tables. should i just leave that cost data
in the ingredient table. When I calculat a year to date average of daily
cost how will it effect my data.

The cost does indeed need to be in both tables, then; the cost in the
Ingredient table is the "cost as of the current moment" whereas the
cost in the Production table is the "cost as of the time of
production".

Use my second suggestion, using the AfterUpdate event of the Form
combo box.

John W. Vinson[MVP]

John,
I did try your suguestion the combo box worked ok, I used the wizard to set
it up and bound it to material 1 then bound a tex box to ingredient cost 1
used your vb code in the afterupdate and the text box would show the cost
for the the first ingredient in the table, but when the combo box is changed
the cost stayed the same it does not change.


"John Vinson" wrote:

On Wed, 2 Feb 2005 15:43:02 -0800, "Bart"
wrote:

OK, I have a tabel for ingredients ( several ingredients ) three fields
date name and cost
I have another table daily production where I am using a form to pull data
in from different tables along with input of daily information. then there
will be a report that does a daily produciton cost . This report will use the
cost of each ingredient for each specific day. the ingredient cost may go
up every month or may stay the same but for calculating purpose I thought
storing the ingredient cost on the daily production report would insure the
cost would be calculated at the correct rate each time. But that would be
storing the cost in two different tables. should i just leave that cost data
in the ingredient table. When I calculat a year to date average of daily
cost how will it effect my data.

The cost does indeed need to be in both tables, then; the cost in the
Ingredient table is the "cost as of the current moment" whereas the
cost in the Production table is the "cost as of the time of
production".

Use my second suggestion, using the AfterUpdate event of the Form
combo box.

John W. Vinson[MVP]