Checking for 3 dates

[Afrosheen via AccessMonster.com]

I have a form when once completed it checks 3 different fields for dates
before it will archive. I have one routine that does work. I was wondering if
I could have all three date fields check from the one routine.

20 If IsNull(WorkRec) Then
30 Call MsgBox("You must Enter a Date in the Plan Rec Field." _
& vbCrLf & "" _
& vbCrLf & " Before you can Archive. A Date is Required" _
, vbExclamation, Application.Name)
40 'Me!Combo20.Dropdown
50 WorkRec.SetFocus


The other two date fields are called IntRec and FinalDue.

I don't know if there's a way this can be done. If not then I guess I could
have 3 individual checks.

Thanks for reading this post. In the past this group has helped me
tremendously.

 

[Dirk Goldgar]

I have a form when once completed it checks 3 different fields for dates
before it will archive. I have one routine that does work. I was wondering
if
I could have all three date fields check from the one routine.

20 If IsNull(WorkRec) Then
30 Call MsgBox("You must Enter a Date in the Plan Rec Field." _
& vbCrLf & "" _
& vbCrLf & " Before you can Archive. A Date is Required"
_
, vbExclamation, Application.Name)
40 'Me!Combo20.Dropdown
50 WorkRec.SetFocus


The other two date fields are called IntRec and FinalDue.

I don't know if there's a way this can be done. If not then I guess I
could
have 3 individual checks.

Thanks for reading this post. In the past this group has helped me
tremendously.

For this sort of thing, I use a function I wrote that checks every control
on the form to see if the ones tagged as "Required" are not empty. If any
are not filled in, a message is displayed listing them, and the focus is set
to the first of those in the tab order.

Here's the function:

'------ start of function code ------
Function fncRequiredFieldsMissing(frm As Form) As Boolean

On Error Resume Next

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim strMsgName As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag Like "*Required*" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If

If blnNoValue Then

strMsgName = vbNullString
If .Controls.Count = 1 Then
strMsgName = .Controls(0).Caption
If Right$(strMsgName, 1) = ":" Then
strMsgName = Trim$(Left$(strMsgName,
Len(strMsgName) - 1))
End If
End If
If Len(strMsgName) = 0 Then
strMsgName = .Name
Select Case Left$(strMsgName, 3)
Case "txt", "cbo", "lst", "chk"
strMsgName = Mid(strMsgName, 4)
End Select
End If

strErrorMessage = strErrorMessage & vbCr & _
" " & strMsgName

If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If

End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, _
vbInformation, "Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'------ end of function code ------

The trick here is that I set the .Tag property of each required field to
"Required".

I would usually call the function from the form's BeforeUpdate event, not
allowing the record to be saved if any required fields are missing. Like
this:

'------ start of form code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'------ end of form code ------

 

[Dale_Fye via AccessMonster.com]

Dirk,

Just wondering whether there is any advantage of using Like over Instr in
this instance?

If .Tag Like "*Required*" Then

as opposed to:

if instr(.Tag, "Required") > 0 Then

Does one process more quickly or efficiently than the other?

Dale

I have a form when once completed it checks 3 different fields for dates
before it will archive. I have one routine that does work. I was wondering

[quoted text clipped - 18 lines]

Thanks for reading this post. In the past this group has helped me
tremendously.

For this sort of thing, I use a function I wrote that checks every control
on the form to see if the ones tagged as "Required" are not empty. If any
are not filled in, a message is displayed listing them, and the focus is set
to the first of those in the tab order.

Here's the function:

'------ start of function code ------
Function fncRequiredFieldsMissing(frm As Form) As Boolean

On Error Resume Next

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim strMsgName As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag Like "*Required*" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If

If blnNoValue Then

strMsgName = vbNullString
If .Controls.Count = 1 Then
strMsgName = .Controls(0).Caption
If Right$(strMsgName, 1) = ":" Then
strMsgName = Trim$(Left$(strMsgName,
Len(strMsgName) - 1))
End If
End If
If Len(strMsgName) = 0 Then
strMsgName = .Name
Select Case Left$(strMsgName, 3)
Case "txt", "cbo", "lst", "chk"
strMsgName = Mid(strMsgName, 4)
End Select
End If

