Access - How to add records using subforms

By timothy_kramer ·
Here's the quick of it:

1. I have a tblPOHeader and tblPODetail
2. I want the user to enter in purchase orders using a form that contains a fsubPOHeader in the Header of the form and fsubPODetail in the Detail section of the form
3. When entering data into the Header, certain fields should be populated into the Detail (Order Date, Request Ship Date, Promised Ship Date, Load Number, etc.)
4. When adding a new record in the header, the detail should be blank until data is entered.

I know this is kind of quick and just "thrown out there" like a grenade, but I was hoping to get an answer. Let me know if more information is needed. Thanks!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

logical structure of data

by john.a.wills In reply to Access - How to add recor ...

If all the things to be loaded into the detail rwo and form were keys I would say use the child/parent relationship in the subform's properties, but as you seem to have some things that are not keys I suspect that your problem may be ill defined.

Collapse -

Table relationships

by timothy_kramer In reply to logical structure of data

My tblPOHeader and tblPODetail tables both have a key of BU, DocNumber, DocType, and LoadNumber. When the user enters info for a new PO, the BU and DocType are defaulted in. The DocNumber and LoadNumber are both generated numbers. What I want is for this information to also populate into the detail section and update both the tblPOHeader and tblPODetail. The PODetail on the form then has fields to enter for PartNumber and QTY, which pull in information from the tblItemMaster table such as description, UOM, price, vendor, etc.

My disconnect that I am getting is when entering data into the header section, it is not populating my detail section, which means it is also not inserting the records into the tblPODetail, but only the tblPOHeader. Perhaps my structure is not correct, but it seemed logical to me to have a header and detail for PO information.

Collapse -

child/parent relationship

by john.a.wills In reply to Table relationships

Have you tried putting the child/parent relationship in the properties box of the subform?

Collapse -

parent/child relationship

by timothy_kramer In reply to child/parent relationship

Yes, the properties of the fsubPODetail has the links to the frmPOHeader using the DocNumber, DocType, and BU fields. I can scroll through the header section but when I go to each record, the detail does not change. For example, I have DocNumber 1001, 1002, 1003 in both the Header and Detail tables. I can scroll through the form for these orders but have to go seperately on both the Header form and the detail form. I am not sure what I am missing in the link between the two.

I don't know if this matters, but I am using text boxes on this form as well to use as search strings for the user. So they can search for records by entering in 1001 in the DocNumber text box. My "Refresh" command button has code that searches for the DocNumber and returns those records (or at least is supposed to return those records).

Collapse -


by wynjoe In reply to parent/child relationship

If your table is Headerform and the subform is detail, just go to the properties of the subform then add the "tablename.primary key" source. For example. If the table name is Header and the same detail between the 2 forms is the DocNumber, then you should put " Header.Docnumber" on the subform>properties>Record source. There should be a relationship between the 2 same record.

Related Discussions

Related Forums