Although this is my first post I have been roaming these forums for a while now. I am working on a database which will eventually be converted to PHP and mySQL. It is my first time going through it from start to finish but I have worked with Access before...
Heres a brief bit of what I'm doing.
I have customers who have different types of "systems" who all need "scans"
Every customer has a system type. Customers MAY have multiple scan templates and multiple scans for each template.
In the customer form you can chose customers from a list which populates fields with their info. If you click the add customer button you get linked to a new form with all the appropriate fields to fill out. The clientID is autonum generated and after you fill out all the info you can chose to add a system type and information on the customers system. This brings a new form, HERES WHERE THE PROBLEMS START!!
When you open that form the clientID from the add customer form must be filled into the systemtype form. (since we are still working with the same customer) you can then select different types of systems configurations from drop downs (these all work) I have two problems here:
1) If I carry the clientID over by making the control source = to the first form, then clientID doesn't save in both tables.
2) When the form first opens the systemtypeID (autonum/primary key) doens't generate right away and posts an error saying "hey idiot you need a primary key before you enter information"
Keeping all that in mind, we go back to the customer form and you can then chose to EDIT the systemtype just in case it changes. the problem here is it is the same form as before but in edit mode. well, i still want to carry the clientID over from the client form this time but this time it must populate the system info fields based on a query of what they are.
In other words, the same form has to have fields that change in control source depending on what parent form opens it.
I hope everybody is still with me. If you have any questions please respond and I will answer ASAP
EDIT: oh, and another thing...
If I pass openargs to the systems info form and populate the fields using that + a query, I run into a problem where if no record exsists (lets say we are creating the info for the first time) then the form posts an error rather than allowing me to create a new record...
I'm sure I have more problems I'm forgetting, but lets solve these together first!
It sounds like one of two things. (1) Either you have all your data in one huge table - all the customers with all their templates and all their scans, which means that your data is not normalized at all. This doesn't seem likely, however, because of the nature of your problems. You probably do have normalized data, but (2) you don't mention whether your tables have defined relationships.
You should have the following table structure: a customer table, which has a one-to-many relationship to a template table, which then has a one-to-many relationship to a scan table. The customer table should have a PK (customer code). The template table should have a two-field PK (customer code (FK) and Template code). The scan table should likewise have a multiple-field (2 or 3, depending on whether the template code is repeated) PK.
If you have this structure, you can easily use sub-forms for data entry. Then you can stop calling yourself an idiot. (You aren't one! You just don't necessarily have all the experience you need for the project. Don't worry; you'll get it quickly enough.)