I'm building a CRM/Service database in MS Access with the following structure:
- Accounts Table (AccountID)
- Contacts Table (ContactID and AccountID for 1 to many)
- Service Orders Table (SONumber and AccountID)
- SO Work Lines Table (LineID and SONumber)
Accounts is the "master" table in that everything is keyed off of that. Contacts are linked to accounts and then Service Orders are linked to either a contact or an account. SO Work lines are linked to the Service Order.
So I create or open an account, then on the Account Details form it lists all existing SOs with a New SO button. This all works so far. When I click the New SO button I want it to associate the currently selected account or contact to the new SO and pre-populate the header of the form with the account or contact name and address information in locked fields so it doesn't allow edits in that part. I Then should be able to fill out the additional details of the header like Service Date, Job Name, Site ID, Times in and out etc. Then below add work lines to the SO using a continuous form.
I have created an SO Details form that currently works for viewing existing SOs. It has a query behind it that includes the Account details along with the SO details. It includes a subform to display the SO Line Items. I set it up so when I click the New SO button on the Account Details form it will open this form in data entry mode. If I use this mode I am thinking all the fields will start blank so I probably don't want to do that.
I hope I have given enough information, if not I can clarify.