I need help setting up a form and your suggestion on how to go about this. I have a basic understanding of access and normalization. I am using access 07. Ok now to the question. I am trying to design a database to track gas cylinders that we loan to our customers. I currently have it planned with 4 tables.

Table 1 = Cylinders (Basic info about the cylinders to include cylinder number which is like a serial number for our cylinders)

Table 2 = Company (Company name, company ID, address, phone, fax, and email)

Table 3 = Customer (Cust name, company ID, phone 1, phone 2, and email)

Table 4 = History (Has relationship to cylinder number and customer name. Also has date and a status in or out)

Now for the real question I would like to design a form that I can enter Customer name, date, and status in or out one time. Then I would like to be able to anywhere from 1 to fifty cylinder numbers. Then I would like it to log this info into the history table.

---

Answer from Richard Rost:

Here's what I would do… make your main form like you have it, and DON'T bind the fields for Effective Date, In/Out, or Customer to any fields.

Now, for the history subform, include those fields and set their DEFAULT VALUES to be equal to the fields on the main form:

=Forms!MainFormName!EffectiveDate
=Forms!MainFormName!InOut
etc.

Now, you only have to set those top fields once, and then as you enter in the serial numbers for the cylinders in the subform, the default values for those extra fields will be set automatically.

If you don't want to SEE those fields in your subform, that's OK… you can just set their visible property to False, but they'll need to be on the subform.

For more help with the Forms!FormName!Fieldname naming conventions, see this tutorial:

If you want to refer to a value on a Form, you can use the notation:

Forms!FormName!FieldName

You can use this notation inside of queries, other forms, or even reports. I have a free tutorial that explains how to use this:

http://599cd.com/tips/access/forms-formname-field-notation?key=BlFAlEx

Note that if you're refering to a value on a SUBFORM, the correct notation is:

Forms!FormName!SubForm.Form!FieldName

Crazy, I know. Enjoy! Hope this helps.