Subform Code Question

[KSmith]

I have a mainform (The One) and a subform (The Many) that displays data
correctly.

The problem: (The Many) can have one item or as many as nine or ten items
and I need to be able to subtract an amount the user enters on the mainform
from all of the subform items.

The way I have it coded now it subtracts only the top or first item amount.

How do you set-up a ‘Do While Not End Of File’ section of code in Access 2007?
Thanks in advance

 

[Marshall Barton]

I have a mainform (The One) and a subform (The Many) that displays data
correctly.

The problem: (The Many) can have one item or as many as nine or ten items
and I need to be able to subtract an amount the user enters on the mainform
from all of the subform items.

The way I have it coded now it subtracts only the top or first item amount.

How do you set-up a ‘Do While Not End Of File’ section of code in Access 2007?

With Me.subformcontrol.Form.RecordsetClone
.MoveFirst
Do Until .EOF
!itemamount = !itemamount - someamount
.MoveNext
Loop
End With

BUT, this seems like a very unusual thing to do and possibly
dangerous. Make sure you have both a way to undo this kind
of thing as well as a way to prevent it from being done more
than once.

 

[KSmith]

Thanks for your reply. As of now I am working with sample data. So if I
screw it up no big deal, for now.

I would very much appreciate any tips on a better way to do this.

What the form does. The MANY table has all of the ITEMS needed to make one
of the ONE table items.

So when the user ‘builds’ an item in the ‘ONE’ table, the form needs to
subtract that amount from each of the items it took to build it.

As of now there’s about 40 items in the ‘ONE’ table and about 200 items in
the ‘MANY’ table.

Some items take two of the ‘MANY’ items to complete and some take as many as
nine of the ‘MANY’ items to complete.

I got a combo box that displays the items based on the ‘ONE’ table. The
‘MANY’ table items are displayed in a subform.

As you probably can guess this is the first time I have dealt with subforms.
Again, Many Thanks

 

[Marshall Barton]

Not sure I follow that, especially why you would want to
subtract a fixed amount from something in the many side
form. Seems like if the one side item uses x many side
items of one kind and y items of another kind, what are you
subtracting.

Maybe it's just that I don't understand how the many side
records were created and why you need to subtract something.
Kind of sounds like you are reducing an inventory of items.
If so, I suspect that there is really a many to many
relationship between inventorey items and the list of items
that can be built???

 

[KSmith]

This company builds about 40 different products at this site. I trying to
make an inventory control program to help track raw material in, and finished
products shipped out.

And also track the individual parts through the production process.

I have about 30 raw material part numbers that make about 200 in-process
part numbers that make about 40 finished products.

In addition to tracking raw meterial we need to be able to track the
in-process parts as they progress through the production process, which is 5
steps from raw material to the assembly process.

I want to be able to enter on a form the catalog model number of a product,
and have the form display all pertinent information about that product, last
shipped, inventory amounts at each of the in-process steps for the items it
takes to build the finished product, and the raw material inventory for each
of the items for that product.

So, at the final assembly point I will be taking several pieces and making
one part. For example if a final assembly part has two pieces.
In-Process Part In-Process Inv Final Part Number
4444 878 4422
2222 787

If I assembled 200 pieces of the 4422 Final Assembly Part Number, I need to
subtract 200 pieces from each of the 4444, 2222 in-process inventory levels.

So for the 4444 part the inventory would be 878 - 200 = 678
2222 part the inventroy would be 787 - 200 = 578

And the inventory level for the finished part 4422 would 200 if the
inventory was 0 to start with.

I hope this better explains the process.

And thanks for the help.

--
KSmith

Not sure I follow that, especially why you would want to
subtract a fixed amount from something in the many side
form. Seems like if the one side item uses x many side
items of one kind and y items of another kind, what are you
subtracting.

Maybe it's just that I don't understand how the many side
records were created and why you need to subtract something.
Kind of sounds like you are reducing an inventory of items.
If so, I suspect that there is really a many to many
relationship between inventorey items and the list of items
that can be built???
--
Marsh
MVP [MS Access]

Thanks for your reply. As of now I am working with sample data. So if I
screw it up no big deal, for now.

I would very much appreciate any tips on a better way to do this.

What the form does. The MANY table has all of the ITEMS needed to make one
of the ONE table items.

So when the user ‘builds’ an item in the ‘ONE’ table, the form needs to
subtract that amount from each of the items it took to build it.

As of now there’s about 40 items in the ‘ONE’ table and about 200 items in
the ‘MANY’ table.

Some items take two of the ‘MANY’ items to complete and some take as many as
nine of the ‘MANY’ items to complete.

I got a combo box that displays the items based on the ‘ONE’ table. The
‘MANY’ table items are displayed in a subform.

As you probably can guess this is the first time I have dealt with subforms.

.

 

[KSmith]

I was looking at the code sample you posted yesterday, and I am confused.

How do you set up the first line of the sample code?

What is subformcontrol?

How does the form know that it’s suppose to use a certain table to perform
the calculations on?

