Subform Code Question
in [Forms]
|
Prev: MoveNext
Next: Pass a String Var to a Function
From: KSmith on 2 Jun 2010 10:00 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" wrote: > 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] > > > KSmith wrote: > >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" wrote: > >> KSmith wrote: > >> >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. > . >
From: Marshall Barton on 2 Jun 2010 12:22 KSmith wrote: >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) -- Marsh MVP [MS Access]
From: Marshall Barton on 2 Jun 2010 12:36 KSmith wrote: >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. -- Marsh MVP [MS Access]
From: KSmith on 2 Jun 2010 15:16 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. -- KSmith "Marshall Barton" wrote: > KSmith wrote: > >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) > > -- > Marsh > MVP [MS Access] > . >
From: KSmith on 2 Jun 2010 17:25
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. -- KSmith "KSmith" wrote: > 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. > -- > KSmith > > > "Marshall Barton" wrote: > > > KSmith wrote: > > >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) > > > > -- > > Marsh > > MVP [MS Access] > > . > > |