SubForm does not synchronise with MainForm

[hummingbird]

My mainform is based on a table and serves to update the table fields. The subform is based on a query that has calculated fields in it. The subform serves to provide read only information and is set as enable - 'no', locked - 'yes'

The master link field is configured as a combo control (to prevent data modification) and is sorted ascending with an undisplayed primary key autonumber field. The child link field is a text control.

Problem I have is when I tab (the first tab is the master link field) through the mainform, one record to the next, the subform synchronises with mainform, displaying related records. However when I select random fields from the combo dropdown and tab on, the subform remains frozen, and when tabbing into the next record, th subform vanishes. I also note that the master link field in the underlying table is modified to some other value.

Any ideas why this is happening? Thanks.

 

[jsv2002]

In the afterupdate try inserting Me.Requery as for the underlying records being altered it sounds to me like incorrect structure or use of the combo box to display data do you mean you are using the tables primary key in the combo box and selecting a record in current form view.

Seeing an example would help greatly

regards John

 

[hummingbird]

John the Me.Requery in afterupdate did not help.

To simplfy the matter, I got rid of the combo box with the ordinary bound control for the master control link. The field for the master is from a parameter query (to allow selecting specific records) which in turn is an indexed (no duplicates) field in the underlying table.

Great so far. The form/subform opens and parameter query enables displays of appropriate record info in both main/sub sections. Data is then entered in the main form (subform is read only), but the problem is thatafter the last field in mainform is tabbed, it cycles back to the master link field - AND THE ENTERED DATA STILL REMAIN AS THEY WERE. I would have expedted the fields to clear and the parameter query box to reappear. Now if I type over the master field, the subform responds with appropriate new data, but the mainform continues to show the data entered in the previous cycle. Now when I try to close the form/subform, a popup box appears "The change you requested to the table were not successful because they would create duplicate values in the index, primary key or relationships...This record cannot be saved etc"

So clearly whats happening is that the data in the first cycle is not being updated into the table, and the pointer is at the same record in the mainform where when new data is eneterd into the (indexed) master field on the next cycle, its illegal, and upon closing the form the warning message appears.

So the question why is the data not being saved, why does the parameter query not reappear in the next cycle ... and what am I doing wrong?? Most thankful for any assistance.

 

[jsv2002]

Any chance you could post/attach a sample of your DB?

 

[hummingbird]

John, I used access 2007 and this project is 12M in size ... not sure how I could post that, unless I could zip and email it ?

 

[jsv2002]

You could zip and attach here but tbh I only have access 2007 installed on my "test bed" machine which is at my partners so would be a while before I can look at generally use 2003 as most people I deal with are still on that version but someone else here may be able to help sooner...

regards John