I am trying to develop a custom message when a user enters a duplicate lesson number(not primary key). However, in this case, duplicate entries are ok as long as the user first verifies the information between the two records.
I have the following code that works perfectly but I want to be able to display additional information for the user to make the yes or no decision to add the duplicate lesson number.
What I would like to add to the message box is more information about the lesson that is already in the database. Namely the lesson title. So I would like the message box to say something like:
The Lesson Number 'E(E)1234' is already assigned to 'Lesson Title'. Do you still want to use it?
If 'Lesson Title' is inactive, click yes to proceed otherwise click no to cancel and enter another lesson number.
Code:
Private Sub LNumber_BeforeUpdate (Cancel As Integer)
strMsg = " Lesson Number already in use!!! " & _
vbCrLf & vbCrLf & " Lesson Number " & Me.LNumber & " is already in use. Do You still want to use it?" & _
vbCrLf & vbCrLf & " If the Lesson currently assigned the Lesson Number " & Me.LNumber & " is inactive " & _
vbCrLf & " click 'Yes' to proceed otherwise click 'No' to cancel and enter another Lesson Number. "
If Not IsNull(Me.LNumber) Then
If DLookup("LNumber", "tblLCData", "[LNumber] = '" & Me.LNumber & "'") > 0 Then
If MsgBox(strMsg, vbQuestion + vbYesNo, "Duplicate Lesson Number!!!") = vbNo Then
Cancel = True
Else
Cancel = False
End If
End If
End If
End Sub
Thanks in advance for the assistance.
Sean