Date Validation

[Gastank]

Can anyone please help me?
Users enter a date on a form control [txtas400date] - a date which is usually within the previous 7 days of the current date. I want to prompt the user to check the date entered if it is older than the 7 days but allow entry if the user confirms. I also want to prevent a date later than the current date. I entered the following on the "Before update" event of the control but receive a Run-time error message 2115. Where am I going wrong?

Private Sub txtas400date_BeforeUpdate(Cancel As Integer)
If DateDiff("d", [txtas400date], Date) >= 7 Or DateDiff("d", Date, [txtas400date]) >= 1 Then
If MsgBox("Please check date, Correct?", vbYesNo) = vbYes Then
' If User chooses yes, then force a save by setting Me.Dirty = False
Me.Dirty = False
Else
'set text box to null (="")
Me!txtas400date = Null

Cancel = True
End If
End If

End Sub

 

[Rich]

Something like
Private Sub txtDate_BeforeUpdate(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString
If DateDiff("d", Me.txtDate, Date) >= 7 Or DateDiff("d", Date, Me,txtDate) >= 1 Then
Beep

Msg = "Please check date, Correct?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Date verification"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Exit Sub
Else
Cancel = True
Me.Undo
End If
End If
End Sub
where Me.txtDate is your re-named date field

 

[Gastank]

Rich,

thanks for the quick reply, I'll try your suggestion staight away - thanks again



Gastank

 

[Pat Hartman]

Using "Me.Dirty = False" to save a record is obtuse. If you want to save a record, use the command that saves a record -
DoCmd.RunCommand acCmdSaveRecord
Nobody will have any question what that command does and you won't have to write notes to yourself so you won't forget.

Anyway, Rich's suggestion should solve the problem. It was caused by the Me.Dirty = False. You were essentially instructing Access to execute the BeforeUpdate event recursively. BeforeUpdate is the last event that Access executed before actually saving a record. You don't need to do anything in this event to force a save, the save will happen as soon as the event ends unless you cancel it. So the Me.Dirty = False told Access (in a round-about way) to save the record which caused Access to run the BeforeUpdate event where you told Access to save the record which caused Access to..... you see the problem.

 

[Gastank]

Pat,

as ever a lucid reply, I'll save this one.

Many Thanks

Gastank

 

[bassman197]

Hello Pat,
Sorry to just reply out of the blue, I read this thread with great interest - I have a similar problem that I've been dealing with. I have inherited a small database that writes contracts, addendums and inovices. Since ONE contract will only have ONE addendum, all of the fields for these are in the same table and our users often encounter the "This record has been changed by another user" message when working with both the contract and addendum forms. The other user is simply the other form accessing the same record. I'd like to make this bulletproof, so our people can have both forms open and make changes at will. I was considering trying dirty=false. I did try SaveRecord, but Access didn't like it - maybe because a second form accessing the record was still open. Maybe, I should isolate the fields that are unique to the addenum form in their own table, and make it a one to one relationship? Then there would be no conflict, since the two forms are looking at different records? I've heard that one to one is not used that often, but maybe it's the way to avoid the "This record has been changed" error in this case? Any insight would be appreciated. Thanks!

Using "Me.Dirty = False" to save a record is obtuse. If you want to save a record, use the command that saves a record -
DoCmd.RunCommand acCmdSaveRecord
Nobody will have any question what that command does and you won't have to write notes to yourself so you won't forget.

Anyway, Rich's suggestion should solve the problem. It was caused by the Me.Dirty = False. You were essentially instructing Access to execute the BeforeUpdate event recursively. BeforeUpdate is the last event that Access executed before actually saving a record. You don't need to do anything in this event to force a save, the save will happen as soon as the event ends unless you cancel it. So the Me.Dirty = False told Access (in a round-about way) to save the record which caused Access to run the BeforeUpdate event where you told Access to save the record which caused Access to..... you see the problem.