Search within Results of Search

[Seren]

this has got me in a little bit of a tizz and I feel stuck in this same
place. I have been trying to figure out the problem for about 4 days now.

ok, here we go- I have a form (Form2), that the user can enter Job number
and details of the job. If the Job already exists (job numbers are unique),
there is a message box letting the user know it already exists, and then
there is code to take the user to that record... as follows:

Private Sub txtJobNum_BeforeUpdate(Cancel As Integer)
Dim JNum As String
Dim stDup As String
Dim rsc As dao.Recordset

Set rsc = Me.RecordsetClone

JNum = Me.txtJobNum.Value
stDup = "txtJobNum = " & "'" & JNum & "'"

' check if toolnum already exists in tblTool
If DCount("txtJobNum", "tblJob", stDup) > 0 Then
' clear toolNum field
Me.Undo
' set form to existing matching record
rsc.FindFirst stDup
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
DoCmd.Save
End Sub

This works well, and it brings up the tool number in the subform (subTool)
associated with that job number. However, if the Job Number does not exist,
the user can fill in the info and it adds it to the underlying table
(tblTool). Now when there is a need to search for a tool number, that is
when I'm having my problem. Once the tool info is entered, I need to be able
to search the Tool field in the subTool subform. Tool Numbers are unique
also. There can be many jobs to one tool number, so even if the job number
is new, it may correspond to a tool number already in tblTool. I tried the
same technique as above, to no availe... this is what I have now, complete
with commented out lines of code I've tried...

Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
Dim TNum As String
Dim stDup As String
Dim rsc As dao.Recordset

Set rsc = Me.RecordsetClone

TNum = Form_subTool.ToolNum.Value
stDup = "ToolNum = " & "'" & TNum & "'"

' check if toolnum already exists in tblTool
If DCount("ToolNum", "tblTool", stDup) > 0 Then
MsgBox ("corresponding tool number was found")
' clear toolNum field
' Me.Undo
' set form to existing matching record
rsc.FindFirst stDup
' DoCmd.FindRecord (TNum)
' DoCmd.GoToRecord(acTable, "subTool" , acGoTo = ToolNum)
MsgBox ("you were taken to the corresponding tool number")
' Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

I am just trying to get it to bring up the tool number the user entered in
the txtTool field and display all of it's information, then associate that
toolID with the JobNum in tblJob. The Form2 and subTool are associated on
ToolId field...

Any help would be greatly appreciated. my imaginary brick wall is getting
much use on this one!!

Thanks!!