strErrorMessage = strErrorMessage & vbCr & _
" " & strMsgName

If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If

End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, _
vbInformation, "Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'------ end of function code ------

The trick here is that I set the .Tag property of each required field to
"Required".

I would usually call the function from the form's BeforeUpdate event, not
allowing the record to be saved if any required fields are missing. Like
this:

'------ start of form code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'------ end of form code ------

 

[Dirk Goldgar]

Just wondering whether there is any advantage of using Like over Instr in
this instance?

If .Tag Like "*Required*" Then

as opposed to:

if instr(.Tag, "Required") > 0 Then

Does one process more quickly or efficiently than the other?

I never tested it before, so I just did. It turns out that InStr is
marginally faster than Like for this purpose. I do mean *marginally* -- it
takes a high-resolution timer (or lots of reps) to tell the difference. But
since there's a difference, I wil amend my code to use InStr instead of Like
when testing for the simple presence of a substring.

Thanks for raising the question, Dale.

 

[Dale_Fye via AccessMonster.com]

Dirk,

The reason I asked is because until I saw it in your code, I didn't even
realize the VB had a "Like" operator that worked like this. I've obviously
used it in SQL statements, but never in VB code.

When comparing a value with a constant string, I think I prefer the "Like"
method for readability. But it gets a little less friendly when you are
testing variable or the value of a form control.

Dale

Just wondering whether there is any advantage of using Like over Instr in
this instance?

[quoted text clipped - 6 lines]

Does one process more quickly or efficiently than the other?

I never tested it before, so I just did. It turns out that InStr is
marginally faster than Like for this purpose. I do mean *marginally* -- it
takes a high-resolution timer (or lots of reps) to tell the difference. But
since there's a difference, I wil amend my code to use InStr instead of Like
when testing for the simple presence of a substring.

Thanks for raising the question, Dale.

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me. I set up your function as per your program. I
set the date field to Required and ran the program. Here is the code where it
updates the record.

This is where it is supposed to update the record.

Private Sub Combo20_AfterUpdate()
10 On Error GoTo Combo20_AfterUpdate_Error

Me.DateAch = Date
Me.Archive = True
Requery

On Error GoTo 0
Exit Sub

Combo20_AfterUpdate_Error:
70 Err.Description = Err.Description & " In Procedure " &
"Combo20_AfterUpdate of VBA Document Form_frmTaps Subform"
80 Call LogError(Err.Number, Err.Description, "Combo20_AfterUpdate")

End Sub


'-----------------------------------------------------------------------------
----------
' Procedure : Form_BeforeUpdate
' Author : Tom
' Date : 6/20/2009
' Purpose :
' Notes :
'-----------------------------------------------------------------------------
----------
'
Private Sub Form_BeforeUpdate(Cancel As Integer)
10 On Error GoTo Form_BeforeUpdate_Error

20 Cancel = fncRequiredFieldsMissing(Me)

30 On Error GoTo 0
40 Exit Sub

Form_BeforeUpdate_Error:
50 Err.Description = Err.Description & " In Procedure " &
"Form_BeforeUpdate of VBA Document Form_frmTaps Subform"
60 Call LogError(Err.Number, Err.Description, "Form_BeforeUpdate")

End Sub
This is your Call Part.
It didn't work. It is supposed to check the required Date field?

Am I doing something wrong?

I have a form when once completed it checks 3 different fields for dates
before it will archive. I have one routine that does work. I was wondering

[quoted text clipped - 18 lines]

Thanks for reading this post. In the past this group has helped me
tremendously.

For this sort of thing, I use a function I wrote that checks every control
on the form to see if the ones tagged as "Required" are not empty. If any
are not filled in, a message is displayed listing them, and the focus is set
to the first of those in the tab order.

Here's the function:

'------ start of function code ------
Function fncRequiredFieldsMissing(frm As Form) As Boolean

On Error Resume Next

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim strMsgName As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag Like "*Required*" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If

