stoping duplicate entries of clients

J

Jackson

I'm having a problem where a user is entering information into a datasheet
view (so he can enter information faster)and on occasion the user is
entering duplicate information for the same person id. My table looks like
this,

Pickup table
PickupId - Primary key
Client ID
Date
Location
Total Pounds


Is there anyway that I can prevent the user from entering duplicate clientid
for that particular date and location? So that when the user enters the
information in the datasheet view he will be prompted.

TIA,

Jackson
 
A

Allen Browne

If you want to totally and permanently prevent duplicates:

1. Open the table in design view.

2. Open the Indexes box (toolbar.)

3. On the first line of the dialog, enter an index name in the first column,
and the first field of the index in the second column. On the subsequent
lines, leave the index name blank, and just enter the field names. It will
look like this:
ClientIdDateLocation ClientID
Date
Location

4. Select the first row in the dialog again (the one with the index name),
and in the lower pane, set the Unique property to Yes.

5. Save.


If you just want to warn the user of the possible duplicate and allow them
to override the warning, use the BeforeUpdate event procedure of the *form*.
Cancel the event to prevent the save. It will be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If ((Me.[Client ID] = Me.[Client ID].OldValue) And _
(Me.[Date] = Me.[Date].OldValue) And _
(Me.Location = Me.Location.OldValue)) Or _
IsNull(Me.[Client ID]) Or IsNull(Me.[Date]) Or _
IsNull(Me.Location) Then
'do nothing
Else
strWhere = "([Client ID] = " & Me.[Client ID] & ") AND ([Date] = " &
_
Format(Me.[Date], "\#mm\/dd\/yyyy\#") & _
") AND (Me.Location = """ & Me.Location & """)"
varResult = DLookup("PickupId", "Pickup", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of ID " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Duplicate") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

BTW, "Date" is not a good name for a field. It is a reserved word in VBA
(for the system date), so there are contexts where it is ambiguous and
yields unreliable results.
 
J

Jackson

Thanks for all of the Advice Allen,

I decided to go with the first option that you suggested but first I have to
clean up the duplicates first.

Thanks again,

Jackson


Allen Browne said:
If you want to totally and permanently prevent duplicates:

1. Open the table in design view.

2. Open the Indexes box (toolbar.)

3. On the first line of the dialog, enter an index name in the first
column, and the first field of the index in the second column. On the
subsequent lines, leave the index name blank, and just enter the field
names. It will look like this:
ClientIdDateLocation ClientID
Date
Location

4. Select the first row in the dialog again (the one with the index name),
and in the lower pane, set the Unique property to Yes.

5. Save.


If you just want to warn the user of the possible duplicate and allow them
to override the warning, use the BeforeUpdate event procedure of the
*form*. Cancel the event to prevent the save. It will be something like
this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If ((Me.[Client ID] = Me.[Client ID].OldValue) And _
(Me.[Date] = Me.[Date].OldValue) And _
(Me.Location = Me.Location.OldValue)) Or _
IsNull(Me.[Client ID]) Or IsNull(Me.[Date]) Or _
IsNull(Me.Location) Then
'do nothing
Else
strWhere = "([Client ID] = " & Me.[Client ID] & ") AND ([Date] = "
& _
Format(Me.[Date], "\#mm\/dd\/yyyy\#") & _
") AND (Me.Location = """ & Me.Location & """)"
varResult = DLookup("PickupId", "Pickup", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of ID " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Duplicate") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

BTW, "Date" is not a good name for a field. It is a reserved word in VBA
(for the system date), so there are contexts where it is ambiguous and
yields unreliable results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jackson said:
I'm having a problem where a user is entering information into a
datasheet view (so he can enter information faster)and on occasion the
user is entering duplicate information for the same person id. My table
looks like this,

Pickup table
PickupId - Primary key
Client ID
Date
Location
Total Pounds


Is there anyway that I can prevent the user from entering duplicate
clientid for that particular date and location? So that when the user
enters the information in the datasheet view he will be prompted.
Click to expand...
Click to expand...
 
A

Allen Browne

Yes, the engine-level solution is better than the form-level one.

If you want suggestions on how to identify the duplicates, see:
Finding Duplicates in an imported DBF
at:
http://allenbrowne.com/xbase-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jackson said:
Thanks for all of the Advice Allen,

I decided to go with the first option that you suggested but first I have
to clean up the duplicates first.

Thanks again,

Jackson


Allen Browne said:
If you want to totally and permanently prevent duplicates:

1. Open the table in design view.

2. Open the Indexes box (toolbar.)

3. On the first line of the dialog, enter an index name in the first
column, and the first field of the index in the second column. On the
subsequent lines, leave the index name blank, and just enter the field
names. It will look like this:
ClientIdDateLocation ClientID
Date
Location

4. Select the first row in the dialog again (the one with the index
name), and in the lower pane, set the Unique property to Yes.

5. Save.


If you just want to warn the user of the possible duplicate and allow
them to override the warning, use the BeforeUpdate event procedure of the
*form*. Cancel the event to prevent the save. It will be something like
this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If ((Me.[Client ID] = Me.[Client ID].OldValue) And _
(Me.[Date] = Me.[Date].OldValue) And _
(Me.Location = Me.Location.OldValue)) Or _
IsNull(Me.[Client ID]) Or IsNull(Me.[Date]) Or _
IsNull(Me.Location) Then
'do nothing
Else
strWhere = "([Client ID] = " & Me.[Client ID] & ") AND ([Date] = "
& _
Format(Me.[Date], "\#mm\/dd\/yyyy\#") & _
") AND (Me.Location = """ & Me.Location & """)"
varResult = DLookup("PickupId", "Pickup", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of ID " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Duplicate") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

BTW, "Date" is not a good name for a field. It is a reserved word in VBA
(for the system date), so there are contexts where it is ambiguous and
yields unreliable results.

Jackson said:
I'm having a problem where a user is entering information into a
datasheet view (so he can enter information faster)and on occasion the
user is entering duplicate information for the same person id. My table
looks like this,

Pickup table
PickupId - Primary key
Client ID
Date
Location
Total Pounds


Is there anyway that I can prevent the user from entering duplicate
clientid for that particular date and location? So that when the user
enters the information in the datasheet view he will be prompted
Click to expand...
Click to expand...
Click to expand...
 
Top