Subform table relationship

[Geoffk78]

Hi All,



I have 2 problems that I have been trying to solve for too long now, I have searched this and other forums for an answer but I can not find a solution. I believe that the 2 problems related.

I have a DB based on 3 main tables,

Table Name Primary Key Foreign Key
TblCustomers CustomerID
TblDataInput DataInputID Customer
TblDataInputSubForm DataInputSubFormID DataInputID

All primary keys are autonumber fields and all foreign keys are number fields

I have created a main form "FrmAuditInput" which is has a sub form "SFrmAuditInputSubForm", form "FrmAuditInput" is populated with a combo box "Customer" (foreign key) this works fine.

Problem 1. Is that I cannot get the sub-form to load the data into the table.

Problem 2. I cannot get the sub-form table to relate to the main form table.

I would appreciate any help.

GeoffK78
AuditDB.zip

[June7]

That all sounds good.

Main form is bound to TblDataInput?

Subform is bound to TblDataInputSubForm?

Controls are bound to fields of table?

Subform container control link properties are set?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Geoffk78]

June7,

Thanks for the reply I have attached file.

GeoffK78AuditDB.zip

[June7]

The DataEntry property of both forms is set to Yes. This means forms will not display existing records when form opens, only New Record row. Is this what you want?

Why is Customer field in tblDataInput and tblDataInputSubform?

Don't include tblDataInputSubform in the main form RecordSource. Customer combobox is bound to wrong field. Should be Customer from tblDataInput.

Tables not included for the comboboxes.

[Geoffk78]

June7,

Thanks for the input unfortunately the DB the I attached was an old one that I had been playing with, the correct one is attached, I have made adjustments as you suggested, still cannot get it to work.

GeoffK78AuditDB.zip

[June7]

There is no relationship between TblCustomers and TblDataInputSubForm, in spite of the link set in Relationships. Remove that link from Relationships.

Change the RecordSource for FrmDataInput to RIGHT JOIN:
SELECT TblCustomers.*, TblDataInput.* FROM TblCustomers RIGHT JOIN TblDataInput ON TblCustomers.CustomerID = TblDataInput.Customer;

Set Master/Child links on DataInputID fields.

[Geoffk78]

June7,

How should I relate the 2 tables, I have tried every combination that I could think of with no success.

Could please provide some advice on which fields to link and how, my experience with access is limited, I previously compiled a DB but it was not normalized (everything was in the one table) this time I would like to do it properly, your advise has been a great help so far, I have also been going through some reference books but I seem to be going around in circles.

GeoffK78

[June7]

Explain what this data is. What are the entities and how are they related. CustomerID should not be a foreign key in both TblDataInput and TblDataInputSubForm.