If blnNoValue Then

strMsgName = vbNullString
If .Controls.Count = 1 Then
strMsgName = .Controls(0).Caption
If Right$(strMsgName, 1) = ":" Then
strMsgName = Trim$(Left$(strMsgName,
Len(strMsgName) - 1))
End If
End If
If Len(strMsgName) = 0 Then
strMsgName = .Name
Select Case Left$(strMsgName, 3)
Case "txt", "cbo", "lst", "chk"
strMsgName = Mid(strMsgName, 4)
End Select
End If

strErrorMessage = strErrorMessage & vbCr & _
" " & strMsgName

If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If

End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, _
vbInformation, "Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'------ end of function code ------

The trick here is that I set the .Tag property of each required field to
"Required".

I would usually call the function from the form's BeforeUpdate event, not
allowing the record to be saved if any required fields are missing. Like
this:

'------ start of form code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'------ end of form code ------

 

[Dirk Goldgar]

(please not comments and questions inline)

Thanks for getting back to me. I set up your function as per your program.
I
set the date field to Required and ran the program.

When you say you set the date field to Required, do you mean that you set
the Tag property of one or more controls to "Required", in the design view
of the form? Or did you just set a field's Required property to Yes, in
design view of the table? You should have done the former (set the Tag
property); the latter is irrelevant.

What controls did you tag as "Required"?

Here is the code where it
updates the record.

This is where it is supposed to update the record.

Private Sub Combo20_AfterUpdate()
10 On Error GoTo Combo20_AfterUpdate_Error

Me.DateAch = Date
Me.Archive = True
Requery

On Error GoTo 0
Exit Sub

Combo20_AfterUpdate_Error:
70 Err.Description = Err.Description & " In Procedure " &
"Combo20_AfterUpdate of VBA Document Form_frmTaps Subform"
80 Call LogError(Err.Number, Err.Description,
"Combo20_AfterUpdate")

End Sub

Are you using the Requery method just to force the record to save? If so,
there's a much more efficient method:

Me.Dirty = False

'-----------------------------------------------------------------------------
----------
' Procedure : Form_BeforeUpdate
' Author : Tom
' Date : 6/20/2009
' Purpose :
' Notes :
'-----------------------------------------------------------------------------
----------
'
Private Sub Form_BeforeUpdate(Cancel As Integer)
10 On Error GoTo Form_BeforeUpdate_Error

20 Cancel = fncRequiredFieldsMissing(Me)

30 On Error GoTo 0
40 Exit Sub

Form_BeforeUpdate_Error:
50 Err.Description = Err.Description & " In Procedure " &
"Form_BeforeUpdate of VBA Document Form_frmTaps Subform"
60 Call LogError(Err.Number, Err.Description, "Form_BeforeUpdate")

End Sub
This is your Call Part.
It didn't work. It is supposed to check the required Date field?

Am I doing something wrong?

Presumably, but I'm not yet sure what. Your answers to the questions above
will help.

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me Dirk,

1) I set the date fields to Required in the Design view.
2) The three date fields do not have any Required in a control.
3) I have a sub form in data view. When I do the requery it just so it will
"Archive" the file and not be part of the sub form. The "Archive" just puts
an X in a true/false box. Then when I requery the sub form it doesn't show
the line I just entered.

(please not comments and questions inline)

Thanks for getting back to me. I set up your function as per your program.
I
set the date field to Required and ran the program.

When you say you set the date field to Required, do you mean that you set
the Tag property of one or more controls to "Required", in the design view
of the form? Or did you just set a field's Required property to Yes, in
design view of the table? You should have done the former (set the Tag
property); the latter is irrelevant.

What controls did you tag as "Required"?

Here is the code where it
updates the record.

[quoted text clipped - 18 lines]

End Sub

Are you using the Requery method just to force the record to save? If so,
there's a much more efficient method:

Me.Dirty = False

'-----------------------------------------------------------------------------
----------

[quoted text clipped - 24 lines]

Am I doing something wrong?

