Checking for duplicate record

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I have a form & subform to track my employee training. The main form shows
the employee and the subform shows the training records in a continuous form.
The fields on the subform are:

Material
Size Range
Tolerance
Trained - Yes/No Checkbox

Each subform has 4 records for each employee but only one of those records
can have a "true" value in the checkbox. The other 3 have to be a "False"
value. How can I set it up where if a user checks one of the checkboxes in
one of these 4 records to set it to "true" it won't allow it if one of the
other 3 checkboxes are already set to "true"? Basically only allowing 1 of
these 4 records to have a "true" value in the checkbox.

Any help is greatly appreciated.

SS
 
I forgot to mention one thing...

I can't set the index in the table to "No Duplicates" because there could be
other employees that have identical records in my table. I'm just confused on
how to do this.
 
Use the BeforeUpdate event of the form to test if there is another record
marked true.

The example below assumes that:
a) your subform is linked to the main form on the EmployeeID field;
b) your subform's table has a primary key field named ID.

If the Trained box is checked, it uses DLookup() to see if there is any
other record for this employee where the Trained box is checked. There are 3
parts to the criteria string:
a) same EmployeeID as in the main form;
b) Trained is True;
c) ID is different (i.e. don't compare this record itself.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
dim varResult As Variant
If Me.Trained.Value Then
strWhere = "(EmployeeID = " & Nz(Me.Parent![EmployeeID], 0) & _
") AND (Trained = True) AND (ID <> " & Nz(Me.ID, 0) & ")"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Uncheck the other record and try again."
'Me.Undo
end If
End If
End Sub
 
Perfect! Thank you very much Allen!

Allen Browne said:
Use the BeforeUpdate event of the form to test if there is another record
marked true.

The example below assumes that:
a) your subform is linked to the main form on the EmployeeID field;
b) your subform's table has a primary key field named ID.

If the Trained box is checked, it uses DLookup() to see if there is any
other record for this employee where the Trained box is checked. There are 3
parts to the criteria string:
a) same EmployeeID as in the main form;
b) Trained is True;
c) ID is different (i.e. don't compare this record itself.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
dim varResult As Variant
If Me.Trained.Value Then
strWhere = "(EmployeeID = " & Nz(Me.Parent![EmployeeID], 0) & _
") AND (Trained = True) AND (ID <> " & Nz(Me.ID, 0) & ")"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Uncheck the other record and try again."
'Me.Undo
end If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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


Secret Squirrel said:
I have a form & subform to track my employee training. The main form shows
the employee and the subform shows the training records in a continuous
form.
The fields on the subform are:

Material
Size Range
Tolerance
Trained - Yes/No Checkbox

Each subform has 4 records for each employee but only one of those records
can have a "true" value in the checkbox. The other 3 have to be a "False"
value. How can I set it up where if a user checks one of the checkboxes in
one of these 4 records to set it to "true" it won't allow it if one of the
other 3 checkboxes are already set to "true"? Basically only allowing 1 of
these 4 records to have a "true" value in the checkbox.

Any help is greatly appreciated.

SS
Click to expand...
Click to expand...
 
Back
Top