Error - The data has been changed

[philelpko]

Afternoon all,

Cheers to anyone who can help! So far this forum has been a big lifeline to me!!

I believe this to be the last hurdle before my database is complete!

Problem - I have a main form with 3 subforms. Each subform is hidden until it is made visible through use of a check box (code has been written to only allow one box to be visible at any one time). These had been working perfectly but all of a sudden I have begun to get the error message

"The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes"

This is hard to believe as i am the only person using the database. I have found a few articles relating to this but no-one ever suggests a possible fix for this problem.

The Microsoft site says to fix the problem to add "Me.Parent.Requery" to the AfterUpdate event on the subforms. This does work but now when you try to change subforms using the check boxes, the 1st click on a check box appears to trigger the AfterUpdate but then you have to re-click the same check box to acctually change forms.

i.e. to change to Travel Subform from Cars you have to click the Travel Check Box, wait a second or two, and then click the Travel Check Box again to actually get it to change.

Seems a little pedantic i know but it just makes the form non user friendly.

Open to suggestions!! Cheers.

[MMcCarthy]

In Tools - Options under the Advanced tab.

Is your Default Record Locking set to Edited Record?

Is Open databases using record-level locking ticked?

[philelpko]

Default Record Locking is set to "No Locks"

The Open databases using record-level locking is ticked.

[MMcCarthy]

Default Record Locking is set to "No Locks"

The Open databases using record-level locking is ticked.

Change Default Record Locking set to "Edited Record" and comment out the Me.Parent.Requery code. Does this solve the problem?

Mary

[philelpko]

Alas not,

Commenting out the Me.Parent.Request code allows me to switch between the subforms on a single click but I now get the error message again (Record locking changed to "Edited Record")

The subforms update perfectly well regardless of the error message. Is there anyway to disable certain error messages so the code carries on unhindered?

(cheers for the help)

[MMcCarthy]

Alas not,

Commenting out the Me.Parent.Request code allows me to switch between the subforms on a single click but I now get the error message again (Record locking changed to "Edited Record")

The subforms update perfectly well regardless of the error message. Is there anyway to disable certain error messages so the code carries on unhindered?

(cheers for the help)

When you click the tickboxes are the subforms just switching from invisible to visible?

[philelpko]

Yes, the check boxes only change the visible state from true -> false (or vice versa). On the main form the 3 subforms are on top of one another. I read articles about over laying. Half said dont do it, the other half said its fine as long as you dont need to change the layout later.

[MMcCarthy]

Yes, the check boxes only change the visible state from true -> false (or vice versa). On the main form the 3 subforms are on top of one another. I read articles about over laying. Half said dont do it, the other half said its fine as long as you dont need to change the layout later.

I've used a similar procedure before. The record shouldn't be trying to update just because the subform is going invisible.

Are your tickboxes based on a option group or are they individual?

[philelpko]

The tickboxes are individual

[MMcCarthy]

The tickboxes are individual

Can you post the code for one of the tickboxes with the Me.Parent.Requery included.

[philelpko]

Here is the code I am using to control the check boxes

 
Private Sub CarSelect_Click() 
 
   If CarSelect = True Then ' tickbox3
      SubGeneral.Visible = False ' subform1
      GeneralSelect = False ' tickbox1
      SubTravel.Visible = False ' subform2
      TravelSelect = False ' tickbox2
      SubCar.Visible = True ' subform3
   Else
      SubCar.Visible = False ' subform3
   End If
 
End Sub
 

This code is located in the main form.

I deleted the "Me.Parent.Requery" code. The Me.Parent.Requery code was on the subforms unlike the check box code.

p.s (finishing for the day, so wont reply for an hour or 2)

[philelpko]

I deleted the "Me.Parent.Requery" code. The Me.Parent.Requery code was on the subforms unlike the check box code.

p.s (finishing for the day, so wont reply for an hour or 2)

Forgot to mention, that was the only code on the subforms

[MMcCarthy]

Try this ...

