G
Guest
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.
it in table 2 record for wich my form is for.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Vinson
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]
G
Guest
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
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
J
John Vinson
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]
G
Guest
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
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
G
Guest
help anyone, please
J
John Vinson
help anyone, please
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]
G
Guest
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.
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.
J
John Vinson
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]
G
Guest
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.
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.
J
John Vinson
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.
Please post the following information:
- The Control Source and name of the combo box and the textbox
- The Row Source property of the combo (and the fields in the table if
it's just a table)
- The VBA code in the AfterUpdate event
John W. Vinson[MVP]
G
Guest
Combo box
name material 1
Controll source ingredient 1
Row source SELECT ingredients.ID, ingredients.[material name],
ingredients.unitcost FROM ingredients;
ingredient table name is ingredients
date
material name
unit cost
source
Daily Production Table name is Daily Block
ingredient 1
cost 1
ingredient 2
cost 2
ingredient 3
cost 3
there may be 5 up to 7 ingredients used on any given day and ingredient may
change from day to day depending on what type of block are made.
thanks john
ps I tried fooling around with the after upday code and now i am getting
errors is there a way to clear the vba codes i put in.
name material 1
Controll source ingredient 1
Row source SELECT ingredients.ID, ingredients.[material name],
ingredients.unitcost FROM ingredients;
ingredient table name is ingredients
date
material name
unit cost
source
Daily Production Table name is Daily Block
ingredient 1
cost 1
ingredient 2
cost 2
ingredient 3
cost 3
there may be 5 up to 7 ingredients used on any given day and ingredient may
change from day to day depending on what type of block are made.
thanks john
ps I tried fooling around with the after upday code and now i am getting
errors is there a way to clear the vba codes i put in.
J
John Vinson
Answers inline.
You might want to add ORDER BY [Material name] to the SQL so the
materials are sorted alphabetically.
This table is INCORRECTLY STRUCTURED. You're making the very common
error of "committing spreadsheet". In a relational database, tables
should grow DOWN, not across. You should have *TWO* tables in a one to
many relationship; the "one" table would have information about the
"daily block" - but *nothing* about ingredients; the other table would
have a link to the Daily Block table, an IngredientID, and a Cost
field. If a batch has five ingredients the table will have five rows
for that batch; if it has eight, eight rows (which would be impossible
to handle in your current design!). It then becomes much, much easier
to sum up the cost for a batch.
Open the VBA editor, select the code using the mouse or by holding
dowh Shift and using the up/down arrow keys, and press the Delete key.
It's just a text editor - you can edit the VBA code just as you would
edit text in Notepad or Word.
You didn't post your code so I cannot help with your question as to
why the code is not working.
John W. Vinson[MVP]
Combo box
name material 1
Controll source ingredient 1
Row source SELECT ingredients.ID, ingredients.[material name],
ingredients.unitcost FROM ingredients;
You might want to add ORDER BY [Material name] to the SQL so the
materials are sorted alphabetically.
ingredient table name is ingredients
date
material name
unit cost
source
Daily Production Table name is Daily Block
ingredient 1
cost 1
ingredient 2
cost 2
ingredient 3
cost 3
there may be 5 up to 7 ingredients used on any given day and ingredient may
change from day to day depending on what type of block are made.
This table is INCORRECTLY STRUCTURED. You're making the very common
error of "committing spreadsheet". In a relational database, tables
should grow DOWN, not across. You should have *TWO* tables in a one to
many relationship; the "one" table would have information about the
"daily block" - but *nothing* about ingredients; the other table would
have a link to the Daily Block table, an IngredientID, and a Cost
field. If a batch has five ingredients the table will have five rows
for that batch; if it has eight, eight rows (which would be impossible
to handle in your current design!). It then becomes much, much easier
to sum up the cost for a batch.
thanks john
ps I tried fooling around with the after upday code and now i am getting
errors is there a way to clear the vba codes i put in.
Open the VBA editor, select the code using the mouse or by holding
dowh Shift and using the up/down arrow keys, and press the Delete key.
It's just a text editor - you can edit the VBA code just as you would
edit text in Notepad or Word.
You didn't post your code so I cannot help with your question as to
why the code is not working.
John W. Vinson[MVP]
G
Guest
So, you say I should design a form that will put data into each different
table.
I have many tables for this project
employess for hours
ingedients for cost
block for blocky type
customers for customer name
would one form be able to put data into each table and when i need the
produciton report i would then use a common field like the date to compile a
report.
table.
I have many tables for this project
employess for hours
ingedients for cost
block for blocky type
customers for customer name
would one form be able to put data into each table and when i need the
produciton report i would then use a common field like the date to compile a
report.
John Vinson said:Answers inline.
Combo box
name material 1
Controll source ingredient 1
Row source SELECT ingredients.ID, ingredients.[material name],
ingredients.unitcost FROM ingredients;
You might want to add ORDER BY [Material name] to the SQL so the
materials are sorted alphabetically.
ingredient table name is ingredients
date
material name
unit cost
source
Daily Production Table name is Daily Block
ingredient 1
cost 1
ingredient 2
cost 2
ingredient 3
cost 3
there may be 5 up to 7 ingredients used on any given day and ingredient may
change from day to day depending on what type of block are made.
This table is INCORRECTLY STRUCTURED. You're making the very common
error of "committing spreadsheet". In a relational database, tables
should grow DOWN, not across. You should have *TWO* tables in a one to
many relationship; the "one" table would have information about the
"daily block" - but *nothing* about ingredients; the other table would
have a link to the Daily Block table, an IngredientID, and a Cost
field. If a batch has five ingredients the table will have five rows
for that batch; if it has eight, eight rows (which would be impossible
to handle in your current design!). It then becomes much, much easier
to sum up the cost for a batch.
thanks john
ps I tried fooling around with the after upday code and now i am getting
errors is there a way to clear the vba codes i put in.
Open the VBA editor, select the code using the mouse or by holding
dowh Shift and using the up/down arrow keys, and press the Delete key.
It's just a text editor - you can edit the VBA code just as you would
edit text in Notepad or Word.
You didn't post your code so I cannot help with your question as to
why the code is not working.
John W. Vinson[MVP]
J
John Vinson
So, you say I should design a form that will put data into each different
table.
I have many tables for this project
employess for hours
ingedients for cost
block for blocky type
customers for customer name
would one form be able to put data into each table and when i need the
produciton report i would then use a common field like the date to compile a
report.
Bart, I didn't address any of these issues in my post.
But yes, one form - with subforms and combo boxes and other
appropriate tools - can do this. You'ld store the information in the
various tables as needed; the Report would not be based on a
"production report table", but on a Query joining the various tables.
That is you'ld pull the employee name from the Employees table, the
customer name from the Customers table, etc. etc.
John W. Vinson[MVP]
G
Guest
Ok back to problem at hand,
Private Sub cboingredient1_AfterUpdate()
Me!txtcost1 = cboingredient1.Column(2)
End Sub
this is the code for my after update it does not work as written
Private Sub cboingredient1_AfterUpdate()
Me!txtcost1 = cboingredient1.Column(2)
End Sub
this is the code for my after update it does not work as written
J
John Vinson
Ok back to problem at hand,
Private Sub cboingredient1_AfterUpdate()
Me!txtcost1 = cboingredient1.Column(2)
End Sub
this is the code for my after update it does not work as written
I have no idea why it wouldn't. Remember, though, that the Column()
property is zero based - this would store the *third* field in
cboIngredient's RowSource query.
John W. Vinson[MVP]
G
Guest
this is the combo box row source
SELECT ingredients.ID, ingredients.[material name], ingredients.unitcost
FROM ingredients;
and the combo box name is ingredient1
and the text box name is cost1
thanks again for your help. bart
SELECT ingredients.ID, ingredients.[material name], ingredients.unitcost
FROM ingredients;
and the combo box name is ingredient1
and the text box name is cost1
thanks again for your help. bart
J
John Vinson
this is the combo box row source
SELECT ingredients.ID, ingredients.[material name], ingredients.unitcost
FROM ingredients;
and the combo box name is ingredient1
and the text box name is cost1
thanks again for your help. bart
Thank you Bart.
I do not know what is wrong.
I do not know what error you are getting or what specifically is
happening. I cannot see your computer.
I suggest that you start a new thread explaining the problem, bearing
in mind that the volunteers here cannot see your computer and will not
be readily able to go back and reread all your prior messages.
I wish I could help but I'm now completely at a loss for what might be
wrong.
John W. Vinson[MVP]