I know these questions may be elementary in nature but it’s so frustrating
to know what you want to do and to not be able to tell Access 2007 how to do
it.

Can you recommend any books or websites that would have coding examples for
various problems or situations?

Again, many Thanks.

--
KSmith

Not sure I follow that, especially why you would want to
subtract a fixed amount from something in the many side
form. Seems like if the one side item uses x many side
items of one kind and y items of another kind, what are you
subtracting.

Maybe it's just that I don't understand how the many side
records were created and why you need to subtract something.
Kind of sounds like you are reducing an inventory of items.
If so, I suspect that there is really a many to many
relationship between inventorey items and the list of items
that can be built???
--
Marsh
MVP [MS Access]

Thanks for your reply. As of now I am working with sample data. So if I
screw it up no big deal, for now.

I would very much appreciate any tips on a better way to do this.

What the form does. The MANY table has all of the ITEMS needed to make one
of the ONE table items.

So when the user ‘builds’ an item in the ‘ONE’ table, the form needs to
subtract that amount from each of the items it took to build it.

As of now there’s about 40 items in the ‘ONE’ table and about 200 items in
the ‘MANY’ table.

Some items take two of the ‘MANY’ items to complete and some take as many as
nine of the ‘MANY’ items to complete.

I got a combo box that displays the items based on the ‘ONE’ table. The
‘MANY’ table items are displayed in a subform.

As you probably can guess this is the first time I have dealt with subforms.

.

 

[Marshall Barton]

This company builds about 40 different products at this site. I trying to
make an inventory control program to help track raw material in, and finished
products shipped out.

And also track the individual parts through the production process.

I have about 30 raw material part numbers that make about 200 in-process
part numbers that make about 40 finished products.

In addition to tracking raw meterial we need to be able to track the
in-process parts as they progress through the production process, which is 5
steps from raw material to the assembly process.

I want to be able to enter on a form the catalog model number of a product,
and have the form display all pertinent information about that product, last
shipped, inventory amounts at each of the in-process steps for the items it
takes to build the finished product, and the raw material inventory for each
of the items for that product.

So, at the final assembly point I will be taking several pieces and making
one part. For example if a final assembly part has two pieces.
In-Process Part In-Process Inv Final Part Number
4444 878 4422
2222 787

If I assembled 200 pieces of the 4422 Final Assembly Part Number, I need to
subtract 200 pieces from each of the 4444, 2222 in-process inventory levels.

So for the 4444 part the inventory would be 878 - 200 = 678
2222 part the inventroy would be 787 - 200 = 578

And the inventory level for the finished part 4422 would 200 if the
inventory was 0 to start with.

Yes, that helps clarify the objectives and confirms my
suspicion that this is mostly an inventory calculator.

The next level of critical information is the tables and
their relationships. It's common for novice database
designers to get confused about what information goes into
which table (Google for Rules of Normalization). The
relationships between tables is an extremely important part
of the data design.

I can't tell much of that from what you've posted so far, so
I think it would help if you could post the tables, their
significant fields and how they are related. For example, I
think part of it might look something like:

MainParts table:
FinalAssPartNum Text (primary key)
. . . (other final part fields)

Inventory table:
PartID Text (primary key)
QtyAvail Long
. . . (other inventory fields)

PartComponents table:
FinalAssPartNum Text (foreign key to MainParts)
PartID Text (foreign key to Inventory)
(primary key is the above two fields)
QtyRequired Long

Production table:
RunID AutoNumber (primary key)
RunDate Date
Quantity Long
. . . (other production run info)

 

[Marshall Barton]

I was looking at the code sample you posted yesterday, and I am confused.

How do you set up the first line of the sample code?

Not sure what you mean by "setup"

Just type it into the event procedure that you are using to
start the calculations.

What is subformcontrol?

subformcontrol is the name of the control on the main form
that is used to display the (sub)form object. This name may
or may not be the same as the name of the (sub)form object

How does the form know that it’s suppose to use a certain table to perform
the calculations on?

A form only knows about what's in its record source
table/query. Anything beyond that would have to be dealt
with by VBA code in various event procedures.

I know these questions may be elementary in nature but it’s so frustrating
to know what you want to do and to not be able to tell Access 2007 how to do
it.

Translating an idea into a useful process is where most of
the work hides out ;-)

Can you recommend any books or websites that would have coding examples for
various problems or situations?

There are a lot of them out there, but it's been a very long
time since I went through one to any level of detail and I
don't think that one has been updated for the last couple of
versions of Access. I suggest that you visit your local
library and/or bookstore and browse through several to see
if they cover the kind of info you are looking for in a
style that suits your brain's way of thinking.

 

[KSmith]

Thanks for the info. Below is an approximation of the two main tables
involved.

tblAssemblyArea
AssemblyID CatalogNum In_ProNum ModelNumQty
1 123 144 787
2 123 155 888
3 123 177 879
4 555 531 123
5 555 533 43
6 712 711 152
7 819 801 90

