Checking for duplicate record

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
 
S

Secret Squirrel

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.
 
A

Allen Browne

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
 
S

Secret Squirrel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top