Results 1 to 5 of 5
  1. #1
    Paok is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    4

    Searching in a Subform and opening Parent record in the Main form

    Hey all, after searching for quite a while I couldn't find a solution for my specific situation - I've got an Access DB where I have a main form that includes the general information about a client, and a subform that contains all contact details (e.g. phones and addresses of that client's branches) of that specific customer. (distributed in 2 tables that are related - Clients and ContactInformation) Now on my database I have a "quick search" form that you can either enter the customer id or customer name and it will open in the main form (it searches in a query that also contains the records that appear in the subform and in another table).
    Now what i'm trying to achieve is adding to that search is an option that will make it possible to enter for example a specific contact address (which is part of the information in the subform) and make it open that customer in the main form, on the record where that specific contact address is part of the subform.


    the current search form works currently like this:
    Code:
    Private Sub btnSearch_Click()
        Dim strCriteria As String
          strCriteria = "[CustomerID]='" & Replace(Me.txtSearchBox, "'", "''") & _
        "' OR [CustomerName]='" & Replace(Me.txtSearchBox, "'", "''") & "'"
        If DCount("*", "qrySearch", strCriteria) > 0 Then
            DoCmd.OpenForm "frmCustomersViewer", acNormal, , strCriteria
        Else
            MsgBox "No results!"
        End If
    End Sub
    Thanks in advance!

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    As long as the contact address data is included in the query, you can search the query for it the same as any other.

    But because the address data is not part of the main form, you cannot use it in the criteria to open the form. What you can do is this:

    - Include the address in the search criteria as you did for the other two
    - Instead of DCount, use DLookup with the Nz function to return a CustomerID that matches, or 0 if none is found
    - Open the form using the customer ID as the criteria

    I'll let you modify the initial strCriteria string to include the address, then do something like this:

    Code:
    Private Sub btnSearch_Click()
        Dim strCriteria As String, MatchID as Integer
    '
    ' Modify this part to include address
    '
          strCriteria = "[CustomerID]='" & Replace(Me.txtSearchBox, "'", "''") & _
        "' OR [CustomerName]='" & Replace(Me.txtSearchBox, "'", "''") & "'"
        MatchID = nz(DLookup("[CustomerID]", "qrySearch", strCriteria),0)
        If MatchID > 0 Then
              strCriteria = "[CustomerID] = " & MatchID
              DoCmd.OpenForm "frmCustomersViewer", acNormal, , strCriteria
        Else
              MsgBox "No results!"
        End If
    End Sub
    This assumes you cannot have a CustomerID = 0!

    If more than one record in the query matches the criteria, you will get the first one it finds.

    HTH

    John
    Last edited by John_G; 07-23-2014 at 12:01 PM. Reason: additional info

  3. #3
    Paok is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2014
    Posts
    4
    John_G Thank you very much! that worked great!

  4. #4
    squeezelit is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    2
    With this code, it opens the first one it finds. How about if I need it to open all records of customers that matches the same address?

    Thanks!

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You would have to modify it so that strCriteria contained only the address; you would not need the DLookup.

    You have to be careful when using an address in the criteria. To humans, 123 Maple, 123 Maple St. and 123 Maple Street are all the same address, but to MS Access they are different. You might want to generalize the criteria a bit for more flexibility, for example with the instr() function:

    instr(address,"123 Maple") > 0

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  2. Searching by field on subform on main form
    By helen21112010 in forum Forms
    Replies: 1
    Last Post: 10-16-2012, 07:55 AM
  3. Replies: 4
    Last Post: 05-17-2011, 06:56 AM
  4. Replies: 1
    Last Post: 12-22-2010, 01:28 AM
  5. opening a second form from main/subform
    By PJPCVP in forum Database Design
    Replies: 1
    Last Post: 10-29-2010, 09:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums