Synchronise sub forms via buttons from main form

[PhilBowen]

I am using Access 2003.

I want to synchronise sub forms via buttons shown on the main form. The main form provides information for each application that is received. The sub forms accessed via the buttons provides related information to each of the main forms.

Everything works find except: The sub forms do not "link"/ synchronise with the main form. I type in information in to the sub form. I exit. Go back again and it has disappeared. It has been saved in a separate "sequence".

I have spent hours and hours on this. I have tried via one to one, one to many, referential integrity, properties, filters, etc. etc. Can you please give me an "idiots guide" as to what I need to do.

[MMcCarthy]

The subform source should contain a unique autonumber ID field key and a foreign key reference to the unique ID field on the main form.

The form/subform link should be based on the unique Id field on the main form(parent) and the foreign key reference to it on the subform(child).

Does this help?

[comteck]

Ensure there is a one-many relationship set up between the underlying tables (referential integrity enforced). On the sub-table, the foreign key should be set up as "Number", as opposed to "Autonumber ". The PK for the main table is Autonumber.

comteck

[PhilBowen]

Thanks for the responses. I do have some knowledge of Access. However, in this case, can you give me an "idiots" guide as to what I need to do.

Thanks

Phil

[PhilBowen]

I have set up one to many relationships. The main form is autonumbered. No probs so far. I have then gone to the sub forms (accessed via the buttons) and tried to set up a foreign key. I am advised (understandably ) that I can't have a "null value". Each field in the sub forms have been set up so that they do not need to be filled/ completed. Help...........

[MMcCarthy]

Is your subform built on a table or a query?

[PhilBowen]

There are three sub forms accessed by buttons. Each is built on a separate query

Regards

Phil

[MMcCarthy]

Each of the queries for the subforms must contain a reference to the Primary Key of the record source of the Main Form.

For example;

Main Form with a record source of tblCustomers with a primary key called CustID

Subform 1 with a record source of a query showing Invoices. The fields would include a Foreign key reference from tblInvoices to CustID.

Subform 2 with a record source of a query showing Orders. The fields would include a Foreign key reference from tbl Orders to CustID.

And so on ....

If the tables your queries are build on don't have a foreign key relationship to the record source of your main form you can't have them as subforms.

[PhilBowen]

Thanks for the response. The main form and subforms have reference to the primary key which is the auto number. For example one sub form has the following fields:
Button accesses:
(Auto number)
Report 1
Date report returned
Report 2
Date report 2 returned
Report 3
Date report 3 returned
In the above I therefore need to provide a foreign key. Help

Regards Phil

[MMcCarthy]

The Primary Key of a Field is the AutoNumber. For example CustID in tblCustomers.

In tblOrders the Primary Key would be an AutoNumber OrderID. However, this table would also contain a Field called CustID of type Number. In the Relationships window create a reference between this field and the Primary Key field of tblCustomers. This is a one to many relationship, as in one customer can place many orders.

Because tblOrders now contains a field called CustID which references the Primary Key of tblCustomers, when you create a subform based on tblOrders on a Form based on tblCustomers, any record entered into the subform would automatically populate the foreign key CustID on the subform with the Primary Key CustID value from the main form.

[PhilBowen]

Thanks. So I need to "disconnect " the autonumber/primary key on the subform? In the example re reports sub form I gave above what becomes the foreign key.
Regards

Phil

[comteck]

The best way to do it is to set the Primary Key for your tblCustomers as Autonumber (i.e CustID = Autonumber). But in your tblOrders, set both OrderID and CustID as Primary Keys, and make both their data types Number (not Autonumber).

You then set up a one to many relationship between tblCustomers and tblOrders using CustID.

The only Autonumber should be the one on your main form.

comteck

[MMcCarthy]

Phil

You haven't said how you got the AutoNumber field in your query for your subform.
Where did it come from?
How is your query made up?
From what tables?
What relationships?

Thanks. So I need to "disconnect " the autonumber/primary key on the subform? In the example re reports sub form I gave above what becomes the foreign key.
Regards

Phil

[PhilBowen]

The autonumber is on the main form. I have also created autonumber ID primary key on the sub forms accessed via the buttons. (The ID primary key on the sub forms were set up during the wizard process).

In response to the advice above I maintained the ID primary key on the main form and then created "number" (not auto number) on the sub forms. I received a pop up menu advising that "canot accept null value".

I left the primary key off the sub form. However, same problem. I access the sub forms via the buttons on the main form. On the relationships I created one to many. The data is inputted. I exit. When I go back the information has not been linked with the main form.

I have created the data base to date with no probs. Just this one problem. It is driving me absolutely crazy.......... ..

Regards

Phil

[PhilBowen]

I also continue to see "filtered" in the sub forms. If I remove the filter the data I put in for another entry reappears.