Composite primary key

[tothemax]

I have a table with a composite primary key, name and date. This does not allow a double entry of a person with the same date. However, I would like to, when someone enters a duplicate date, not accept that date and give a message that they already have that date reserved. Any ideas on how to do this coding. I am drawing a blank. Thanks for any help you may be able to give me.

 

[Elysium]

Couldn't you trap this by looking at the error message logged for primary key violation? And since you know that this error has occured, you could then tell the user? Or am I missing your point?

 

[kjv1611]

Try this:

cmbMyCombo_BeforeUpdate(Cancel As Integer)
If DCount("[Audit ID Number]", "tblMyTable", "[Audit ID Number] = Forms![frmMyFormName]!txtAuditID") Then
   MsgBox ("This account has already been audited this month.  Choose another account to audit.")
   Cancel = True
   End If
End Sub

I have this working perfectly in my database. The txtAuditID is a hidden text box that stores the combined values for the audit id.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

OOps, I left off the "Private Sub" part - kind of important.

Here is same with correction:

Private Sub cmbMyCombo_BeforeUpdate(Cancel As Integer)
If DCount("[Audit ID Number]", "tblMyTable", "[Audit ID Number] = Forms![frmMyFormName]!txtAuditID") Then
   MsgBox ("This account has already been audited this month.  Choose another account to audit.")
   Cancel = True
   End If
End Sub

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV