AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
I am trying to create a db for a consignment business. I have a customer table, an inventory table, a location table, and an order table with a junction table linking all records.
The process I am looking to create begins by 1)entering customer data, 2) tieing that customer to a store location, 3) entering inventory data (the item the customer is consigning), and 4) entering order data (creating an invoice).
I am looking to create a sequence of forms to capture the data for the entire process of a single customer so that it flows consistently and so that the data remains related.
Is there anyway to link forms so that one data field can be carried into a new form, thus linking the data? I'll expound: I enter in Customer info with an Autonumber CustomerID. Then I want to macro over to the Inventory Form and carry over the CustomerID so that I can link the InventoryID to the respective customer. Lastly, I need to carry the InventoryID and/or CustomerID over to the Order Form to capture pricing, promotions, and value-added services.
My reasoning for this is so that as the database grows, I don't have to do a customer lookup on the Inventory Form for hundreds or thousands of customers.
I hope I was able to portray the situation clearly. Let me know if you require any additional information to answer. Any thoughts or suggestions would be greatly appreciated.
Kind regards,
Stephen
Answer First, I you mention macros, I recommend that you use macros sparingly. They are very limiting and what you know about macros can easily translate to VBA. For example. If you create a macro to open a form, the parameters of the OpenForm Action are the same as for the OpenForm Method in VBA. But you have much more flexibility in filling in those parameters.
Second, I think your workflow could use some revision. Here's how I would do it:
1) You actually have 2 types of customers. Those who buy from you and those who supply you. You should be keeping records of both. Sometimes a buyer may also be a supplier. So I would have a customer's table with a Supplier flag to indicate that they have supplied an item for consignment.
2) Create a Customers mainform with an Inventory subform. This way you can see all the items a specific customer (supplier) has consigned on a single form. This form would be filtered so only thjose with the supplier flag set would be shown.
3) New items would be entered on the Inventory subform. So first you find (or enter) the supplier, then enter the item in the subform. The CustomerID will automatically be entered as a foreign key with the Inventory record.
4) As for Orders, you should have InventoryID as a foreign key in your Order detail records. So when you slect the Item (or items) being purchased, you use that to link back to your Inventory table. You link to the suppliers thru the Inventory table, not directly through orders.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
