Some background: The form is for entering obituary data. On one of the form's tabs, a combo box (FirstPublicationName) lists newspapers the obituary appears in. Other fields are for recording the column (FirstPublicationColumn) and page no. (FirstPublicationPage) where the obit appears. If the user specifies column and/or page no., but no newspaper, I want an error message to appear.
I'm now also receiving another error message when I try to save a record:
The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
Here's the Sub's code. It's triggered by a Before Update form property:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'This procedure is triggered either by clicking one of the navigation arrows in the
'bottom of the frame (previous record, next record, last record, new record),
'or by typing a record number and pressing Enter. Its actions are the same
'as those when the Save button is clicked in any of the form's tabs (e.g.,
'the SaveRecord1_Click procedure): First check for errors, and then
'save the record and display a confirmation message.
If LenB([Gender] & "") = 0 Or LenB([LastName] & "") = 0 _
Then GoTo Err_Form_BeforeUpdate
'If Gender or LastName fields are blank, go to error message.
'-------------------
If Me.FirstPublicationDate < Me.DeathDate _
Then GoTo Err_FirstPublicationDate_Change
'If FirstPublicationDate is earlier than DeathDate,
'go to error message.
'-------------------
If LenB([FirstPublicationName] & "") = 0 _
And LenB([FirstPublicationNameNote] & "") = 0 _
And LenB([FirstPublicationColumn] & "") > 0 _
Or LenB([FirstPublicationPage] & "") > 0 _
Then GoTo Err_FirstObitPageOrColumn_Change
'If FirstPublicationName and FirstPublicationNameNote
'fields are blank, and FirstPublicationColumn
'or FirstPublicationPage field has content,
'go to error message.
If LenB([SecondPublicationName] & "") = 0 _
And LenB([SecondPublicationNameNote] & "") = 0 _
And LenB([SecondPublicationColumn] & "") > 0 _
Or LenB([SecondPublicationPage] & "") > 0 _
Then GoTo Err_SecondObitPageOrColumn_Change
'If SecondPublicationName and SecondPublicationNameNote
'fields are blank, and SecondPublicationColumn or
'SecondPublicationPage field has content,
'go to error message.
'-------------------
If LenB([ThirdPublicationName] & "") = 0 _
And LenB([ThirdPublicationNameNote] & "") = 0 _
And LenB([ThirdPublicationColumn] & "") > 0 _
Or LenB([ThirdPublicationPage] & "") > 0 _
Then GoTo Err_ThirdObitPageOrColumn_Change
'If ThirdPublicationName and ThirdPublicationNameNote
'fields are blank, and ThirdPublicationColumn or
'ThirdPublicationPage field has content,
'go to error message.
Cancel = True
DoCmd.RunCommand acCmdSaveRecord
'----------------------
'Error Messages for Form_BeforeUpdate(Cancel As Integer)
'----------------------
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
MsgBox "On the Identity tab, please specify both Gender and Last Name."
If LenB([Gender] & "") = 0 Then
Gender.SetFocus
ElseIf LenB([Gender] & "") > 0 Then
LastName.SetFocus
End If
Cancel = True
'----------------------
Exit_FirstPublicationDate_Change:
Exit Sub
Err_FirstPublicationDate_Change:
MsgBox "Date of Death (on Death Age/Date tab) must be earlier" & vbCrLf & _
"or the same as First Obituary Date (on Obituary tab)."
DeathDate.SetFocus
Cancel = True
'----------------------
Exit_FirstObitPageOrColumn_Change:
Exit Sub
Err_FirstObitPageOrColumn_Change:
MsgBox "On the Obituary tab, there must be a First Obituary Publication" & vbCrLf & _
"or note before there is a First Obituary Column or First Obituary Page."
FirstPublicationName.SetFocus
Cancel = True
'----------------------
Exit_SecondObitPageOrColumn_Change:
Exit Sub
Err_SecondObitPageOrColumn_Change:
MsgBox "On the Obituary tab, there must be a Second Obituary Publication" & vbCrLf & _
"or note before there is a Second Obituary Column or Page."
SecondPublicationName.SetFocus
Cancel = True
'----------------------
Exit_ThirdObitPageOrColumn_Change:
Exit Sub
Err_ThirdObitPageOrColumn_Change:
MsgBox "On the Obituary tab, there must be a Third Obituary Publication" & vbCrLf & _
"or note before there is a Third Obituary Column or Page."
ThirdPublicationName.SetFocus
Cancel = True
'----------------------
End Sub