Is Dirty OR Is Not Dirty?

[True.Kilted.Scot]

Hi all.

I have a form, which is linked to a sub-form.

In the BeforeUpdate event of the form, I want to check the validity of
the values within several controls. So, my thought was to code it as
follows:

If Me.Dirty Then
.....
.....
End If

This way, only if the values on the form have changed, will I check the
values. However, this is causing a problem.

When the form is first displayed, all the fields are blank. I then
select a name from a drop-down list, and certain fields are populated.
However, if I move to the next record, I wouldn't expect any of the
validation code to be executed...but it is.

I guess that since the new values displayed are not the same as the old
values, the "Dirty" property is set to TRUE for the form and so the
BeforeUpdate code is triggered.

My question: How can prevent this happening? I can place an "If
Me.NewRecord = True Then", so that the code is only triggered if a new
record is created. However, the user can also modify an existing
record, and so far, I cannot get the Me.Dirty to work, or figure out an
alternative.

Any and all suggestions are, as always, appreciated.

Rgds

Duncs

 

[Baz]

Hi all.

I have a form, which is linked to a sub-form.

In the BeforeUpdate event of the form, I want to check the validity of
the values within several controls. So, my thought was to code it as
follows:

If Me.Dirty Then
.....
.....
End If

This way, only if the values on the form have changed, will I check the
values. However, this is causing a problem.

When the form is first displayed, all the fields are blank. I then
select a name from a drop-down list, and certain fields are populated.
However, if I move to the next record, I wouldn't expect any of the
validation code to be executed...but it is.

I guess that since the new values displayed are not the same as the old
values, the "Dirty" property is set to TRUE for the form and so the
BeforeUpdate code is triggered.

My question: How can prevent this happening? I can place an "If
Me.NewRecord = True Then", so that the code is only triggered if a new
record is created. However, the user can also modify an existing
record, and so far, I cannot get the Me.Dirty to work, or figure out an
alternative.

Any and all suggestions are, as always, appreciated.

Rgds

Duncs

