Subform Code Question
in [Forms]
|
Prev: MoveNext
Next: Pass a String Var to a Function
From: KSmith on 1 Jun 2010 11:30 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 -- KSmith
From: Marshall Barton on 1 Jun 2010 14:22 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. -- Marsh MVP [MS Access]
From: KSmith on 1 Jun 2010 16:29 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 --- KSmith "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. > > -- > Marsh > MVP [MS Access] > . >
From: Marshall Barton on 1 Jun 2010 22:20 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: KSmith on 2 Jun 2010 08:46
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" 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. > . > |