tblFinalAssembly
FinalAssID CatalogNum CataloglNumQty
1 123 220
2 555 100
3 712 78
4 819 100

As you can guess CatalogNum is the field that links these two tables.

Let’s say for example I had to build 100 pieces of the 123 Catalog Model
Number.
Then I need the form to subtract 100 pieces each from the 144, 155, 177
In_ProNum field's in the tblAssemblyArea table.

And I need it to add 100 pieces to the 123 CatalogNum record in
tblFinalAssembly.

The way the form runs now it Adds the correct amount, to the correct record,
in tblFinalAssembly and subtracts the correct amount from only the top record
that is displayed in the subform (tblAssemblyArea).

The subform (tblAssemblyArea) does display the correct information.

Many, Many Thanks.

 

[Marshall Barton]

Thanks for the info. Below is an approximation of the two main tables
involved.

tblAssemblyArea
AssemblyID CatalogNum In_ProNum ModelNumQty
1 123 144 787
2 123 155 888
3 123 177 879
4 555 531 123
5 555 533 43
6 712 711 152
7 819 801 90

tblFinalAssembly
FinalAssID CatalogNum CataloglNumQty
1 123 220
2 555 100
3 712 78
4 819 100

As you can guess CatalogNum is the field that links these two tables.

Let’s say for example I had to build 100 pieces of the 123 Catalog Model
Number.
Then I need the form to subtract 100 pieces each from the 144, 155, 177
In_ProNum field's in the tblAssemblyArea table.

And I need it to add 100 pieces to the 123 CatalogNum record in
tblFinalAssembly.

The way the form runs now it Adds the correct amount, to the correct record,
in tblFinalAssembly and subtracts the correct amount from only the top record
that is displayed in the subform (tblAssemblyArea).

The subform (tblAssemblyArea) does display the correct information.

It looks like once you enter the catalog number and quantity
in the main form, everything else is supposed to be
automatic. If so, I think(?) you can use a main form button
so users can indicate that the catalog number and quantity
are entered and verified, The button's Click event
procedure can do the calculations:

With Me.AssemblyAreaform.Form.RecordsetClone
.MoveFirst
Do Until .EOF
!ModelNumQty = !ModelNumQty - Me.CataloglNumQty
.MoveNext
Loop
End With

But. I still don't see where you take the number of each
part used in a catalog item. Or, if a catalog item needs x
number of a particular part, there are x records for the
part in tblAssemblyArea for the catalog num.

As a background concern, I am worried that managing an
inventory is more complicated than just that. If you
haven't done so yet, start worrying about how you are going
to handle rejected, lost and scrapped inventory items and
what you should do when the year end stock take does not
agree with the database values.

 

[KSmith]

FYI - I made the corrections to my code per your sugestions and it works
almost.
What is going on now.

For example, if the subfrom has two records displayed and you enter 20
pieces to subtract, it will subtract 40 pieces.

If the subform has three records displayed and your enter 20 pieces two
subtract, it will subtract 60 pieces. So, in this example it is subtracting
20 pieces '3' times!

..MoveNext , Loop , and End With are in my code like they are in your sample
code.

The way I made the from and subform is: I made the mainform first, and then
made a second form that I move onto the mainform.

Should I have let the Access Widzards help me build this?

Again many thanks.

 

[Marshall Barton]

Marsh, Thanks a MILLION!

I had to add .Edit before and .Update after the line of code that does the
work.

OMG, I am so sorry that I forgot the .Edit and .Update
lines. My other brain cell must have overloaded and stolen
cycles from my newsgroup dedicated(?) cell.

It is nice that you were able to overcome that and make some
progress.

 

[Marshall Barton]

KSmith wrote:
[snip]

The way I made the from and subform is: I made the mainform first, and then
made a second form that I move onto the mainform.

Should I have let the Access Widzards help me build this?

Only if it's convenient for you to use a wizard to get the
form started. If you do, I'm pretty sure you will still end
up doing all the details in design view. Personally, I
almost never use a wizard to create a form or report.

 

[KSmith]

Marsh, Thanks a MILLION!

I had to add .Edit before and .Update after the line of code that does the
work.

And thank you for expressing concern for the other items you mention. The
forms that ‘moves’ the inventory from one location to another does have a
place where the user enters ‘Bad Parts’ made during their process.

The form has two command buttons, one does the ‘Good Parts’ and ‘Bad Parts’
calculations and displays this on the form so the user has a chance to
re-enter if they have made a mistake. The other command button ‘Confirm’ is
pressed when they are sure they have the correct ‘Part Number’ with the
correct amounts are entered.

The reject amounts are append to another table along with other information
so I will be able to track who did what and when they did it.

A lot of our products are made of wood. So there’s a lot of waste even when
the machines are set correctly and the operators are careful.

I’m sure I haven’t thought of everything that I will need to make this thing
work. And I sure I will be posting some more questions later, especially
when I get to writing all of the reports that they want.

It’s glad to know that there is a resource like this available.

Again many THANKS.