Private Sub CarSelect_Click()
 
   Me.Requery
 
   If CarSelect = True Then ' tickbox3
      SubGeneral.Visible = False ' subform1
      GeneralSelect = False ' tickbox1
      SubTravel.Visible = False ' subform2
      TravelSelect = False ' tickbox2
      SubCar.Visible = True ' subform3
   Else
      SubCar.Visible = False ' subform3
   End If
 
End Sub
 

[philelpko]

No luck, it doesnt get rid of the error message.

I put a few breaks into the code (I have put the Microsoft code back in) to see what On Event procedures were running at what time.

When a subform is modified, and a check box is click on, the AfterUpdate event is triggered (as you would expect) and runs but then the On_click event doesnt trigger. I have tried clicking else where on the main form after modifying the subforms but it doesnt trigger unless another check box or combo box (on the main form) is selected.

Is there anyway to enter the Me.Parent.Requery style requery into the On_click event on the check boxes to requery the subforms without having to rely on the AfterUpdate event or is this not possible?

[MMcCarthy]

Is there anyway to enter the Me.Parent.Requery style requery into the On_click event on the check boxes to requery the subforms without having to rely on the AfterUpdate event or is this not possible?

The first line in the code I sent you...

 
Me.Requery
 

Is the same thing. You don't need Parent because you're on the main form.

[philelpko]

The first line in the code I sent you...

 
Me.Requery
 

Is the same thing. You don't need Parent because you're on the main form.

Rather silly question of me to ask in hind sight!

[MMcCarthy]

Rather silly question of me to ask in hind sight!

Don't worry about it.

More importantly, does it solve your problem?

Mary

[philelpko]

Don't worry about it.

More importantly, does it solve your problem?

Mary

Nope, still get the same data error message even with the Me.requery in the On_click event.

I think that I am going to have to re-do my main form but this time not hide the forms one behind another. What way would you recommend laying out a main form with 3 subforms (only 1 subform is allowed to be viewed at a time).

I think the main prob with this database is that its the 1st one i have ever designed and I keep finding quicker and easier ways to write code that I wrote the week before, so I am thinking of just re-writting the whole thing!!

Cheers for all the help Mary :)

[MMcCarthy]

Nope, still get the same data error message even with the Me.requery in the On_click event.

I think that I am going to have to re-do my main form but this time not hide the forms one behind another. What way would you recommend laying out a main form with 3 subforms (only 1 subform is allowed to be viewed at a time).

I think the main prob with this database is that its the 1st one i have ever designed and I keep finding quicker and easier ways to write code that I wrote the week before, so I am thinking of just re-writting the whole thing!!

Cheers for all the help Mary :)

You could use a multi page object.

Before you scrap the idea completely, change your click boxes to command buttons. This is what I used when I was hiding/unhiding subforms. It may make a difference.

Mary

[philelpko]

You could use a multi page object.

Before you scrap the idea completely, change your click boxes to command buttons. This is what I used when I was hiding/unhiding subforms. It may make a difference.

Mary

Mary you are a god send!!!

I never have tried changing the check boxes to command buttons as i figured it wouldnt make any difference as the both worked on "on click" events.

Works a treat now!! Thanks massively.

[MMcCarthy]

Mary you are a god send!!!

I never have tried changing the check boxes to command buttons as i figured it wouldnt make any difference as the both worked on "on click" events.

Works a treat now!! Thanks massively.

At a guess I would say with the tickboxes Access was attributing values to them whereas it doesn't with command buttons.

Mary

[NeoPa]

Fascinating thread.
Who would have thought Access would behave so unpredictably ;)
Nice work getting it sorted out - particulary Mary.

[SBroekmeulen]

Really Fascinating,
I have a same sort of problem but I do need Checkboxes, since they do Hold Information.

I have a subform with analising results, which is initially filles with default values. The checkbox tells me wether to print the results.

When I Re-analyse, the new records are added to the list, and I need the checkboxes to be copied to the newest results. All goes well so far...
Then I need to remove the older results from the report, and run a query that unchecks the checkboxes on the older records. At the end of this routine I requery, to update the form and I get the same erroromessage as Philelpko...

I tried in-code SQL vs Access-Queries for it was documented somewhere on the internet as a problem between access and sql regarding interpretation of booleans (-1 vs 1), no result.

Any more suggestions without changing the checkboxes???