Presumably, but I'm not yet sure what. Your answers to the questions above
will help.

 

[Dirk Goldgar]

Thanks for getting back to me Dirk,

1) I set the date fields to Required in the Design view.
2) The three date fields do not have any Required in a control.

If I understand you right, this is the problem. The function I posted looks
at all the controls that have their Tag property set to "Required".
Therefore, as I said in my original post (but you probably overlooked), you
must set the Tag property of each control that you want the function to
check.

So do this: Open your form in design view. Use Shift+Click to select all
three of the date fields at once. Then press Alt+Enter to open their joint
property sheet. Go to the Other tab of the property sheet, find the Tag
property, and enter this:

Required

.... on that line. Then close the property sheet, save the form, and close
the form.

Now open the form in form view and see what happens when you create a new
record and leave any of those date fields blank.

3) I have a sub form in data view. When I do the requery it just so it
will
"Archive" the file and not be part of the sub form. The "Archive" just
puts
an X in a true/false box. Then when I requery the sub form it doesn't show
the line I just entered.

Ah, now I understand.

 

[Afrosheen via AccessMonster.com]

Thanks Dirk for the help. It works perfectly now.

I just have to fix the requery problem. When I entered all the information
and left the dates out, your program worked. The problem is the it does the
requery first then it comes up with an error saying there is no record or
something like that. I some how have to do the requery after all information
in in place. I'll have to think about that.

Something like:
if all fields and all required fields have something in it then
requery
else
go back to your program
endif

Thanks again.

 

[Afrosheen via AccessMonster.com]

Dirk, is there somewhere else I can put your Cancel =
fncRequiredFieldsMissing(Me) statement besides in the "Before Form Update" ?
I'm trying to check the required fields before I enter the last field. It
keeps coming up with an error which says that the "cancel" variable can not
be found.

Thanks for the help so far. It has helped me out greatly.

Thanks Dirk for the help. It works perfectly now.

I just have to fix the requery problem. When I entered all the information
and left the dates out, your program worked. The problem is the it does the
requery first then it comes up with an error saying there is no record or
something like that. I some how have to do the requery after all information
in in place. I'll have to think about that.

Something like:
if all fields and all required fields have something in it then
requery
else
go back to your program
endif

Thanks again.
[quoted text clipped - 28 lines]

Ah, now I understand.

 

[Dirk Goldgar]

Dirk, is there somewhere else I can put your Cancel =
fncRequiredFieldsMissing(Me) statement besides in the "Before Form Update"
?
No.

I'm trying to check the required fields before I enter the last field. It
keeps coming up with an error which says that the "cancel" variable can
not
be found.

That's because the Cancel argument only appears for events that can be
canceled, and the only one that makes sense in this context is the form's
BeforeUpdate event.

I don't understand why you want to check the required fields *before* the
user makes some attempt to save the record. What are you trying to do with
this form, and why?

 

[Dirk Goldgar]

Thanks Dirk for the help. It works perfectly now.

I just have to fix the requery problem. When I entered all the information
and left the dates out, your program worked. The problem is the it does
the
requery first then it comes up with an error saying there is no record or
something like that. I some how have to do the requery after all
information
in in place. I'll have to think about that.

Without seeing the exact sequence of events, I think the problem is probably
that your requery forces the record to be saved, which (if the record can't
be saved) raises and error. I suggest that you check whether the record can
be saved *before* requerying, like this:

If fncRequiredFieldsMissing(Me) = False Then
Me.Requery
End If

 

[Afrosheen via AccessMonster.com]

Thanks again Dirk. It is working the way I think it should now. I really
appreciate your help. It will save time and make sure the data entry person
has something in place.

Thanks again.

Thanks Dirk for the help. It works perfectly now.

[quoted text clipped - 5 lines]

information
in in place. I'll have to think about that.

Without seeing the exact sequence of events, I think the problem is probably
that your requery forces the record to be saved, which (if the record can't
be saved) raises and error. I suggest that you check whether the record can
be saved *before* requerying, like this:

If fncRequiredFieldsMissing(Me) = False Then
Me.Requery
End If