duplicate records

[Guest]

i am entering patients names and thier travel dates .one patient may travel
many times.for each visit of a patient a have to make an entry. i need a code
to prompt me in case of entering same patient name for the same travel date
..currently i am using qry to find any duplicate records enterd.( my field
names : patient,travel)

 

[Rick B]

Do you want to allow duplicate name/dates? If not, go to your table and
build a compound index of the two fields and don't allow duplicates.

 

[Guest]

Do you want to allow duplicate name/dates? If not, go to your table and
build a compound index of the two fields and don't allow duplicates.

thanks mr rick ..
i tried this but i didnt get it . any more clarification please.
thanks alot

 

[Rick B]

While in design-view for your table. Click the VIEW menu and select INDEXES.

Add a new Index Name.

In the field name, put the first field.

On the next row, put the second field name BUT DO NOT PUT ANOTHER INDEX
NAME. This will create a compound index. Se the index's UNIQUE property to
"Yes".

Hope that helps.

 

[Guest]

You should certainly do as Rick recommends and index the table to prevent
this, but in addition to that you can also intercept any duplicates at
control level in the data entry form. As its possible for either control to
have its data entered first you'll need to do it for both by entering some
code in the BeforeUpdate event procedure of each. For the Patient control:

Dim strMessage as string, strCriteria As String

strMessage = "Duplicated patient/travel date."

If Not IsNull(Me.Travel) Then
strCriteria = Travel = #" & Format(Me.Travel,"mm/dd/yyyy") & "# And " &_
"Patient = """ & Me.patient & """"
If Not IsNull(DLookup("Patient", "YourTable", strCriteria))
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

For the Travel control:

Dim strMessage as string, strCriteria As String

strMessage = "Duplicated patient/travel date."

If Not IsNull(Me.Patient) Then
strCriteria = Travel = #" & Format(Me.Travel,"mm/dd/yyyy") & "# And " &_
"Patient = """ & Me.patient & """"
If Not IsNull(DLookup("Patient", "YourTable", strCriteria))
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

The duplication of code here could be avoided by putting it in a single
function called as each control's BeforeUpdate event property, but it will
work just the same as two separate event procedures.

This should work OK in a single user database, but in a multi-user
environment on a network two users could theoretically be entering the same
new Patient and Travel values simultaneously, in which case the code would
not necessarily pick up the duplication. The violation of the index would be
picked up, however, and a data error triggered for whichever user tries to
save their record last. This would generate a default system error message,
but you could handle the error more elegantly and inform the user more
specifically of the reason in the form's Error event procedure if you wished.