Allow More Than One Form Per ID

[ProgEvalJAA]

I need to change the design of a form from allowing only one from per customer id to multiple forms per customer id.

My database specifics are as follows:

1. The data entry form has two components, a header and a subform linked to the main form based on customer id number.

2. All the fields are in the database's main table.

3. The header portion of the form contains an unbound combo box. Customer id numbers appear from a drop down list in this combo box, although not all id numbers appear in the list. Only those id numbers with a date in a field labeled FormDate (another field in the main table)appear in the drop down list based on a query titled CustomersWithFormDate. The query serves as the row source for the combo box.

4. Therefore, by design, the form is set to allow only one per customer id number and only if a date is keyed for the FormDate field on the main table.

At this point, I think I need to:

1. Move the relevant fields to a new table;

2. Set the primary key field of my new table to be an autonumber field and link the new table to the main table based on customer id number. Therefore, each customer could have more than one line in the new table; and

3. Reset the form's fields to pull from the new table.

 

[misscrf]

I would start someplace like this:

http://www.utteraccess.com/wiki/index.php/Normalization

It looks like a lot, but give youself an hour to save yourself months cleaning up design issues later.

Naming is very important.
tables should have a prefix like tbl
queries qry
forms frm
reports rpt

tbc textbox controls, cbo for combboxes which are NOT fields, which would be PK for primary key, FK for foreign keys, int for integers, txt for text, mem for memo fields, etc.

This only becomes more and more useful as you get into calling objects in vb.

Do yourself a favor and don't use spaces, instead use ProperCaps.

I always start an app by listing out fields and table names in Excel, and then I build the tables and then the forms, then reports.

Go through the rules of the fields in the excel sheet by explaining them in outline format (word is fine) especially if you are building specs for end users. Make it their terms and not technical. What are the rules?

Customers - for each customer we will capture the following info... (don't mention PK informaiton).

Name - each customer can have 1 salutation, 1 first name, etc.

Orders - each customer can have multiple orders. Each order can have ....

Once you do that, you can identify the level of normalization you require to make a solid app:

http://www.utteraccess.com/forum/Treatise-Normalization-t197282.html

Finally this may help too:

http://www.utteraccess.com/wiki/index.php/Normalizing_A_Database

Some stuff on the naming, but further down, how to decide what to break into another table.

Take the time to clean up the tables, names, etc now and you will be happy later!

HTH.

misscrf

It is never too late to become what you could have been ~ George Eliot