IF Statement- If only it would work both ways!

jammin140900

Registered User.
Local time
Tomorrow, 02:09
Joined
Aug 18, 2008
Messages
35
Hi there,

I have a datasheet subform with milestone items where users enter dates as each milestone passes and I'm setting up a automatic status box that continually updates as each milestone date get's entered.

I've got a few "IF" statesments to work this and it works perfectly when a user enters a date and moves away from the field, the status changes automatically. I also want it to be able to back-track in that, if for some reason the user accidently enters the date in the wrong milestone box and clears the field, I want the status to automatically revert back to the former status. Using the same theory, this doesn't work. I'm not sure if it's a refresh problem or something else?

Here's a copy of the code..

Private Sub DateReferredtoAuditor_BeforeUpdate(Cancel As Integer)
lngrecordnum = Forms![frmAuditing].CurrentRecord
End Sub


Private Sub DateReferredtoAuditor_AfterUpdate()
'**31/08/09 MF- Automation of Audit Status Function

If Me.DateReferredtoAuditor <> "" And Me.AuditStatusID = 1 Then
Me.AuditStatusID = 2
ElseIf Me.DateReferredtoAuditor = "" And Me.AuditStatusID = 2 Then
Me.AuditStatusID = 1
End If

Forms![frmAuditing].Requery
DoCmd.GoToRecord acDataForm, "frmAuditing", acGoTo, lngrecordnum
End Sub


Any ideas please? Don't know why the same theory doesn't work in the opposite direction! Thanks,Jammin
 
Have you tried testing with the IsNull() function in place of ZLS? i.e.,

If Not IsNull(Me.DateReferredtoAuditor) And Me.AuditStatusID = 1 Then ... etc.,
 
Just tried that and it doesn't work either.. No error messages but it doesn't do anything...

Any other thoughts?
 
Mate, you are a legend! That actually did work.. It was only that it was the other way around:

If IsNull(Me.DateReferredtoAuditor) And Me.AuditStatusID = 1 Then

Thanks a lot for your help. I was really irriated and stuck with this for a day now! :)
 
If you set stop points, and you mouse-over the Me.DateReferredtoAuditor (or alternatively, use Debug.Print Me.DateReferredtoAuditor) what value does it return after you've deleted the value from it?

Ok - nevermind. Sounds like you have it solved!
 
Last edited:
By the way, do you know how to stop making it go to the very first record after executing the code? I have a form and this is a subform in datasheet view. Quite annoying when it scrolls automatically to the very first record on the datasheet...
 
Requery is a powerful little method (like pressing the nuclear launch button). If you absolutely need to use it, then it would take some way of backtracking to return the cursor to the *current* record, such as saving a key value from the record, then doing a Find on it after the Requery.

Otherwise, if you can live without the Requery (or can wait to run it at some later time) then you should be in the clear as far as staying on or in the vicinity of your immediate record.
 
NP - hope you get your project working how you need :)
 

Users who are viewing this thread

Back
Top Bottom