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