Question for D. Goldgar

  • Thread starter Thread starter Lucas
  • Start date Start date
L

Lucas

Mr. Goldgar,

A month or more ago, you provided me with the following
instructions on how to make a field Required and how then
to display a message asking the user to fill in those
fields. Your instructions are below:

The function below checks the Tag property of every
control on the form
to see if it contains the string "Required". If it does,
the control
must not be Null. If there are any required controls
that are empty,
the function displays a message listing them, sets the
focus to the
first one in the tab index, and returns True. If all
required controls
have been filled in, the function returns false.

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

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage 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 = "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
strErrorMessage =
strErrorMessage & vbCr & _
" " & .Name
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 -----

Having saved this function in a standard module, you can
call it from a
form's BeforeUpdate event procedure like this:

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

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'---- end of event procedure code -----

I copied and pasted your code into a module, saved it as
fncRequiredFieldsMissing. Then I took a control from my
form, typed Required for its tag, and inserted your code
above (putting the correct form name in) into the Before
Update procedure for the form. I get a compile error in
VBA saying it did not expect a module. I've retried it
now 3 times (from scratch), following your instructions
carefully, and get the same message each time. Do you
have any ideas?

Thanks for all the help you've given me in the past.

Lucas
 
Lucas said:
Mr. Goldgar,

A month or more ago, you provided me with the following
instructions on how to make a field Required and how then
to display a message asking the user to fill in those
fields. Your instructions are below:

The function below checks the Tag property of every
control on the form
to see if it contains the string "Required". If it does,
the control
must not be Null. If there are any required controls
that are empty,
the function displays a message listing them, sets the
focus to the
first one in the tab index, and returns True. If all
required controls
have been filled in, the function returns false.

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

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage 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 = "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
strErrorMessage =
strErrorMessage & vbCr & _
" " & .Name
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 -----

Having saved this function in a standard module, you can
call it from a
form's BeforeUpdate event procedure like this:

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

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'---- end of event procedure code -----

I copied and pasted your code into a module, saved it as
fncRequiredFieldsMissing. Then I took a control from my
form, typed Required for its tag, and inserted your code
above (putting the correct form name in) into the Before
Update procedure for the form. I get a compile error in
VBA saying it did not expect a module. I've retried it
now 3 times (from scratch), following your instructions
carefully, and get the same message each time. Do you
have any ideas?

Thanks for all the help you've given me in the past.

Lucas
Click to expand...

The problem is that you saved the module with the same name as the
function. That doesn't work -- a module mustn't have the same name as
any public function. Rename the module -- I'd suggest something like
"basUtilities" or "modUtilities", based on the idea that you may put
other functions of general utility into this module. After you've
renamed the module, the function ought to work, barring any other errors
that may have been introduced in the transcription.
 
Back
Top