duplicate record procedure

[vjb]

I have a form that captures info on a case BUT if the case (record) already exits and the user wants to add services I need to add a P to the end of the case # - which is the primary key - and paste append. How do I bring up a custom message box when there is a record present that asks if they want to add services to this record instead of the canned Access message about duplicate values etc. If yes, copy certain fields of this record and paste append to the table with case #+P, open a form to capture service info. If no, erase the fields on the form and close the message box.
Thanks for you help!

 

[dtq2003]

Duplicate Procedure

Function Information_ValidateCaseNum()
On Error GoTo Information_ValidateCaseNum_Err

' Attached to the Information form.
' Attached to the BeforeUpdate event of the CaseNum field.
If (Eval("DLookUp(""[CaseNum]"",""[Client]"",""[CaseNum] = Form.[CaseNum] "") Is Not Null")) Then
' If the value of CaseNum not unique, display a message.
MsgBox "The CaseNum you entered already exists. Enter a unique Case.", vbInformation, "Duplicate CaseNum"
' Return to the CaseNum control.
DoCmd.CancelEvent
End If


Information_ValidateCaseNum_Exit:
Exit Function

Information_ValidateCaseNum_Err:
MsgBox Error$
Resume Information_ValidateCaseNum_Exit

End Function

 

[vjb]

Thank you so much!

 

[vjb]

more VBA help needed please...

I need to combine the above code with other code I have. This is the code. When I compile the db the first line is highlighted and the error message is "Compile Error - Expected End of Sub". Any ideas?

Private Sub Command398_Click()
Function Information_ValidateCaseNum()
On Error GoTo Information_ValidateCaseNum_Err

' Attached to the Information form.
' Attached to the BeforeUpdate event of the CaseNum field.
If (Eval("DLookUp(""[CaseID]"",""[Demo]"",""[CaseID] = Form.[CaseID] "") Is Not Null")) Then
' If the value of CaseNum not unique, display a message.
MsgBox "The CaseNum you entered already exists. Enter a unique Case.", vbInformation, "Duplicate CaseNum"
' Return to the CaseNum control.
DoCmd.CancelEvent
Else
On Error GoTo Err_Command398_Click
Me![C-STADATCU] = Forms![NewCasePage1]![c-stadatcux]
Me![REFSRC] = Forms![NewCasePage1]![REFSRC]
If IsNull(Me!SUBREF) Then
MsgBox "Please enter a valid date in Date Submitted", vbOKOnly, "Date Validation"
Me![SUBREFx].SetFocus
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Case has been saved", 0, "Case Status"
End If


Exit_Command398_Click:
Exit Function

Err_Command398_Click:
MsgBox Err.Description
Resume Exit_Command398_Click

End Function

End If


Information_ValidateCaseNum_Exit:
Exit Function

Information_ValidateCaseNum_Err:
MsgBox Error$
Resume Information_ValidateCaseNum_Exit

End Function
End Sub