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.

[PhilBowen]

I forgot to respond to a couple of your questions. The queries are made up from individual tables e.g. reports, photographs, notes. These are then linked together as one to many and connected back to the main table which is the "main form"

[MMcCarthy]

By what field are they connected to the Main Form?

I forgot to respond to a couple of your questions. The queries are made up from individual tables e.g. reports, photographs, notes. These are then linked together as one to many and connected back to the main table which is the "main form"

[PhilBowen]

Autonumber in the original and then number following advice.

[MMcCarthy]

Phil,

I don't understand what you mean. But if at one table in each of your subform source queries doesn't have a field with a direct link (reference not same) to the Primary Key on your main form you cannot use them as a subform.

Autonumber in the original and then number following advice.

[PhilBowen]

As advised by comtech (above) I have set the Primary Key for the main form as Autonumber (i.e CustID = Autonumber). I have then removed the autonumber form the sub forms and the replaced it with "applications" both on the main form and on ths sub form.

I have then used the autonumber on the main form as the primary key and the "application" on the sub form for the primary key

Regards

Phil

[PhilBowen]

Using the autonumber on the main form as primary key and then application on the sub form as the primary key; one to many. I click on the button input the photograph. I then go to the next main form and enter the sub form. The photo is as laoded on the sub form via the first main form. (The sub form is not sycnchronised with the main form.)

Regards

Phil

[MMcCarthy]

Did you use the wizard to attach the subform to the main form.

That is, did you predesign the subform and then drag the subform tool on to the Main Form and use the Wizard in Access to attach it. Even if you did this previously I would suggest redoing it. If the Parent Child relationship is not clear then the Wizard won't be able to attach the subform.

Using the autonumber on the main form as primary key and then application on the sub form as the primary key; one to many. I click on the button input the photograph. I then go to the next main form and enter the sub form. The photo is as laoded on the sub form via the first main form. (The sub form is not sycnchronised with the main form.)

Regards

Phil

[PhilBowen]

Thanks. Yep! Tried that. I have gone back to basics since I was in touch last and re done the forms, connected via on to many, Auto number on the main form and connected to sub form by name "application" which is common to both main and sub form. Same problem.

If I input data into the sub form without connecting to the main form then it inputs data and photos sequencially. No probs. It is onlt when I try to link/ synchronise the sub form to the main form that this problem reoccurs. I have visited Northwind data base. No luck. I have visited the Microsoft and other web sites re this synchronisation/ linking. I have followed instructions exactly/ to the letter. I am sure that this is fundemental problem and that I am missing something simple here.

It is not for the want of effort or trying.

Regards

Phil

[MMcCarthy]

Phil

I get the impression from your last email that your relationship is backwards

In this instance of a one to many relationship the records on the side of one have to be the recordsource of the main form and the records on the many side have to be the recordsource of your subform.

In this case the unique ID of your subform Application would not be the represented in the recordsource of your main form. The case should be that the unique ID on your main form the Autonumber ID would be represented in the record source of your subform as Number ID (not an autonumber type) this is the foreign key reference in your subform to the unique Primary Key ID of your main form. You cannot do it the other way around.

Thanks. Yep! Tried that. I have gone back to basics since I was in touch last and re done the forms, connected via on to many, Auto number on the main form and connected to sub form by name "application" which is common to both main and sub form. Same problem.

If I input data into the sub form without connecting to the main form then it inputs data and photos sequencially. No probs. It is onlt when I try to link/ synchronise the sub form to the main form that this problem reoccurs. I have visited Northwind data base. No luck. I have visited the Microsoft and other web sites re this synchronisation/ linking. I have followed instructions exactly/ to the letter. I am sure that this is fundemental problem and that I am missing something simple here.

It is not for the want of effort or trying.

Regards

Phil

[PhilBowen]

Tried that as well. Still the same problem.

[MMcCarthy]

Phil

Check your PM's (Personal Messages) - see menu on top right of page.

Tried that as well. Still the same problem.