Hi all,
I have a continous form with two combo on it that use an after update SQL Insert to insert into a many to many relationship table.
I am trying to make this code work for a before insert event to check if the two selected values of the combo boxes are all ready in the
table and if they are to canel, msgbox, and exit so not to be able to add the same record. I have spent all day on this code and cannot
get it to work with the combo boxes? I have similar code in for some text boxes on different form which works well so would be really
great if someone could tell me what I am doing wrong.
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim CY As String
Dim CT As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim CYkey As Long
Dim CTkey As Long
Set rsc = Me.RecordsetClone
CY = (Me.CboCompany)
CT = (Me.CboCompanyType)
CYkey = Nz(DLookup("CompanyID", "Company2CompanyTypeTbl", "CompanyID=" & CY), 0)
CTkey = Nz(DLookup("CompanyTypeID", "Company2CompanyTypeTbl", "CompanyTypeID=" & CT), 0)
'both exist and are in same existing record
If CYkey > 0 And CYkey = CTkey Then
Me.Undo
'Cancel = True
MsgBox "Warning! Company Name of " _
& CY & " and the Company Type " & CT & " are already in Database." _
& vbCr & vbCr & "You need to choose a diffeerent Company Type.", _
vbInformation, "Duplicate Information"
Exit Sub
End If
End Sub