Please help

[Terrence Carroll]

I have a question about how to update an audit date field for third party
timecards. I did have a question about updating an audit date field tied to
one combo box yesterday and was able to get a workable solution with a Select
Case statement relatively quickly. However, today I need to update an audit
date field to the current date based upon properties or values entered in
several controls. For instance if the hours in our system match the hours on
our a third party providers time card I have a check box indicating "Hours
Match." If Hours Match is checked I want the Audit Date to equal todays
date. Also if the timesheet is missing, I have a check box for "Timesheet
Missing." If Timesheet Missing is checked I want the Audit Date to equal
todays date. I also have two text fields that show hours in the third party
system. If either of these two text fields are populated, I would like the
Audit Date to equal the current date. Once the audit date is populated I
would like the audit date field to be disabled. If all of the controls
mentioned above are null I would like the audit field to be null. What would
the best way to accomplish this be? Should I do a command button or should I
use the after update event on each control to update the audit status. I am
unsure how to code this and where the best place to put the code would be.
Any help you could provide would be great.

Thanks,

Terry Carroll

 

[Gina Whipp]

Terry,

I would use the After_Update of the fields to set the values of the
corresponding fields. Something like...

If Me.YourField = YourValue Then
Me.YourOtherField = YourValue
Else
Me.YourOtherField = AnotherYourValue
End If

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

message I have a question about how to update an audit date field for third party
timecards. I did have a question about updating an audit date field tied to
one combo box yesterday and was able to get a workable solution with a
Select
Case statement relatively quickly. However, today I need to update an audit
date field to the current date based upon properties or values entered in
several controls. For instance if the hours in our system match the hours
on
our a third party providers time card I have a check box indicating "Hours
Match." If Hours Match is checked I want the Audit Date to equal todays
date. Also if the timesheet is missing, I have a check box for "Timesheet
Missing." If Timesheet Missing is checked I want the Audit Date to equal
todays date. I also have two text fields that show hours in the third party
system. If either of these two text fields are populated, I would like the
Audit Date to equal the current date. Once the audit date is populated I
would like the audit date field to be disabled. If all of the controls
mentioned above are null I would like the audit field to be null. What
would
the best way to accomplish this be? Should I do a command button or should
I
use the after update event on each control to update the audit status. I am
unsure how to code this and where the best place to put the code would be.
Any help you could provide would be great.

Thanks,

Terry Carroll

 

[Terrence Carroll]

Thank you very much Ken and Gina - You both have been very helpful.

-Terry Carroll

 

[David W. Fenton]

Private Function GetAuditDate()

' Set audit date to Null by default
Me.[Audit Date] = Null

' examine each criterion one by one
If Me.[Hours Match] Then
Me.[Audit Date] = VBA.Date
ElseIf Me.[Timesheet Missing] Then
Me.[Audit Date] = VBA.Date
ElseIf Not IsNull(Me.[txtHours1]) Then
Me.[Audit Date] = VBA.Date
ElseIf Not IsNull(Me.[txtHours2]) Then
Me.[Audit Date] = VBA.Date
End If

' disable/lock audit date control if has value
Me.[Audit Date].Enabled = IsNull(Me.[Audit Date])
Me.[Audit Date].Locked = Not IsNull(Me.[Audit Date])

End Function

I find this code very puzzling. First off, it's dangerous to not
force evaluation of a control/field in a Boolean test. That is:

If Me.[Hours Match] Then

....should instead be one of these alternatives:

If (Me.[Hours Match]) Then

If (Me.[Hours Match] = True) Then

If Me.[Hours Match].Value Then

I find it simplest in this kind of case to just force evaluation by
putting it in parens.

Secondly, there's a four-level If/Then/ElseIf where all the
conditions result in exactly the same action. I don't see why a
single If statement with four conditions evaluated with Boolean Or
should not be equivalent:

If (Me.[Hours Match]) Or (Me.[Timesheet Missing]) _
Or (Not IsNull(Me.[txtHours1])) _
Or (Not IsNull(Me.[txtHours2])) Then
Me.[Audit Date] = VBA.Date
End If

Maybe I'm wrong on the logic somehow, but it seems to me to be
logically equivalent.

 

[David W. Fenton]

Can you explain for my edification why "it's dangerous to not
force evaluation of a control/field in a Boolean test". Its not
something that had ever occurred to me, but I'm always happy to
learn the error of my ways.

I don't know if it's a current problem, but back in the A97 days, it
could create an implicit reference that would prevent your app from
closing. It may be that current Access/VBA versions don't have this
problem, but I think it's good practice, just as it's good practice
to use ByVal instead of the default ByRef for sub/function
parameters when you don't actually need to pass by reference.

 

[joelgeraldine]

_çp^^^ùùù

Private Function GetAuditDate()

' Set audit date to Null by default
Me.[Audit Date] = Null

' examine each criterion one by one
If Me.[Hours Match] Then
Me.[Audit Date] = VBA.Date
ElseIf Me.[Timesheet Missing] Then
Me.[Audit Date] = VBA.Date
ElseIf Not IsNull(Me.[txtHours1]) Then
Me.[Audit Date] = VBA.Date
ElseIf Not IsNull(Me.[txtHours2]) Then
Me.[Audit Date] = VBA.Date
End If

' disable/lock audit date control if has value
Me.[Audit Date].Enabled = IsNull(Me.[Audit Date])
Me.[Audit Date].Locked = Not IsNull(Me.[Audit Date])

End Function

I find this code very puzzling. First off, it's dangerous to not
force evaluation of a control/field in a Boolean test. That is:

If Me.[Hours Match] Then

...should instead be one of these alternatives:

If (Me.[Hours Match]) Then

If (Me.[Hours Match] = True) Then

If Me.[Hours Match].Value Then

I find it simplest in this kind of case to just force evaluation by
putting it in parens.

Secondly, there's a four-level If/Then/ElseIf where all the
conditions result in exactly the same action. I don't see why a
single If statement with four conditions evaluated with Boolean Or
should not be equivalent:

If (Me.[Hours Match]) Or (Me.[Timesheet Missing]) _
Or (Not IsNull(Me.[txtHours1])) _
Or (Not IsNull(Me.[txtHours2])) Then
Me.[Audit Date] = VBA.Date
End If

Maybe I'm wrong on the logic somehow, but it seems to me to be
logically equivalent.