You seem to be labouring under some kind of fundamental misunderstanding.
If your form is displaying a "blank" (i.e. new) record, and then you put
values in some of the fields (by typing them in, or doing it in code, it
doesn't matter), you are dirtying the new record. This is by definition,
it's not something you can avoid. Further, once a record is dirty, Access
will automatically attempt to save it when you move to a new record, and the
BeforeUpdate event ALWAYS fires before Access saves a record.

You should be able to see from the above that there is absolutely no point
in testing the form's Dirty property in the BeforeUpdate event procedure.
If the BeforeUpdate event has fired, then the form MUST be Dirty.

It might help if you were to explain what you are trying to achieve in
functional terms. What fields are you "populating" and why? And why don't
you want the record to be validated after you have populated the fields?

 

[True.Kilted.Scot]

Baz,

Thanks for your reply.

The form I have is for a TimeSheet / Re-Charging databse. Employees
first enter the date on which they worked on a project, the project
name and finally the start and end times. So, for example, the
following could be entered:

Joe Bloggs Project X 09:15 10:50

When the database is loaded, the user select their name from a
drop-down list and the form is populated with the last record that they
entered. At this point, the value of Me.Dirty is FALSE, since my
breakpoint does not tigger. However, when I move to the next record in
the database for the displayed user, Me.Dirty = TRUE.

From what you have said, I can only assume that this is since the

values that are now displayed are different to those previously
displayed, and so, the form is "Dirty". Am I correct?

What I am trying to achieve, is to validate the new entries that a user
has made--either in a brand new record, or in a modified current
record--and present the user with a message advising them: "Some
fields have invalid entries. Please examine those field marked in Red"
At this point, I would then alter the colour of the invalid control(s)
label to red, to show the user what requires changing.

Is this possible? Or, am I better to validate each field after the
value has been entered?

If the latter, would this be via the BeforeUpdate event, or the OnExit
event?

Many thanks

Duncs

 

[True.Kilted.Scot]

Baz,

As a followup to my previous reply, I am now confused.

As I said before, the form initially appears blank and the user will
select their name form the drop-down list.

If I move to th enext record for the selected user, the code in the
"BeforeUpdate" event after the "If Me.Dirty Then" is triggered. This I
thought was due to the fact that the second record is different to the
first, and so the form is now "Dirty".

However, if I run the code and then move to the next record belonging
to the selected user, the code in the "BeforeUpdate" event is NOT
triggered. I can move from record to record, and the code following
the IF is not triggered.

Am I misunderstanding the meaning of the "Dirty" property?

Duncs

 

[Baz]

Baz,

Thanks for your reply.

The form I have is for a TimeSheet / Re-Charging databse. Employees
first enter the date on which they worked on a project, the project
name and finally the start and end times. So, for example, the
following could be entered:

Joe Bloggs Project X 09:15 10:50

When the database is loaded, the user select their name from a
drop-down list and the form is populated with the last record that they
entered. At this point, the value of Me.Dirty is FALSE, since my
breakpoint does not tigger. However, when I move to the next record in
the database for the displayed user, Me.Dirty = TRUE.

values that are now displayed are different to those previously
displayed, and so, the form is "Dirty". Am I correct?

What I am trying to achieve, is to validate the new entries that a user
has made--either in a brand new record, or in a modified current
record--and present the user with a message advising them: "Some
fields have invalid entries. Please examine those field marked in Red"
At this point, I would then alter the colour of the invalid control(s)
label to red, to show the user what requires changing.

Is this possible? Or, am I better to validate each field after the
value has been entered?

If the latter, would this be via the BeforeUpdate event, or the OnExit
event?

Many thanks

Duncs

How are you displaying the last record that they entered? Have you got some
code in the Combo Box's AfterUpdate event or some such? If so, could you
post it please. The correct way to do this (or one of several correct ways)
would be to open the form with it's DataEntry property set to True, and
then, when the name is selected, to set the form's Filter and FilterOn
properties appropriately.

If the breakpoint you mention is in the BeforeUpdate event, then you will
not see it when the record is dirtied. The BeforeUpdate event fires when
you attempt to save a record (e.g. by moving to a new record), not when the
record is dirtied.

What you propose is perfectly feasible using the form's BeforeUpdate event,
but it does seem to me that in some way you are inadvertantly creating a new
record instead of displaying an existing one, or inadvertanly modifying the
existing record.

 

[Baz]

Baz,

As a followup to my previous reply, I am now confused.

As I said before, the form initially appears blank and the user will
select their name form the drop-down list.

If I move to th enext record for the selected user, the code in the
"BeforeUpdate" event after the "If Me.Dirty Then" is triggered. This I
thought was due to the fact that the second record is different to the
first, and so the form is now "Dirty".

However, if I run the code and then move to the next record belonging
to the selected user, the code in the "BeforeUpdate" event is NOT
triggered. I can move from record to record, and the code following
the IF is not triggered.

Am I misunderstanding the meaning of the "Dirty" property?

Duncs

Hi Duncs,

I'm not sure. If a form is Dirty, it simply means that the current record
has been modified but not saved. No comparison is necessary or is done with
other records.

You are doing something wrong when the name is selected from the combo box.
As I said in my other post, if there is code that runs when that happens,
please post it.

Also, it occurs to me: is the combo box containing the names bound to the
record source? If so, then simply choosing a name from the list will be
sufficient to dirty the record. The combo box needs to be unbound, with
code attached to it that filters the form by the selected user.

 

[True.Kilted.Scot]

Baz,

Here is the code in my Combo Box's AfterUpdate:

Private Sub txtEmployeeID_AfterUpdate()

'Setup for errortrapping
On Error GoTo Err_txtEmployeeID_AfterUpdate

'Requery the form to display the most recent data
Me.Requery

'Setup the link field between the main form and the subform
Me.txtTSEmployeeID = Me.txtEmployeeID.Column(1)

'Requery the subform
Me.sfmFullTimeSheet.Form.Requery

'If the record has not been submitted,move the focus to the
dateworked field
If Me.chkSubmitted <> -1 Then
txtDateWorked.SetFocus
End If

Exit_txtEmployeeID_AfterUpdate:

'Exit the AfterUpdate procedure
Exit Sub

Err_txtEmployeeID_AfterUpdate:

'An error has occured, so record it
Call LogError(Err.Number, Err.Description,
"frmUATTimeSheet.txtEmployeeID_AfterUpdate", , False)
Resume Exit_Err_txtEmployeeID_AfterUpdate

End Sub


"If the breakpoint you mention is in the BeforeUpdate event, then you
will
not see it when the record is dirtied. The BeforeUpdate event fires
when
you attempt to save a record (e.g. by moving to a new record), not when
the
record is dirtied. "

I follow what you are saying above, however, in relation to my own
database, I don't understand it!!!

When I display my first record, everything is fine. I then move to the
next record in the sequence, and the breakpoint "If Me.Dirty = TRUE" is
TRUE, adn so the code in the BeforeUpdate is triggered. However, each
subsequent move to th enext or previous record, see's the "If Me.Dirty
= TRUE" condition, evaluating to FALSE.

Duncs

 

[Baz]

Baz,

Here is the code in my Combo Box's AfterUpdate:

Private Sub txtEmployeeID_AfterUpdate()

'Setup for errortrapping
On Error GoTo Err_txtEmployeeID_AfterUpdate

'Requery the form to display the most recent data
Me.Requery

'Setup the link field between the main form and the subform
Me.txtTSEmployeeID = Me.txtEmployeeID.Column(1)

'Requery the subform
Me.sfmFullTimeSheet.Form.Requery

'If the record has not been submitted,move the focus to the
dateworked field
If Me.chkSubmitted <> -1 Then
txtDateWorked.SetFocus
End If

Exit_txtEmployeeID_AfterUpdate:

'Exit the AfterUpdate procedure
Exit Sub

Err_txtEmployeeID_AfterUpdate:

'An error has occured, so record it
Call LogError(Err.Number, Err.Description,
"frmUATTimeSheet.txtEmployeeID_AfterUpdate", , False)
Resume Exit_Err_txtEmployeeID_AfterUpdate

End Sub


"If the breakpoint you mention is in the BeforeUpdate event, then you
will
not see it when the record is dirtied. The BeforeUpdate event fires
when
you attempt to save a record (e.g. by moving to a new record), not when
the
record is dirtied. "

I follow what you are saying above, however, in relation to my own
database, I don't understand it!!!

When I display my first record, everything is fine. I then move to the
next record in the sequence, and the breakpoint "If Me.Dirty = TRUE" is
TRUE, adn so the code in the BeforeUpdate is triggered. However, each
subsequent move to th enext or previous record, see's the "If Me.Dirty
= TRUE" condition, evaluating to FALSE.

Duncs

Hi Duncs,

Hmmm, you really need to provide more information about the structure of the
whole thing. What data does the main form display? What is it's
recordsource? What, if anything, is the control source of txtEmployeeID?
And of txtTSEmployeeID? What does the subform display? What is it's
recordsource?

Cheers

Baz

 

[BruceM]

I just want to add that a control's Before Update event will run only if you
change the value in the control, so it is not an effective way of checking
to be sure the field was filled in. You can use a control's Exit event to
set the focus to another control, which would eventually force that
control's Exit event to run, but you would probably want to check whether
the control contains a value before setting the focus there, or something
like that; otherwise updating a record could get annoying for the user, who
would be forced from one control to the other even if modifying just one
field. The form's Before Update event is usually the best place to perform
the sort of validation you want to do.
If it helps, Me.Dirty = False does the same thing as DoCmd.RunCommand
acCmdSaveRecord, as I understand it. "If Me.Dirty" means, in effect, "If
the record has changed".

 

[True.Kilted.Scot]

Baz,

Information requested, listed below:

1. The main form displays the following fields:

Employee Name - Drop-Down list
Date Worked - Text field formatted as dd/mm/yy
Project - Drop-Down list
Start Time - Text field formatted as HH:MM
End Time - Text field formatted as HH:MM
Duration - (End Time - Start Time)

2. The main form has, as its RecordSource, a query, the SQL of
which is as follows:

SELECT tblEmployee.fldEmployeeID, tblTimeSheet.fldUserID,
_
tblTimeSheet.fldWorkID,
tblTimeSheet.fldEmployeeID, _
tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, _
tblTimeSheet.fldProjectID,
tblProjects.fldProjectID, _
tblProjects.fldProjectDescription,
tblProjects.fldIsWork, _
tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, _
tblTimeSheet.fldSubmitted
FROM tblProjects INNER JOIN (tblEmployee INNER JOIN
tblTimeSheet _
ON tblEmployee.fldEmployeeID =
tblTimeSheet.fldEmployeeID) _
ON tblProjects.fldProjectID =
tblTimeSheet.fldProjectID
WHERE
(((tblEmployee.fldEmployeeID)=[Forms]![frmUATTimeSheet]![txtEmployeeID])
_
AND
((tblTimeSheet.fldUserID)=CurrentUser())) _
ORDER BY tblTimeSheet.fldDateWorked DESC ,
tblTimeSheet.fldStartTime;

The query retrieves the records from the main table,
tblTimeSheet, for the employee who's name is in the Employee Name
field, and who's ID matches
their logged user ID. This prevents Joe Bloggs from looking at
/ modifying John Smith's records.

3. txtEmployeeID is an unbound control, with no Control Source. It
has a RowSource, which is set to:

SELECT [fldSurname] & ", " & [fldForename] AS EmpName, _
[tblEmployee].[fldEmployeeID],
[tblEmployee].[fldLeft], _
[tblEmployee].[fldIncludeInHours] FROM
tblEmployee _
WHERE ((([tblEmployee].[fldLeft])=No) And _
(([tblEmployee].[fldIncludeInHours])=Yes)) _
ORDER BY [fldSurname] & ", " & [fldForename];

4. txtTSEmployeeID has a ControlSource of:

tblTimeSheet.fldEmployeeID

Within the AfterUpdate event of txtEmployeeID, this field is
set to Me.txtEmployeeID.Column(1). It is also set to this value within
the AddRecord & the SaveRecord.

5. Whilst the main form only displays the current record, the
subform displays all the entries that the employee has entered.

6. The subform has the same RecordSource as the one given in point
2 above.


I hope this is enough information for you to get an idea of what I am
attempting to do. If you need any more info, please shout.

Many thanks for your help so far.

Duncs

 

[Baz]

Baz,

Information requested, listed below:

1. The main form displays the following fields:

Employee Name - Drop-Down list
Date Worked - Text field formatted as dd/mm/yy
Project - Drop-Down list
Start Time - Text field formatted as HH:MM
End Time - Text field formatted as HH:MM
Duration - (End Time - Start Time)

2. The main form has, as its RecordSource, a query, the SQL of
which is as follows:

SELECT tblEmployee.fldEmployeeID, tblTimeSheet.fldUserID,
_
tblTimeSheet.fldWorkID,
tblTimeSheet.fldEmployeeID, _
tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, _
tblTimeSheet.fldProjectID,
tblProjects.fldProjectID, _
tblProjects.fldProjectDescription,
tblProjects.fldIsWork, _
tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, _
tblTimeSheet.fldSubmitted
FROM tblProjects INNER JOIN (tblEmployee INNER JOIN
tblTimeSheet _
ON tblEmployee.fldEmployeeID =
tblTimeSheet.fldEmployeeID) _
ON tblProjects.fldProjectID =
tblTimeSheet.fldProjectID
WHERE
(((tblEmployee.fldEmployeeID)=[Forms]![frmUATTimeSheet]![txtEmployeeID])
_
AND
((tblTimeSheet.fldUserID)=CurrentUser())) _
ORDER BY tblTimeSheet.fldDateWorked DESC ,
tblTimeSheet.fldStartTime;

The query retrieves the records from the main table,
tblTimeSheet, for the employee who's name is in the Employee Name
field, and who's ID matches
their logged user ID. This prevents Joe Bloggs from looking at
/ modifying John Smith's records.

3. txtEmployeeID is an unbound control, with no Control Source. It
has a RowSource, which is set to:

SELECT [fldSurname] & ", " & [fldForename] AS EmpName, _
[tblEmployee].[fldEmployeeID],
[tblEmployee].[fldLeft], _
[tblEmployee].[fldIncludeInHours] FROM
tblEmployee _
WHERE ((([tblEmployee].[fldLeft])=No) And _
(([tblEmployee].[fldIncludeInHours])=Yes)) _
ORDER BY [fldSurname] & ", " & [fldForename];

4. txtTSEmployeeID has a ControlSource of:

tblTimeSheet.fldEmployeeID

Within the AfterUpdate event of txtEmployeeID, this field is
set to Me.txtEmployeeID.Column(1). It is also set to this value within
the AddRecord & the SaveRecord.

5. Whilst the main form only displays the current record, the
subform displays all the entries that the employee has entered.

6. The subform has the same RecordSource as the one given in point
2 above.


I hope this is enough information for you to get an idea of what I am
attempting to do. If you need any more info, please shout.

Many thanks for your help so far.

Duncs

Hi Duncs,

This line of code is the problem:

Me.txtTSEmployeeID = Me.txtEmployeeID.Column(1)

txtTSEmployeeID is a bound control (i.e. it has a control source that is in
the record source), and the offending statement changes it's value. Because
the control is bound, changing it's value dirties the record - you are, in
effect, starting to enter a new record.

Either link your subform to txtEmployeeID instead, or create another,
unbound control to link it to instead of txtTSEmployeeID. The control
needn't be visible: in fact, linking subforms to a hidden text box on the
main form is a technique which has a few worthwhile uses.

Hope that helps,

Baz

 

[True.Kilted.Scot]

Baz,

Code removed and it now functions as it should.

There seems to be no detrimental effect to the operation of the databse
by removing the line of code.

Duncs