Attn Sprinks - clarification on VB code

[babs]

Posted this again today since didn't here back sorry - A little anxious to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A dialog
box pops up that says: Query Input must contain at least one Table or Query.
then I have to click on okay. and records in BatchIngredients subform don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo box on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the Table
name BatchIngredient???? or should it be the subform name. I tried that and
got the same error.

One other issue I have is that the Ingredients table has two primary keys -
code and vendor, since one raw material can come from more than one vendor.
Really just want the Batch ingredients to be populated with the raw mat. code
for a given Semiproduct. the lot # or (Batch#) will indicate which vendor it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated with the
product. You’ll calculate the quantities of the batch ingredients from it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your main
Batches form to execute an Insert query. Not being a skilled SQL jockey, I
prefer to create queries in Design View. Once saved, you can execute them
with the OpenQuery method, which the Command Button wizard will do for you.
Alternatively, you can switch to SQL view from query design view, and cut and
paste the query’s SQL into your command button procedure, and use the RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the fraction
field from ProductIngredients to calculate the ingredient quantities, and the
Ingredient field from Ingredients to display the name of the ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields to the
query grid, and also Ingredients.Ingredient (the text field not the numeric
code) and ProductIngredients.Fraction.

After you’ve saved the query, load your BatchIngredients subform in Design
View and change the form’s RecordSource from the table BatchIngredients to
your new query. Place the controls you need. Remember you don’t need or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it with
Wizards enabled so that it will create the shell for you. Select any of the
options, and then edit it and delete all the action code, but preserve the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1 " & _
"FROM ProductIngredient " & _
"WHERE (((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you’ll see you pick up the BatchID (the linking field
for the form/subform) from the main form, and the IngredientID’s from
ProductIngredient, using the form’s ProductID to select the records. Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line except the
last (within the quotes) or the string won’t be valid SQL.

Finally, the last statement requeries the subform so that it can display the
new records.

Since the only field you’ll need your operators to edit is the ingredient’s
Batch Number, you can set the other controls’ Enabled property to No or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free to ask
them.

Sprinks

Told coworker I would attemp to help her get this figured out. Please help

See Below, I did all as FOLLOWS however get stuck on the advice AFter I
created the Batch and Batch Ingredients Table. Should the Quantity to be
produce be in the BAtch table. Not sure how to DO the REst that is after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend to do
completely in Access once you have your basic percentage formulas entered,
i.e., there will be no ongoing requirement to import data from Excel.

If this is the case, I think you will require several tables for this job.
All of them are simple in themselves; splitting them into multiple tables
will make using it so much easier than in Excel alone because you will be
harnessing the power of a relational database.

You didn’t mention it in this post, but I’m inferring from your initial post
that you intend also to keep a record of each batch that would include the
quantity of final product produced, the final product batch number, perhaps
the date and/or operator, and the batch number of each raw material used. Is
this correct?

If this is so, you have the following relationships to account for in your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an ingredient may
be used in many products. The best way to represent this relationship is
with two one-to-many relationships. Based on what you’ve told me, I suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
…any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
…any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your Products
and raw materials, respectively. The last stores your basic formula to
produce 1 unit of product, and represents the 1-to-many relationships to each
of them.

To enter the formulae, I would create a main form based on the Products
table, and a subform based on ProductIngredients, representing foreign keys
with combo boxes to facilitate the data entry. A control in the subform’s
footer would be handy to ensure that the total of the fraction field = 1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have been
entered, it will be easy to calculate the required amounts for any size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
…any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select the
Product ID from a combo box, and enter the Qty to be produced. At some
event, either by pressing a button or from the combo box’ AfterUpdate event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from the
ProductIngredients table for each ingredient in this product. Probably the
easiest way to do this is to create the multi-table query in Design mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients, the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you’re done.

HTH
Sprinks

thanks for the advice on the transpose copy paste but still more questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat name lot #
ABC 1" masking tape 232 rubber 45
2828 toluene 11
376 mineral spt. 49

