In the application that I designed for my company, I have had Suppliers as a table and Parts as a table, each part having one Supplier. This has been used for the longest time to simply reorder the little hardware parts for my company's furniture manufacturing business, and there was never an actual physical inventory stored anywhere for these items. My company now has changed accounting practices and they want to be able to periodically Inventory these parts, and also to store the results of those inventories for possible perusal later. In other words, not just place an count in the Parts table and replace it at the next Inventory. I was thinking about creating a SupplierInventory table that just has a SupplierInventoryID, the Date and the SupplierID, and then creating a PartsInventory table connected by the SupplierInventoryID which just has PartID and PartQty. What I envision is that the user enters the SupplierID in the SupplierInventory table via a combo box in the form header and the Date is defaulted to now (though editable). Once the SupplierID is entered, bang, a list of that suppliers parts appears in the details section with a box to enter the count. The only way I can think to create that list is to insert by code into the PartsInventory table all PartIDs for that SupplierID along with the SupplierInventoryID in the AfterUpdate even for the combo box SupplierID. The PartsInventory subform in the detail section would then not allow additions or deletions, only edits. I wonder if I am over-complicating this situation and if there might be a simpler solution. I admit that although I have, years ago, taken inventory, I have not been tasked with creating the ability to enter that Physical Inventory in a database before. How have you guys out there handled this kind of thing before? I thank you in advance for your time and your replies.
FrmMain has a combobox for SupplierName (think this will be easier for users than ID, or if you want both you can make a 2 column combobox) and Date in Form Header.
When you change the supplier the Details part of the form changes to the new criteria. Showing all the parts, current stock, previous orders and a bit to order new parts?
If this is the case then you are on the right lines, with a table of Suppliers and Parts, but you will probably need another table where all the Orders go, and a relationship between Parts and Orders.
What you can do is have the header part the same, then the List of Parts that belong to that supplier is shown inside a list box on the details section. Then the user would click the part they want to look at, which will then show the details like, current stock, previous orders and a new order section.
Hope I understood you ok, in which case what you said was perfectly fine, but not sure it is so user friendly.
Actually, all I was looking for was a way to populate a list of the items that we get for the chosen Supplier, with Part Number and Description, and just a box to enter the count for each item taken from the Physical Inventory that was just done. The only way I could think to allow for entry of the counts and saving of each particular Physical Inventory was to Insert the appropriate part numbers and the SupplierInventoryID into another table as primary keys, with one additional field for the counts. I don't know if Materials Management software makers usually just create a table when the users signal that it is time to take inventory or do something like I am thinking of doing, which is append the two fields like I have described above. Thanks, though, for your help.
1. put a List box in the form
2. use a continuous form (which displays records one under the other)
Which ever way you choose, just play around with the control source:
Control source can be found by going into design view and opening the properties window.
On control source there is a button at the end of the box that appears when you hover over it or click on control source. This takes you to a query maker. You can do all of the things you wanted in this query maker that you have mentioned, take fields from several tables at once and include count.
Then on the form add an empty box to add the physical inventory. So that for each record there would be a space to record the inventory.
Does this make sense?
Bit early for my brain to function properly.
I just decided to go with my first instinct. When the user selects a supplier, I use ADODB to open the Parts table and the PartInventory table, capturing the SupplierInventoryID from the form where the Supplier is entered. Then I loop through the Parts for that Supplier and add each PartID with the captured SupplierPartID to the PartInventory table. I just had to put programming that deleted currently entered PartInventory entries if no counts had been entered for that list of Supplier's parts yet, or to bar the user from changing the Supplier if counts had been entered. I had thought that the time to populate the list (addnew items to PartInventory) would be overly long, but it turns out that I was much too pessimistic.