FindFirst not working on second sub form

[Afrosheen via AccessMonster.com]

Good morning;
I have two identical subforms set on different tab controls that are
programed the same. The only difference is one is for day shift and the other
is for night shift.

I use this routine to see if there is a duplicate StaffId. Again, I use this
on both subforms. The problem is that on the first subform it works. On the
second subform it doesn't work. The record sets are the same for both. I'm
also using a filter for the difference between day shift and night shift.

This is the code:
20 With RecordsetClone
30 .FindFirst "StaffId = '" & [StaffId] & "'"
40 If .NoMatch Then
50 Exit Sub
60 Else
70 Select Case MsgBox("Sorry, This is a duplicate Staff Id Number,"
_
& vbCrLf & "" _
& vbCrLf & "Which means they already exist.. Try
again?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1,
"Warning Warning")

Case vbYes
80 Cancel = True
90 Me.Undo
100 Case vbNo
110 Cancel = True
'Me.Undo
120 If Me.Dirty Then Me.Undo
130 DoCmd.GoToRecord , , acLast
140 End Select
150 End If
160 End With

Is there something I could look for to correct this problem?
Thanks for your time and help.

 

[Daryl S]

Afrosheen -

Where do you run this code from, and how do you ensure the RecordsetClone is
using the correct recordset (e.g. the one for the second tab)?

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me Daryl,
I have a field called [staffid]. it is on both sub forms.

I have a two tab form. Tab 1 would be for days, tab 2 would be for nights.
The record set is set in the OnCurrent in the subforms.
I don't know how to check the recordset. I do know that when I pull up the
day shift all those that work on days are there. When I click on the tab 2
the night shift shows up.

Sorry, working with record sets I'm not very good at. I do kinda understand
them.

What I'm trying to do is to check and make sure there are no duplicate
staffid when a new record is entered.

I could use the dlookup() function and it would work, but I thought the
findfirst would work more efficiently

 

[Daryl S]

Afrosheen -

When I ask where the code is, I mean what event causes it to execute? Is
this code in the OnCurrent event of the main form? Is it in the Click event
of a button? When do you expect the code to check for a duplicate staff id?

Also, have you tried debugging the code on the second subform to see if it
is executing? Put a breakpoint on the "With RecordsetClone" line and see if
it executes as expected. If it doesn't execute, then you may have it in an
incorrect event.

Then post back with the results, including the subroutine name where this
code is.