As you see the finished prod # and name need to be listed once and then the
ingredients that go into it will tak up numerous records if I list them down
instead of across. I would like to generate a form automatically with the
ingredients so production can enter the lot # they used without haveing to
put the raw matl. code and name in. Is this a place where I need a sub
form(subtable- is there such a thing?. Not sure how to set up the underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special (Transpose)
to rearrange the data so that the columns become rows (and vice versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product and need to
follow lot #s of raw materials put into them and back calcuate quantities of
raw materials used based on the final quantity of product produced.

I have started with one table of the list of raw material recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll
say that these three products make tape ABC,1" masking tape which is the
final product id and name. Every final product has a list of raw materials
and percent of what goes in to make a given final quantity. I have an excel
spreadsheet that lists each final product#, name and across as column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of across. In the
raw material table the items are listed down. I would like to generate a
form for production when they are making the 1" tape(final prod) that would
list the raw material that go into it and would give them a field to put in
the lot # and the qty that they used next to each raw material. I can see
grabbing this from a table created from the excel spreadsheet example but
find it odd that the ex. rubber is now a field name in that table.

Please help - on this table layout!!!
Thanks,Barb

 

[Michel Walsh]

Hi,


add space around the keywords. If you debug.print your strSQL:


Debug.Print strSQL


after you filled it, and before you used it in your DoCmd statement, you
should see something like:

.... AS Expr1FROM Semi....


while you probably intend


... AS Expr1 FROM Semi...

Your statement is read as is you want the alias name EXPR1FROM ... and
no FROM clause if found.


Same pattern with the SELECT (not critical since preceded by a closing
parenthesis) and WHERE (for which it is critical) keywords. Add at least a
SPACE before and after the keywords, for safety, add two, so you will
immediately spot them, when you build the statement, through strings
concatenation. The concatenation operator, &, DOES NOT do it for you.



Hoping it may help,
Vanderghast, Access MVP

Posted this again today since didn't here back sorry - A little anxious to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run
the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A dialog
box pops up that says: Query Input must contain at least one Table or
Query.
then I have to click on okay. and records in BatchIngredients subform
don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo box
on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the Table
name BatchIngredient???? or should it be the subform name. I tried that
and
got the same error.

One other issue I have is that the Ingredients table has two primary
keys -
code and vendor, since one raw material can come from more than one
vendor.
Really just want the Batch ingredients to be populated with the raw mat.
code
for a given Semiproduct. the lot # or (Batch#) will indicate which vendor
it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated with
the
product. You'll calculate the quantities of the batch ingredients from
it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your main
Batches form to execute an Insert query. Not being a skilled SQL jockey,
I
prefer to create queries in Design View. Once saved, you can execute
them
with the OpenQuery method, which the Command Button wizard will do for
you.
Alternatively, you can switch to SQL view from query design view, and cut
and
paste the query's SQL into your command button procedure, and use the
RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the
fraction
field from ProductIngredients to calculate the ingredient quantities, and
the
Ingredient field from Ingredients to display the name of the ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields to
the
query grid, and also Ingredients.Ingredient (the text field not the
numeric
code) and ProductIngredients.Fraction.

After you've saved the query, load your BatchIngredients subform in
Design
View and change the form's RecordSource from the table BatchIngredients
to
your new query. Place the controls you need. Remember you don't need or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it
with
Wizards enabled so that it will create the shell for you. Select any of
the
options, and then edit it and delete all the action code, but preserve
the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1 "
& _
"FROM ProductIngredient " & _
"WHERE (((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you'll see you pick up the BatchID (the linking
field
for the form/subform) from the main form, and the IngredientID's from
ProductIngredient, using the form's ProductID to select the records.
Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line except
the
last (within the quotes) or the string won't be valid SQL.

Finally, the last statement requeries the subform so that it can display
the
new records.

Since the only field you'll need your operators to edit is the ingredient's
Batch Number, you can set the other controls' Enabled property to No or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free to
ask
them.

Sprinks

Told coworker I would attemp to help her get this figured out. Please
help

See Below, I did all as FOLLOWS however get stuck on the advice AFter
I
created the Batch and Batch Ingredients Table. Should the Quantity to
be
produce be in the BAtch table. Not sure how to DO the REst that is
after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend to
do
completely in Access once you have your basic percentage formulas
entered,
i.e., there will be no ongoing requirement to import data from Excel.

If this is the case, I think you will require several tables for this
job.
All of them are simple in themselves; splitting them into multiple
tables
will make using it so much easier than in Excel alone because you will
be
harnessing the power of a relational database.

You didn't mention it in this post, but I'm inferring from your initial
post
that you intend also to keep a record of each batch that would include
the
quantity of final product produced, the final product batch number,
perhaps
the date and/or operator, and the batch number of each raw material
used. Is
this correct?

If this is so, you have the following relationships to account for in
your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an ingredient
may
be used in many products. The best way to represent this relationship
is
with two one-to-many relationships. Based on what you've told me, I
suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
.any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
.any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your
Products
and raw materials, respectively. The last stores your basic formula to
produce 1 unit of product, and represents the 1-to-many relationships
to each
of them.

To enter the formulae, I would create a main form based on the Products
table, and a subform based on ProductIngredients, representing foreign
keys
with combo boxes to facilitate the data entry. A control in the
subform's
footer would be handy to ensure that the total of the fraction field =
1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have
been
entered, it will be easy to calculate the required amounts for any size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
.any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select the
Product ID from a combo box, and enter the Qty to be produced. At some
event, either by pressing a button or from the combo box' AfterUpdate
event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from
the
ProductIngredients table for each ingredient in this product. Probably
the
easiest way to do this is to create the multi-table query in Design
mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients, the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you're done.

HTH
Sprinks


:

thanks for the advice on the transpose copy paste but still more
questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat name
lot #
ABC 1" masking tape 232 rubber
45
2828 toluene
11
376 mineral
spt. 49

As you see the finished prod # and name need to be listed once and
then the
ingredients that go into it will tak up numerous records if I list
them down
instead of across. I would like to generate a form automatically
with the
ingredients so production can enter the lot # they used without
haveing to
put the raw matl. code and name in. Is this a place where I need a
sub
form(subtable- is there such a thing?. Not sure how to set up the
underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special
(Transpose)
to rearrange the data so that the columns become rows (and vice
versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product
and need to
follow lot #s of raw materials put into them and back calcuate
quantities of
raw materials used based on the final quantity of product
produced.

I have started with one table of the list of raw material
recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral
spirits) we'll
say that these three products make tape ABC,1" masking tape which
is the
final product id and name. Every final product has a list of raw
materials
and percent of what goes in to make a given final quantity. I
have an excel
spreadsheet that lists each final product#, name and across as
column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of
across. In the
raw material table the items are listed down. I would like to
generate a
form for production when they are making the 1" tape(final prod)
that would
list the raw material that go into it and would give them a field
to put in
the lot # and the qty that they used next to each raw material.
I can see
grabbing this from a table created from the excel spreadsheet
example but
find it odd that the ex. rubber is now a field name in that
table.

Please help - on this table layout!!!
Thanks,Barb

 

[babs]

Thanks for the advice. Not sure where to put the Debug.Print SQL exactly. I
took out the concatenation operators and added spacing but now getting syntax
errors. Not sure what or where EXACTLY I need to add spacing. Please keep
helping.

thanks,
Barb

Hi,


add space around the keywords. If you debug.print your strSQL:


Debug.Print strSQL


after you filled it, and before you used it in your DoCmd statement, you
should see something like:

.... AS Expr1FROM Semi....


while you probably intend


... AS Expr1 FROM Semi...

Your statement is read as is you want the alias name EXPR1FROM ... and
no FROM clause if found.


Same pattern with the SELECT (not critical since preceded by a closing
parenthesis) and WHERE (for which it is critical) keywords. Add at least a
SPACE before and after the keywords, for safety, add two, so you will
immediately spot them, when you build the statement, through strings
concatenation. The concatenation operator, &, DOES NOT do it for you.



Hoping it may help,
Vanderghast, Access MVP

Posted this again today since didn't here back sorry - A little anxious to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run
the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A dialog
box pops up that says: Query Input must contain at least one Table or
Query.
then I have to click on okay. and records in BatchIngredients subform
don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo box
on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the Table
name BatchIngredient???? or should it be the subform name. I tried that
and
got the same error.

One other issue I have is that the Ingredients table has two primary
keys -
code and vendor, since one raw material can come from more than one
vendor.
Really just want the Batch ingredients to be populated with the raw mat.
code
for a given Semiproduct. the lot # or (Batch#) will indicate which vendor
it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated with
the
product. You'll calculate the quantities of the batch ingredients from
it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your main
Batches form to execute an Insert query. Not being a skilled SQL jockey,
I
prefer to create queries in Design View. Once saved, you can execute
them
with the OpenQuery method, which the Command Button wizard will do for
you.
Alternatively, you can switch to SQL view from query design view, and cut
and
paste the query's SQL into your command button procedure, and use the
RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the
fraction
field from ProductIngredients to calculate the ingredient quantities, and
the
Ingredient field from Ingredients to display the name of the ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields to
the
query grid, and also Ingredients.Ingredient (the text field not the
numeric
code) and ProductIngredients.Fraction.

After you've saved the query, load your BatchIngredients subform in
Design
View and change the form's RecordSource from the table BatchIngredients
to
your new query. Place the controls you need. Remember you don't need or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it
with
Wizards enabled so that it will create the shell for you. Select any of
the
options, and then edit it and delete all the action code, but preserve
the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1 "
& _
"FROM ProductIngredient " & _
"WHERE (((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you'll see you pick up the BatchID (the linking
field
for the form/subform) from the main form, and the IngredientID's from
ProductIngredient, using the form's ProductID to select the records.
Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line except
the
last (within the quotes) or the string won't be valid SQL.

Finally, the last statement requeries the subform so that it can display
the
new records.

Since the only field you'll need your operators to edit is the ingredient's
Batch Number, you can set the other controls' Enabled property to No or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free to
ask
them.

Sprinks


:

Told coworker I would attemp to help her get this figured out. Please
help

See Below, I did all as FOLLOWS however get stuck on the advice AFter
I
created the Batch and Batch Ingredients Table. Should the Quantity to
be
produce be in the BAtch table. Not sure how to DO the REst that is
after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend to
do
completely in Access once you have your basic percentage formulas
entered,
i.e., there will be no ongoing requirement to import data from Excel.

If this is the case, I think you will require several tables for this
job.
All of them are simple in themselves; splitting them into multiple
tables
will make using it so much easier than in Excel alone because you will
be
harnessing the power of a relational database.

You didn't mention it in this post, but I'm inferring from your initial
post
that you intend also to keep a record of each batch that would include
the
quantity of final product produced, the final product batch number,
perhaps
the date and/or operator, and the batch number of each raw material
used. Is
this correct?

If this is so, you have the following relationships to account for in
your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an ingredient
may
be used in many products. The best way to represent this relationship
is
with two one-to-many relationships. Based on what you've told me, I
suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
.any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
.any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your
Products
and raw materials, respectively. The last stores your basic formula to
produce 1 unit of product, and represents the 1-to-many relationships
to each
of them.

To enter the formulae, I would create a main form based on the Products
table, and a subform based on ProductIngredients, representing foreign
keys
with combo boxes to facilitate the data entry. A control in the
subform's
footer would be handy to ensure that the total of the fraction field =
1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have
been
entered, it will be easy to calculate the required amounts for any size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
.any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select the
Product ID from a combo box, and enter the Qty to be produced. At some
event, either by pressing a button or from the combo box' AfterUpdate
event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from
the
ProductIngredients table for each ingredient in this product. Probably
the
easiest way to do this is to create the multi-table query in Design
mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients, the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you're done.

HTH
Sprinks


:

thanks for the advice on the transpose copy paste but still more
questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat name
lot #
ABC 1" masking tape 232 rubber
45
2828 toluene
11
376 mineral
spt. 49

As you see the finished prod # and name need to be listed once and
then the
ingredients that go into it will tak up numerous records if I list
them down
instead of across. I would like to generate a form automatically
with the
ingredients so production can enter the lot # they used without
haveing to
put the raw matl. code and name in. Is this a place where I need a
sub
form(subtable- is there such a thing?. Not sure how to set up the
underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special
(Transpose)
to rearrange the data so that the columns become rows (and vice
versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product
and need to
follow lot #s of raw materials put into them and back calcuate
quantities of
raw materials used based on the final quantity of product
produced.

I have started with one table of the list of raw material
recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral
spirits) we'll
say that these three products make tape ABC,1" masking tape which
is the
final product id and name. Every final product has a list of raw
materials
and percent of what goes in to make a given final quantity. I
have an excel
spreadsheet that lists each final product#, name and across as
column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of
across. In the
raw material table the items are listed down. I would like to
generate a
form for production when they are making the 1" tape(final prod)
that would
list the raw material that go into it and would give them a field
to put in
the lot # and the qty that they used next to each raw material.
I can see
grabbing this from a table created from the excel spreadsheet
example but
find it odd that the ex. rubber is now a field name in that
table.

Please help - on this table layout!!!
Thanks,Barb

 

[Sprinks]

Hi, Babs.

Yes, cboProduct refers to the combo box on the main Batch form that holds
the ProductID.

Not sure what you mean by “running the query on its own returns no recordsâ€.
If you mean that the Query By Design query doesn’t insert any records, it’s
probably because you did not have the Batch form open. The query needs the
BatchID and the ProductID from the form.

The underscore character continues a single statement on the next line,
letting you make a long statement more readable. You definitely need them.
It’s also common practice to put each clause of an SQL statement on a
separate line for the same reason. The spaces I referred to are within the
quotes., e.g.,

“INSERT INTO BatchIngredients(code, BatchID)[put space here]“ & _

The & is concatenating the substrings into a single string. It would be
helpful as you debug this to insert a line to see what your string looks like
after the assignment statement:

MsgBox strSQL

The last statement requeries the subform. If you are getting an error,
double-check the Name of the subform in the Properties window (not the
Database window—the original name of the subform and the name of the subform
*control*, which is what it became when you inserted it onto your main form,
can be different).

I also strongly urge you to avoid multiple field primary keys. Ingredients
and Manufacturers have a many-to-many relationship, just like Products and
Ingredients. They should be properly represented by an intermediate table:

IngredientManufacturers
--------------------------------
IngredientMfrID AutoNumber (Primary Key)
IngredientID Number (Foreign Key to Ingredients)
ManufacturerID Number (Foreign Key to Manufacturers)

Sprinks

 

[Michel Walsh]

Hi,


instead of

----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL
--------------


something like


----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID) " & " SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & " FROM
Semiprodingredients" &
" WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"


Debug.Print strSQL
Debug.Assert False

'Execute the query
DoCmd.RunSQL strSQL

-------------------


I also add a Debug.Assert False so the code will stop just right after the
strSQLvariable would have print its content in the debug immediate window.


Note that I also assumed the strSQL= ... line is just in one line (the
newsreader may have cut it into multiple lines).




Hoping it may help,
Vanderghast, Access MVP

Thanks for the advice. Not sure where to put the Debug.Print SQL exactly.
I
took out the concatenation operators and added spacing but now getting
syntax
errors. Not sure what or where EXACTLY I need to add spacing. Please
keep
helping.

thanks,
Barb

Hi,


add space around the keywords. If you debug.print your strSQL:


Debug.Print strSQL


after you filled it, and before you used it in your DoCmd statement, you
should see something like:

.... AS Expr1FROM Semi....


while you probably intend


... AS Expr1 FROM Semi...

Your statement is read as is you want the alias name EXPR1FROM ...
and
no FROM clause if found.


Same pattern with the SELECT (not critical since preceded by a closing
parenthesis) and WHERE (for which it is critical) keywords. Add at
least a
SPACE before and after the keywords, for safety, add two, so you will
immediately spot them, when you build the statement, through strings
concatenation. The concatenation operator, &, DOES NOT do it for you.



Hoping it may help,
Vanderghast, Access MVP

Posted this again today since didn't here back sorry - A little anxious
to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run
the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A
dialog
box pops up that says: Query Input must contain at least one Table or
Query.
then I have to click on okay. and records in BatchIngredients subform
don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo
box
on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the
Table
name BatchIngredient???? or should it be the subform name. I tried
that
and
got the same error.

One other issue I have is that the Ingredients table has two primary
keys -
code and vendor, since one raw material can come from more than one
vendor.
Really just want the Batch ingredients to be populated with the raw
mat.
code
for a given Semiproduct. the lot # or (Batch#) will indicate which
vendor
it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

:

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated
with
the
product. You'll calculate the quantities of the batch ingredients
from
it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your
main
Batches form to execute an Insert query. Not being a skilled SQL
jockey,
I
prefer to create queries in Design View. Once saved, you can execute
them
with the OpenQuery method, which the Command Button wizard will do for
you.
Alternatively, you can switch to SQL view from query design view, and
cut
and
paste the query's SQL into your command button procedure, and use the
RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the
fraction
field from ProductIngredients to calculate the ingredient quantities,
and
the
Ingredient field from Ingredients to display the name of the
ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields
to
the
query grid, and also Ingredients.Ingredient (the text field not the
numeric
code) and ProductIngredients.Fraction.

After you've saved the query, load your BatchIngredients subform in
Design
View and change the form's RecordSource from the table
BatchIngredients
to
your new query. Place the controls you need. Remember you don't need
or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to
your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it
with
Wizards enabled so that it will create the shell for you. Select any
of
the
options, and then edit it and delete all the action code, but preserve
the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1
"
& _
"FROM ProductIngredient " & _
"WHERE
(((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you'll see you pick up the BatchID (the linking
field
for the form/subform) from the main form, and the IngredientID's from
ProductIngredient, using the form's ProductID to select the records.
Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line
except
the
last (within the quotes) or the string won't be valid SQL.

Finally, the last statement requeries the subform so that it can
display
the
new records.

Since the only field you'll need your operators to edit is the
ingredient's
Batch Number, you can set the other controls' Enabled property to No
or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free
to
ask
them.

Sprinks


:

Told coworker I would attemp to help her get this figured out.
Please
help

See Below, I did all as FOLLOWS however get stuck on the advice
AFter
I
created the Batch and Batch Ingredients Table. Should the Quantity
to
be
produce be in the BAtch table. Not sure how to DO the REst that is
after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend
to
do
completely in Access once you have your basic percentage formulas
entered,
i.e., there will be no ongoing requirement to import data from
Excel.

If this is the case, I think you will require several tables for
this
job.
All of them are simple in themselves; splitting them into multiple
tables
will make using it so much easier than in Excel alone because you
will
be
harnessing the power of a relational database.

You didn't mention it in this post, but I'm inferring from your
initial
post
that you intend also to keep a record of each batch that would
include
the
quantity of final product produced, the final product batch number,
perhaps
the date and/or operator, and the batch number of each raw material
used. Is
this correct?

If this is so, you have the following relationships to account for
in
your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an
ingredient
may
be used in many products. The best way to represent this
relationship
is
with two one-to-many relationships. Based on what you've told me, I
suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
.any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
.any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your
Products
and raw materials, respectively. The last stores your basic formula
to
produce 1 unit of product, and represents the 1-to-many
relationships
to each
of them.

To enter the formulae, I would create a main form based on the
Products
table, and a subform based on ProductIngredients, representing
foreign
keys
with combo boxes to facilitate the data entry. A control in the
subform's
footer would be handy to ensure that the total of the fraction field
=
1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have
been
entered, it will be easy to calculate the required amounts for any
size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
.any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select
the
Product ID from a combo box, and enter the Qty to be produced. At
some
event, either by pressing a button or from the combo box'
AfterUpdate
event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from
the
ProductIngredients table for each ingredient in this product.
Probably
the
easiest way to do this is to create the multi-table query in Design
mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients,
the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you're
done.

HTH
Sprinks


:

thanks for the advice on the transpose copy paste but still more
questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat
name
lot #
ABC 1" masking tape 232 rubber
45
2828 toluene
11
376 mineral
spt. 49

As you see the finished prod # and name need to be listed once and
then the
ingredients that go into it will tak up numerous records if I list
them down
instead of across. I would like to generate a form automatically
with the
ingredients so production can enter the lot # they used without
haveing to
put the raw matl. code and name in. Is this a place where I need
a
sub
form(subtable- is there such a thing?. Not sure how to set up the
underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special
(Transpose)
to rearrange the data so that the columns become rows (and vice
versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product
and need to
follow lot #s of raw materials put into them and back calcuate
quantities of
raw materials used based on the final quantity of product
produced.

I have started with one table of the list of raw material
recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral
spirits) we'll
say that these three products make tape ABC,1" masking tape
which
is the
final product id and name. Every final product has a list of
raw
materials
and percent of what goes in to make a given final quantity. I
have an excel
spreadsheet that lists each final product#, name and across as
column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of
across. In the
raw material table the items are listed down. I would like to
generate a
form for production when they are making the 1" tape(final
prod)
that would
list the raw material that go into it and would give them a
field
to put in
the lot # and the qty that they used next to each raw
material.
I can see
grabbing this from a table created from the excel spreadsheet
example but
find it odd that the ex. rubber is now a field name in that
table.

Please help - on this table layout!!!
Thanks,Barb

 

[babs]

The SQL worked however it is inserting the records into the BatchIngredients
table which is what we want to do. The semicode is not inserted - don't we
want that in there. Also the subform on Batch (qryBatchIngredients) is not
getting the records added to it. I need clarification on relationship for
the query. I have 3 tables.

BatchIngredients SemiProdIngredients RawMaterial
BatchINGID(P) ProductIngredientId(P) Code(P)
BatchId Semicode productname
productIngredientId Code vendor#
code Qtyper100lb(fraction)
lot#
semicode

I got rid of the multiple vendors for this applications to hopefully
simplify it for now. Still not clear on the query??? and why subform based
on it is not getting populated.

Thanks again for you help,
barb

Hi,


instead of

----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL
--------------


something like


----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID) " & " SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & " FROM
Semiprodingredients" &
" WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"


Debug.Print strSQL
Debug.Assert False

'Execute the query
DoCmd.RunSQL strSQL

-------------------


I also add a Debug.Assert False so the code will stop just right after the
strSQLvariable would have print its content in the debug immediate window.


Note that I also assumed the strSQL= ... line is just in one line (the
newsreader may have cut it into multiple lines).




Hoping it may help,
Vanderghast, Access MVP

Thanks for the advice. Not sure where to put the Debug.Print SQL exactly.
I
took out the concatenation operators and added spacing but now getting
syntax
errors. Not sure what or where EXACTLY I need to add spacing. Please
keep
helping.

thanks,
Barb

Hi,


add space around the keywords. If you debug.print your strSQL:


Debug.Print strSQL


after you filled it, and before you used it in your DoCmd statement, you
should see something like:

.... AS Expr1FROM Semi....


while you probably intend


... AS Expr1 FROM Semi...

Your statement is read as is you want the alias name EXPR1FROM ...
and
no FROM clause if found.


Same pattern with the SELECT (not critical since preceded by a closing
parenthesis) and WHERE (for which it is critical) keywords. Add at
least a
SPACE before and after the keywords, for safety, add two, so you will
immediately spot them, when you build the statement, through strings
concatenation. The concatenation operator, &, DOES NOT do it for you.



Hoping it may help,
Vanderghast, Access MVP



Posted this again today since didn't here back sorry - A little anxious
to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run
the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A
dialog
box pops up that says: Query Input must contain at least one Table or
Query.
then I have to click on okay. and records in BatchIngredients subform
don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo
box
on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the
Table
name BatchIngredient???? or should it be the subform name. I tried
that
and
got the same error.

One other issue I have is that the Ingredients table has two primary
keys -
code and vendor, since one raw material can come from more than one
vendor.
Really just want the Batch ingredients to be populated with the raw
mat.
code
for a given Semiproduct. the lot # or (Batch#) will indicate which
vendor
it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

:

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated
with
the
product. You'll calculate the quantities of the batch ingredients
from
it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your
main
Batches form to execute an Insert query. Not being a skilled SQL
jockey,
I
prefer to create queries in Design View. Once saved, you can execute
them
with the OpenQuery method, which the Command Button wizard will do for
you.
Alternatively, you can switch to SQL view from query design view, and
cut
and
paste the query's SQL into your command button procedure, and use the
RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the
fraction
field from ProductIngredients to calculate the ingredient quantities,
and
the
Ingredient field from Ingredients to display the name of the
ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields
to
the
query grid, and also Ingredients.Ingredient (the text field not the
numeric
code) and ProductIngredients.Fraction.

After you've saved the query, load your BatchIngredients subform in
Design
View and change the form's RecordSource from the table
BatchIngredients
to
your new query. Place the controls you need. Remember you don't need
or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to
your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it
with
Wizards enabled so that it will create the shell for you. Select any
of
the
options, and then edit it and delete all the action code, but preserve
the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1
"
& _
"FROM ProductIngredient " & _
"WHERE
(((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you'll see you pick up the BatchID (the linking
field
for the form/subform) from the main form, and the IngredientID's from
ProductIngredient, using the form's ProductID to select the records.
Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line
except
the
last (within the quotes) or the string won't be valid SQL.

Finally, the last statement requeries the subform so that it can
display
the
new records.

Since the only field you'll need your operators to edit is the
ingredient's
Batch Number, you can set the other controls' Enabled property to No
or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free
to
ask
them.

Sprinks


:

Told coworker I would attemp to help her get this figured out.
Please
help

See Below, I did all as FOLLOWS however get stuck on the advice
AFter
I
created the Batch and Batch Ingredients Table. Should the Quantity
to
be
produce be in the BAtch table. Not sure how to DO the REst that is
after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend
to
do
completely in Access once you have your basic percentage formulas
entered,
i.e., there will be no ongoing requirement to import data from
Excel.

If this is the case, I think you will require several tables for
this
job.
All of them are simple in themselves; splitting them into multiple
tables
will make using it so much easier than in Excel alone because you
will
be
harnessing the power of a relational database.

You didn't mention it in this post, but I'm inferring from your
initial
post
that you intend also to keep a record of each batch that would
include
the
quantity of final product produced, the final product batch number,
perhaps
the date and/or operator, and the batch number of each raw material
used. Is
this correct?

If this is so, you have the following relationships to account for
in
your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an
ingredient
may
be used in many products. The best way to represent this
relationship
is
with two one-to-many relationships. Based on what you've told me, I
suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
.any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
.any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your
Products
and raw materials, respectively. The last stores your basic formula
to
produce 1 unit of product, and represents the 1-to-many
relationships
to each
of them.

To enter the formulae, I would create a main form based on the
Products
table, and a subform based on ProductIngredients, representing
foreign
keys
with combo boxes to facilitate the data entry. A control in the
subform's
footer would be handy to ensure that the total of the fraction field
=
1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have
been
entered, it will be easy to calculate the required amounts for any
size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
.any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select
the
Product ID from a combo box, and enter the Qty to be produced. At
some
event, either by pressing a button or from the combo box'
AfterUpdate
event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from
the
ProductIngredients table for each ingredient in this product.
Probably
the
easiest way to do this is to create the multi-table query in Design
mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients,
the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you're
done.

HTH
Sprinks


:

thanks for the advice on the transpose copy paste but still more
questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat
name
lot #
ABC 1" masking tape 232 rubber
45
2828 toluene
11
376 mineral
spt. 49

As you see the finished prod # and name need to be listed once and
then the
ingredients that go into it will tak up numerous records if I list
them down
instead of across. I would like to generate a form automatically
with the
ingredients so production can enter the lot # they used without
haveing to
put the raw matl. code and name in. Is this a place where I need
a
sub
form(subtable- is there such a thing?. Not sure how to set up the
underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special
(Transpose)
to rearrange the data so that the columns become rows (and vice
versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product
and need to
follow lot #s of raw materials put into them and back calcuate
quantities of
raw materials used based on the final quantity of product
produced.

I have started with one table of the list of raw material
recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral
spirits) we'll
say that these three products make tape ABC,1" masking tape
which
is the
final product id and name. Every final product has a list of
raw
materials
and percent of what goes in to make a given final quantity. I
have an excel
spreadsheet that lists each final product#, name and across as
column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of
across. In the
raw material table the items are listed down. I would like to
generate a
form for production when they are making the 1" tape(final
prod)
that would
list the raw material that go into it and would give them a
field
to put in
the lot # and the qty that they used next to each raw
material.
I can see
grabbing this from a table created from the excel spreadsheet
example but
find it odd that the ex. rubber is now a field name in that
table.

Please help - on this table layout!!!
Thanks,Barb

 

[babs]

ITS WORKING SO FAR SO GOOD. AS I DEVELOP MORE I AM SURE I WILL HAVE MORE
QUESTIONS. CAN I POST THEM ATTN. SPRINKS!!!!

THANKS A TON - NOW I CAN GET SOME OTHER STUFF DONE!!!

The SQL worked however it is inserting the records into the BatchIngredients
table which is what we want to do. The semicode is not inserted - don't we
want that in there. Also the subform on Batch (qryBatchIngredients) is not
getting the records added to it. I need clarification on relationship for
the query. I have 3 tables.

BatchIngredients SemiProdIngredients RawMaterial
BatchINGID(P) ProductIngredientId(P) Code(P)
BatchId Semicode productname
productIngredientId Code vendor#
code Qtyper100lb(fraction)
lot#
semicode

I got rid of the multiple vendors for this applications to hopefully
simplify it for now. Still not clear on the query??? and why subform based
on it is not getting populated.

Thanks again for you help,
barb

Hi,


instead of

----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL
--------------


something like


----------------
'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID) " & " SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & " FROM
Semiprodingredients" &
" WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"


Debug.Print strSQL
Debug.Assert False

'Execute the query
DoCmd.RunSQL strSQL

-------------------


I also add a Debug.Assert False so the code will stop just right after the
strSQLvariable would have print its content in the debug immediate window.


Note that I also assumed the strSQL= ... line is just in one line (the
newsreader may have cut it into multiple lines).




Hoping it may help,
Vanderghast, Access MVP

Thanks for the advice. Not sure where to put the Debug.Print SQL exactly.
I
took out the concatenation operators and added spacing but now getting
syntax
errors. Not sure what or where EXACTLY I need to add spacing. Please
keep
helping.

thanks,
Barb

:

Hi,


add space around the keywords. If you debug.print your strSQL:


Debug.Print strSQL


after you filled it, and before you used it in your DoCmd statement, you
should see something like:

.... AS Expr1FROM Semi....


while you probably intend


... AS Expr1 FROM Semi...

Your statement is read as is you want the alias name EXPR1FROM ...
and
no FROM clause if found.


Same pattern with the SELECT (not critical since preceded by a closing
parenthesis) and WHERE (for which it is critical) keywords. Add at
least a
SPACE before and after the keywords, for safety, add two, so you will
immediately spot them, when you build the statement, through strings
concatenation. The concatenation operator, &, DOES NOT do it for you.



Hoping it may help,
Vanderghast, Access MVP



Posted this again today since didn't here back sorry - A little anxious
to
get this figured out.

Thanks SOOO Much for responding back.

I followed your new directions and did all except 1st of all when I run
the
qrybatchIngredients on its own - I get no records???

also when I attach the VB code the command button I get an error- A
dialog
box pops up that says: Query Input must contain at least one Table or
Query.
then I have to click on okay. and records in BatchIngredients subform
don't
get populate.

Below is my VB code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


Dim strSQL As String

'Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT
Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM
Semiprodingredients" &
"WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));"

'Execute the query
DoCmd.RunSQL strSQL

'Requery the subform to display the new records
Me![BatchIngredients].Requery

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

code=Ingredientscode
Batch=Batches
semiprodingredients=ProductIngredient
semicode=ProductID
not sure what cboProduct is = assumed it is the name of the my combo
box
on
the Main Batches Form.

Also not sure about the spacing at the end of your lines- don't think I
needed the _ after the & signs??????

also not sure what name should go in the last line of code is it the
Table
name BatchIngredient???? or should it be the subform name. I tried
that
and
got the same error.

One other issue I have is that the Ingredients table has two primary
keys -
code and vendor, since one raw material can come from more than one
vendor.
Really just want the Batch ingredients to be populated with the raw
mat.
code
for a given Semiproduct. the lot # or (Batch#) will indicate which
vendor
it
came from.

Hope you can help with getting this subform populated!!!!!!
Supposedly needed this done by today. Oh Well
THANKS SO MUCH AGAIN!!!
Barb

Assumed cbosemicode is the NAME of the combo box for

:

Hi, Babs.

Yes. Quantity is a property of the batch because it is associated
with
the
product. You'll calculate the quantities of the batch ingredients
from
it
and the related fractions stored in ProductIngredients.

As far as inserting records, assign code to a command button on your
main
Batches form to execute an Insert query. Not being a skilled SQL
jockey,
I
prefer to create queries in Design View. Once saved, you can execute
them
with the OpenQuery method, which the Command Button wizard will do for
you.
Alternatively, you can switch to SQL view from query design view, and
cut
and
paste the query's SQL into your command button procedure, and use the
RunSQL
method of the DoCmd object.

Before we get to that, though, it occurred to me that you need the
fraction
field from ProductIngredients to calculate the ingredient quantities,
and
the
Ingredient field from Ingredients to display the name of the
ingredient.
Pick them up with a query.

Define a new query, named, say, qryBatchIngredients, based on the
BatchIngredients, Ingredients, and ProductIngredients tables. Be sure
relationships are established between BatchIngredient.IngredientID and
Ingredients.IngredientID and between Ingredients.IngredientID and
ProductIngredient.IngredientID. Drag all the BatchIngredient fields
to
the
query grid, and also Ingredients.Ingredient (the text field not the
numeric
code) and ProductIngredients.Fraction.

After you've saved the query, load your BatchIngredients subform in
Design
View and change the form's RecordSource from the table
BatchIngredients
to
your new query. Place the controls you need. Remember you don't need
or
want the BatchIngredientID or the BatchID on your subform.

To display the quantity needed for each ingredient, add a textbox to
your
subform and set its Control Source property to:

=[Parent]![Qty]*[Fraction]

Now for inserting the records. Create a new command button. Start it
with
Wizards enabled so that it will create the shell for you. Select any
of
the
options, and then edit it and delete all the action code, but preserve
the
error-handling code. Then add the following:

Dim strSQL As String

' Assign SQL string to the string variable
strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _
"SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1
"
& _
"FROM ProductIngredient " & _
"WHERE
(((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));"

' Execute the query
DoCmd.RunSQL strSQL

' Requery the subform to display the new records
Me![BatchIngredient].Requery

If you study the code, you'll see you pick up the BatchID (the linking
field
for the form/subform) from the main form, and the IngredientID's from
ProductIngredient, using the form's ProductID to select the records.
Your
forms and fields may be named differently so you may need to edit this
statement. Be careful to include a space at the end of each line
except
the
last (within the quotes) or the string won't be valid SQL.

Finally, the last statement requeries the subform so that it can
display
the
new records.

Since the only field you'll need your operators to edit is the
ingredient's
Batch Number, you can set the other controls' Enabled property to No
or
Locked property to Yes.

I hope that solves it, but if you have any other questions, feel free
to
ask
them.

Sprinks


:

Told coworker I would attemp to help her get this figured out.
Please
help

See Below, I did all as FOLLOWS however get stuck on the advice
AFter
I
created the Batch and Batch Ingredients Table. Should the Quantity
to
be
produce be in the BAtch table. Not sure how to DO the REst that is
after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......

Please help - Thanks so much,
Barb



Hi, Babs.

First, I am going to assume that this is an application you intend
to
do
completely in Access once you have your basic percentage formulas
entered,
i.e., there will be no ongoing requirement to import data from
Excel.

If this is the case, I think you will require several tables for
this
job.
All of them are simple in themselves; splitting them into multiple
tables
will make using it so much easier than in Excel alone because you
will
be
harnessing the power of a relational database.

You didn't mention it in this post, but I'm inferring from your
initial
post
that you intend also to keep a record of each batch that would
include
the
quantity of final product produced, the final product batch number,
perhaps
the date and/or operator, and the batch number of each raw material
used. Is
this correct?

If this is so, you have the following relationships to account for
in
your
application:

Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)

The latter means a product may have many ingredients, and an
ingredient
may
be used in many products. The best way to represent this
relationship
is
with two one-to-many relationships. Based on what you've told me, I
suggest
the following tables to capture the basic formulas:

Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
.any other product-specific information

Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
.any other raw material-specific information

ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point

The first two tables store the basic information about all of your
Products
and raw materials, respectively. The last stores your basic formula
to
produce 1 unit of product, and represents the 1-to-many
relationships
to each
of them.

To enter the formulae, I would create a main form based on the
Products
table, and a subform based on ProductIngredients, representing
foreign
keys
with combo boxes to facilitate the data entry. A control in the
subform's
footer would be handy to ensure that the total of the fraction field
=
1.0
(=Sum([Fraction]).

Once the basic products, raw materials, and product ingredients have
been
entered, it will be easy to calculate the required amounts for any
size
batch: [Fraction]*[BatchSize]

To capture the information, I suggest two additional tables:

Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
.any other Batch-specific information

BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text

The way I see this working is you enter a new batch number, select
the
Product ID from a combo box, and enter the Qty to be produced. At
some
event, either by pressing a button or from the combo box'
AfterUpdate
event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from
the
ProductIngredients table for each ingredient in this product.
Probably
the
easiest way to do this is to create the multi-table query in Design
mode and
then call it using the OpenQuery method.

On a form based on Batch with a subform based on BatchIngredients,
the
operator can enter the BatchNumber(s) for each ingredient.

A lot of work to be sure, to set it up. But simple when you're
done.

HTH
Sprinks


:

thanks for the advice on the transpose copy paste but still more
questions.
Not quite sure how to set up the table.
example of record??
finished prod. # finished prod name raw material code raw mat
name
lot #
ABC 1" masking tape 232 rubber
45
2828 toluene
11
376 mineral
spt. 49

As you see the finished prod # and name need to be listed once and
then the
ingredients that go into it will tak up numerous records if I list
them down
instead of across. I would like to generate a form automatically
with the
ingredients so production can enter the lot # they used without
haveing to
put the raw matl. code and name in. Is this a place where I need
a
sub
form(subtable- is there such a thing?. Not sure how to set up the
underlying
table even though as in example above.

Thanks,
Barb

:

Babs,

Before importing the data into Access, do a Copy/Paste Special
(Transpose)
to rearrange the data so that the columns become rows (and vice
versa).

HTH
Sprinks

:

I am putting together table/queries/forms to produce a product
and need to
follow lot #s of raw materials put into them and back calcuate
quantities of
raw materials used based on the final quantity of product
produced.

I have started with one table of the list of raw material
recieved. Example
- raw materials #,name - 123(rubber),367(toluen),8989(mineral
spirits) we'll
say that these three products make tape ABC,1" masking tape
which
is the
final product id and name. Every final product has a list of
raw
materials
and percent of what goes in to make a given final quantity. I
have an excel
spreadsheet that lists each final product#, name and across as
column
headings is ALL the raw materials listed.

example.
final prod.name prod# rubber milk toluene mineral spts
1" masking tape abc .50 0 .34 .22

I feel like the raw material should be listed down instead of
across. In the
raw material table the items are listed down. I would like to
generate a
form for production when they are making the 1" tape(final
prod)
that would
list the raw material that go into it and would give them a
field
to put in
the lot # and the qty that they used next to each raw
material.
I can see
grabbing this from a table created from the excel spreadsheet
example but
find it odd that the ex. rubber is now a field name in that
table.

Please help - on this table layout!!!
Thanks,Barb