Required fields

[bdhtexas]

Is there a way to make certain fields within a form required fields, I don't want to make them required on the TABLE, just the form. I have a problem with certain individuals leaving certain fields blank when they update a form and I need it to stop. Any help would be appreciated.

 

[mohammadagul]

hi,
i have been using the following method for somtime and it had proven really good for my db.
1. create a new module and place the following code in it.

Public Function CheckForEntries() As Boolean

'Error-handler inserted on 18/09/04 at 12:14 by
'

On Error GoTo CheckForEntries_Error


'On Error GoTo Err_CheckEntries

Dim strMessage As String
Dim ctl As Control
Dim strControl As String

'set all controls to normal white background
Call SetBackColorNormal

'if not a new record check each controls tag property for Req
If Not Screen.ActiveForm.NewRecord Then
For Each ctl In Screen.ActiveForm.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Tag = "Req" Then
'only build a message for the first required control without an entry
If Nz(Len(ctl.Value), 0) = 0 And Nz(Len(strMessage), 0) = 0 Then
'this function requires that the label for the control have the same name as the control
'followed by _Label
strMessage = Screen.ActiveForm(ctl.ControlName & "_Label").Caption & " is a required field."
'set the strControl variable to the name of the required control that is not filled in
strControl = ctl.ControlName
End If
End If
Next

'if there is no message, then all required controls have entries
If Nz(Len(strMessage), 0) > 0 Then
MsgBox strMessage, vbOKOnly, "Required field left blank!"
'set the back color of the required control in question to yellow
Screen.ActiveForm(strControl).BackColor = 14548991 'Bright Yellow
'and set the focus to the control
Screen.ActiveForm(strControl).SetFocus
'and set the checkforentries to True so that any code in the form can be handled
CheckForEntries = True
Else
'if all required controls have entries set checkforentries to false
CheckForEntries = False
End If
End If

Exit_CheckEntries:
Exit Function

Err_CheckEntries:
MsgBox err.Description
GoTo Exit_CheckEntries

CheckForEntries_Exit:
Exit Function

CheckForEntries_Error:
MsgBox "Unexpected error - " & err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Accounting&&Invoicing - CheForEnt"
Resume CheckForEntries_Exit

End Function

Public Sub SetBackColorNormal()
On Error Resume Next
Dim ctl As Control

For Each ctl In Screen.ActiveForm.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.BackColor = -2147483643 'normal white back color
End If
Next

End Sub

Step2
in your form , the field you want to be required, Place "Req" in the tag property.


Step3
in the on current event of the form place the following code

On Error GoTo Form_Current_Error


Call SetBackColorNormal


Form_Current_Exit:
Exit Sub

Form_Current_Error:
MsgBox "Unexpected error - " & err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Accounting&&Invoicing - ForCur"
Resume Form_Current_Exit

thats it..
try it ... it worked for me ... hope it workd fo ryou also

 

[mohammadagul]

hi,
i have been using the following method for somtime and it had proven really good for my db.
1. create a new module and place the following code in it.

Public Function CheckForEntries() As Boolean

'Error-handler inserted on 18/09/04 at 12:14 by
'

On Error GoTo CheckForEntries_Error


'On Error GoTo Err_CheckEntries

Dim strMessage As String
Dim ctl As Control
Dim strControl As String

'set all controls to normal white background
Call SetBackColorNormal

'if not a new record check each controls tag property for Req
If Not Screen.ActiveForm.NewRecord Then
For Each ctl In Screen.ActiveForm.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Tag = "Req" Then
'only build a message for the first required control without an entry
If Nz(Len(ctl.Value), 0) = 0 And Nz(Len(strMessage), 0) = 0 Then
'this function requires that the label for the control have the same name as the control
'followed by _Label
strMessage = Screen.ActiveForm(ctl.ControlName & "_Label").Caption & " is a required field."
'set the strControl variable to the name of the required control that is not filled in
strControl = ctl.ControlName
End If
End If
Next

'if there is no message, then all required controls have entries
If Nz(Len(strMessage), 0) > 0 Then
MsgBox strMessage, vbOKOnly, "Required field left blank!"
'set the back color of the required control in question to yellow
Screen.ActiveForm(strControl).BackColor = 14548991 'Bright Yellow
'and set the focus to the control
Screen.ActiveForm(strControl).SetFocus
'and set the checkforentries to True so that any code in the form can be handled
CheckForEntries = True
Else
'if all required controls have entries set checkforentries to false
CheckForEntries = False
End If
End If

Exit_CheckEntries:
Exit Function

Err_CheckEntries:
MsgBox err.Description
GoTo Exit_CheckEntries

CheckForEntries_Exit:
Exit Function

CheckForEntries_Error:
MsgBox "Unexpected error - " & err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Accounting&&Invoicing - CheForEnt"
Resume CheckForEntries_Exit

End Function

Public Sub SetBackColorNormal()
On Error Resume Next
Dim ctl As Control

For Each ctl In Screen.ActiveForm.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.BackColor = -2147483643 'normal white back color
End If
Next

End Sub

Step2
in your form , the field you want to be required, Place "Req" in the tag property.


Step3
in the on current event of the form place the following code

On Error GoTo Form_Current_Error


Call SetBackColorNormal


Form_Current_Exit:
Exit Sub

Form_Current_Error:
MsgBox "Unexpected error - " & err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Accounting&&Invoicing - ForCur"
Resume Form_Current_Exit

thats it..
try it ... it worked for me ... hope it workd fo ryou also

 

[dcx693]

I would put the code into a different event. Since the problem is occurring with updates to records, perhaps the Before Update event instead of the On Current event.