Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find record in subform (Access 2000 - all updates)

    I have a frmVendors, based on tblVendors. On frmVendors, there is a subform called fsubContacts, based on tblContacts. The frmVendors and fsubContacts are linked by VendorID. The relationship is One-to-Many.

    What I am trying to do is create a method by which the user can find and move to a specific record on a subform.

    For example...
    Say there are 50 Vendors, and each Vendor has up to 5 Contacts.
    The user wishes to find Contact "Susan Smith" but can't remember which Vendor Susan Smith works for.

    What is the method to use to find and move to the Susan Smith record?

    Thanks.

    Tom

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: find record in subform (Access 2000 - all updates)

    The easiest way is an unbound combobox containing all contacts. Include a hidden column for the VendorID. When user selects a record from combobox, you can call-up that vendor's record. Then, use findfirst method of the recordsetclone property of the subform to move to the specific contact.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: find record in subform (Access 2000 - all updates)

    Also see <post:=381,240>post 381,240</post:>.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find record in subform (Access 2000 - all upda

    Mark
    Thanks for the reply. What you suggested works as far as taking me to the correct Vendor.

    Here is the SQL behind the combo box that does that...
    SELECT tblVendors.VendorID, tblContacts.ContactID, tblContacts.ContactLast, tblContacts.ContactFirst
    FROM tblVendors INNER JOIN tblContacts ON tblVendors.VendorID = tblContacts.VendorID;

    However, I cannot get to the proper record in the subform. Here is the code on the AfterUpdate event for the combo box...
    Dim rs2 As Object
    Dim strTemp2 As String

    Set rs2 = Me.Recordset.Clone
    rs2.FindFirst "[VendorID] = " & Str(Me![Combo23])
    Me.Bookmark = rs2.Bookmark
    strTemp2 = Me.Combo23.Column(1)
    MsgBox "You have selected " & strTemp2
    Me.fsubContacts.SetFocus
    rs2.FindFirst "[ContactID] = " & strTemp2

    The code errors out on the last line, giving Error 3070 ([ContactID] not recognized as a valid field name or expression)

    The reason I put the "strTemp2 = Me.Combo23.Column(1)" line in the code was to make sure I was pulling the correct column, and the message box advises properly.

    I have tried various ways to refer to the [ContactID] field, such as referencing the main form and subform, but that doesn't seem to make any difference.

    I must be going wrong somewhere.

    Tom

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: find record in subform (Access 2000 - all upda

    The last line should search in the recordset of the subform, not in the recordset of the main form:

    Dim rs3 As Object
    Set rs3 = Me.fsubContacts.Form.Recordset.Clone
    rs3.FindFirst "[ContactID] = " & strTemp2
    Me.fsubContacts.Form.Bookmark = rs3.Bookmark

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find record in subform (Access 2000 - all upda

    Hans
    Thanks. Just after my last reply, your post came in. I downloaded the code, modified it to suit my situation, and I have it working perfectly.

    Thanks again.

    Tom

Posting Permissions

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