Subform Code Question

K

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
 
K

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
 
K

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


Marshall Barton said:
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.
.
 
K

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


Marshall Barton said:
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.
.
 
K

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.
 
K

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.
 
K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top