Hi Access Family,
I have this problem I've been trying to solve. I have this table with a foreign key and a Datefield that we want the user to not duplicate. So I indexed the two fields. one person can have many dates on record, but we don't one them to have multiply records with the same date. your next record must be a new date.
I have this code am playing around with and it sort of works the way I would like for the user. One small problem , I am unable to set another record, the msgbox keeps poping up when I try to create another record even if its unique. More-less am trying to get rid of MS Access generic messaging when there's a duplicate where two fields are Indexed.
Can someone guide me to my mistake here... is it that a string criteria is missing. I have did everything possible with my limited vba skills
Code:
Private Sub txtNextDate_BeforeUpdate(Cancel As Integer) Dim vCount As Integer
If IsNull(Me.txtNextDate) Then
MsgBox " A Date is Required!"
Cancel = True
Exit Sub
End If
vCount = DCount("Inm_id", "tblsalesDates", "txtNextDate")
Debug.Print salesDate, vCount
Select Case Me.NewRecord
Case False
If vCount > 1 Then
MsgBox "Sorry! you cannot add same date." & vbCrLf & _
"A Duplicate Record Cannot Exist.", vbExclamation, "NO Duplicates"
Cancel = True
Me.Undo
End If
End Select
End Sub