I have an unbound form i will be using to add records to a table. Combo Boxes with SQL Insert code. Works just fine. I am trying to check and prevent from being duplicated and i think i know why this isnt working but not sure? I sure cant seam to fix it to work. I beleive it is not working due to being text i am looking up and its seeking a number. Any assistance would be great.
Thanks
Dave
Code:
Option Compare Database
Option Explicit
Private Sub CmdAddCSZ_Click()
If IsNull(CboCity) Or IsNull(CboState) Or IsNull(CboZipCode) Then
MsgBox "You're missing some data!"
Exit Sub
End If
Dim ZC As String
Dim CC As String
Dim stLinkCriteria As String
Dim ZCkey As String
Dim CCkey As String
ZC = Nz(Me.CboZipCode, "")
CC = Nz(Me.CboCity, "")
ZCkey = Nz(DLookup("ZipCode", "CSZTbl", "ZipCode=" & "'" & ZC & "'"), 0)
CCkey = Nz(DLookup("City", "CSZTbl", "City=" & "'" & CC & "'"), 0)
'Neither exist, add record
If ZCkey = 0 And CCkey = 0 Then
Exit Sub
End If
'both ZC and CC exist but not in same record, Add Record
If ZCkey > 0 And CCkey > 0 And (ZCkey <> CCkey) Then
Exit Sub
End If
'both exist and are in same existing record, Cancel
If ZCkey > 0 And CCkey = CCkey Then
Me.Undo
'Cancel = True
MsgBox "Warning! City of " _
& CC & " and the ZipCode " & ZC & " are already in Database." _
& vbCr & vbCr & "You cannot duplicate record.", _
vbInformation, "Duplicate Information"
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO CSZTbl (ZipCode, Primary, City, State, County) " & _
"VALUES (" & "CboZipCode" & ", " & ChkPrimary & ", " & "CboCity" & ", " & "CboState" & ", " & "CboCounty" & ")"
DoCmd.SetWarnings True
DoCmd.Close