I’m hoping someone can help with this problem which I have been struggling with for a few days now. So much so that I have created a small database to specifically try to resole the problem!
My problem concerns the use of validation for all fields on a form before I save it. Over the last few days I have read extensively on various forums including this one on how best to do validation for all fields on a form and have concluded that it is best done on the Form_BeforeUpdate event, so this is what I have done. Originally I preferred the use of my own Close button, rather than the ‘X’ button, but I seem to get different errors for both methods.
For the purposes of trying to resolve the problem, I have created a small relational database with a few small tables, and then created a form for each table for data entry. I have used one of these forms for this experiment:
I have created a Products table with a few fields and a Products form with all of the fields on it. I have added the word “Required” (without the quotes) to the Tag field of each control on the form. I have added the following code to the Form_BeforeUpdate event and my Close button (most of the code has been created from code on this forum, including the use of ‘If Me.Dirty Then Me.Dirty = False’ just before my’ DoCmd.Close’ code):
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
If ctl.Tag = "Required" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
ctl.BackColor = 14680063
Cancel = True
Exit For
End If
End If
Next
Exit_BeforeUpdate:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
If I make no changes to any existing record, or do not add a new record, the form closes correctly, irrespective of whether I have pressed the ‘X’ button or my Close button.
If, after creating a new record or editing an existing record and leave any of the fields blank, I press the ‘X’ button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message informing me that ‘You can’t save this record at this time’ and giving me a choice of either closing the form (which discards any changes) or leaving the form open (which puts the cursor into the field in error – which is as it should be).
If, after creating a new record or editing an existing record and leave any of the fields blank, I press my Close button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message (number 2101) informing me that (The setting you have entered isn’t valid for this property’. When I click OK to this message, the cursor is positioned in the field in error – which is as it should be.
My question is this – why do I get these Access messages and how do I get rid of them?
Incidentally, I would prefer to list all the fields in error in one message, but have realised that if the user does not correct them all they will get the message repeated for the remaining controls anyway, so I am not too bothered as long as I can get through this one!
Many thanks for any help in advance.
My problem concerns the use of validation for all fields on a form before I save it. Over the last few days I have read extensively on various forums including this one on how best to do validation for all fields on a form and have concluded that it is best done on the Form_BeforeUpdate event, so this is what I have done. Originally I preferred the use of my own Close button, rather than the ‘X’ button, but I seem to get different errors for both methods.
For the purposes of trying to resolve the problem, I have created a small relational database with a few small tables, and then created a form for each table for data entry. I have used one of these forms for this experiment:
I have created a Products table with a few fields and a Products form with all of the fields on it. I have added the word “Required” (without the quotes) to the Tag field of each control on the form. I have added the following code to the Form_BeforeUpdate event and my Close button (most of the code has been created from code on this forum, including the use of ‘If Me.Dirty Then Me.Dirty = False’ just before my’ DoCmd.Close’ code):
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
If ctl.Tag = "Required" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
ctl.BackColor = 14680063
Cancel = True
Exit For
End If
End If
Next
Exit_BeforeUpdate:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
If I make no changes to any existing record, or do not add a new record, the form closes correctly, irrespective of whether I have pressed the ‘X’ button or my Close button.
If, after creating a new record or editing an existing record and leave any of the fields blank, I press the ‘X’ button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message informing me that ‘You can’t save this record at this time’ and giving me a choice of either closing the form (which discards any changes) or leaving the form open (which puts the cursor into the field in error – which is as it should be).
If, after creating a new record or editing an existing record and leave any of the fields blank, I press my Close button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message (number 2101) informing me that (The setting you have entered isn’t valid for this property’. When I click OK to this message, the cursor is positioned in the field in error – which is as it should be.
My question is this – why do I get these Access messages and how do I get rid of them?
Incidentally, I would prefer to list all the fields in error in one message, but have realised that if the user does not correct them all they will get the message repeated for the remaining controls anyway, so I am not too bothered as long as I can get through this one!
Many thanks for any help in advance.