Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    30

    Unanswered: search within a search result

    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 a bunch!!
    Seren

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    can you post it?

  3. #3
    Join Date
    May 2006
    Posts
    30
    what exactly do you need?

  4. #4
    Join Date
    May 2006
    Posts
    30
    Ok, actually, I guess I should update this. Still having problems, but somewhat different (maybe even making progress?) I got the form for that ends up being the subform to work by itself. When I try to connect it, I can enter a tool number in the subform, it tells me it found a corresponding record, then it throws an error and it doesn't like the Me.Bookmark = rsc.Bookmark line. I get a "Run-Time Error '3021'- No current record"(this works when the subform is all by itself).

    any new ideas?
    Last edited by serendee; 05-04-06 at 14:31.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    do you have relationships setup? Sounds like you are trying to create a child record without a corresponding parent record.

    reading your original post makes it sound like a parent/child relationship is not appropiate for your case.

  6. #6
    Join Date
    May 2006
    Posts
    30
    Yep, I have relationships set up.

    But, I should update. I finally got a little Q&A time with my "mentor" (I'm an intern!). I had to add the linking field to the main form. It would perform the search, but then had no way to link it to that particular job. I had tried this, but backwards. I had added the linking field to the subform as an invisible field.

    So, I was close, just had my reasoning a little backwards. That's good and bad. "Yay, I almost got it!!" "Boo, I was *so close*! I wish I could have gotten it myself!"

    Thanks for your help, though!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •