the data has been changed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

The first part of this works fine:

I have a large table of data to display, so I break it into subform views.
On the Master form M1 I display the name address, phone and stage#. On
subform S1 I display stages1-9 which is simply a lot of dates and a
lastEditedBy# field for each stage. Stages 10-19 is the same as stages1-9
except the numbers change.

I use VBA code on each date field's afterUpdate event to update each stages
lastEditedBy# field. If I change stage4's date, VBA automatically changes
stage4_afterupdate()
lastEditedBy4.value = currentuser()
exit sub

This is were it stopped working:
Since there are so many stages (49 total) I wanted to update the master form
M1 to include lastStage. Every time I edit the stages date, I added to my VBA
stage4_afterupdate()
lastEditedBy4.value = currentuser()
FORMS!M1.controls!lastStage.value = 4 ' *** new line -- error
happens here
exit sub

Now, after adding the new line when I click to another sub form or try to
select another record I receive the error
the data has been changed

Now, this next thing sort of fixes the problem, but I will get strange
effects, such as when I click on the TAB control for another subform, I will
now have to click twice instead of once before the control becomes active,
but I do NOT get the error "the data has been changed"
stage4_afterupdate()
me.requery
forms!m1.requery
lastEditedBy4.value = currentuser()
FORMS!M1.controls!lastStage.value = 4 ' **** error goes away
exit sub


Each form & subform uses a query as the data source. The query does not
duplicate any date except a master key. The master key is used to link the
form & subforms using (parent & child links)

thanks for your help,

phil
 
Hi Phil

If I understand you correctly, all this data is in a single table. This
indicates a serious problem with your database design, but let's look first
at the simple cause of the problem.

You have several forms (some of them subforms) bound to different fields in
the same record.

When you execute the line:
lastEditedBy4.value = currentuser()
you begin an edit on that record, and the record is locked by that subform.

Then, when you edit the next line:
FORMS!M1.controls!lastStage.value = 4
you are telling the parent form (M1) to start editing the same record.

Hence the error message.

The easiest way to work around this is to add the field LastStage to the
recordsource of each of the subforms. Then you can change its value in the
same form where you are currently editing the record:
Me.lastStage.value = 4

As I said though, the real cause of the problem is bad database design. It
is important for efficiency and flexibility to design the database so it is
*normalized*. You can read about database normalization basics here:
http://support.microsoft.com/?id=283878

Your data is breaking the first principle of normalization, because it
contains repeating groups of data within the same record. It causes serious
problems when you want to create queries to answer questions such as:
"Who had a stage completed during January, and which stages were they?"
or "Who has stages which were last edited by Fred?"

Instead of a single table here, you should have three related tables:

Master table:
*MasterID (* = primary key)
Name
Address
Phone
etc (any other data pertaining to this individual)
(note there are no Stage-related fields in here)

Stages:
*StageNumber (byte)
StageName
StageRequirements
etc (any other data pertaining to the stage)

.... and a third table to join the two:

StagesPerformed:
*MasterID (these two fields comprise a
*StageNumber multi-field primary key)
DateStarted
DateCompleted
LastEditedBy
(and any other data pertaining to that particular instance of the stage)

You can either add records to this table en masse (if everyone needs to
complete all 49 stages) or you can add them one at a time as each person
starts a new stage.

Your main form can then be bound to your master table, and your subform (you
need only one new) can be bound to the StagesPerformed table.
 
Back
Top