none
Populate Mainform with Subform detail. RRS feed

  • Question

  • How do i perform a search in the sub form to display data on the main form.

    I have two tables.
    table 1 have the customer address information.
    table 2 have the order details.

    table 2 have the order number. both tables are linked by the customer ID

    the order number is not the primary key neither was it used for the relationship.
    The relationship/primary key is the customer ID number. I have already done a search on the customer ID and it works fine. 

    I am trying to give the user various search options.

    The main form have the customer address and customer ID

    The subform have the order number and detail information.

    Thanks!!!

    This is the code that I am using but it is not working.

    The command button is on the mainform

    Private Sub FindRecord_Click()
    'does record search for existing Order number
    Dim FindID As String
     On Error GoTo Err_Handler
     FindID = InputBox("Enter the Order number you're looking for:")
     With Me.RecordsetClone
      .FindFirst "[Order_NUM]=" & FindID

     If .NoMatch Then
     MsgBox "Sorry, couldn't find that Order Number."
     Else
     Me.Bookmark = .Bookmark
     End If
     End With
    Exit_Here:
     Exit Sub
    Err_Handler:
     Resume Exit_Here
     End Sub


    • Edited by Chibut Tuesday, January 24, 2017 8:26 PM
    Tuesday, January 24, 2017 6:38 PM

Answers

  • You need to understand that a linked subform's recordset is not all rows from the table to which it is bound, but the subset of rows which reference the parent form's current record.  Consequently if you search for an order which is not related to the parent form's current record, the order will not be found.  As I said in my earlier reply, the way to do it is to build a subquery in the code which returns a set of foreign key values where OrderNum matches the search criterion, and search for those rows in the parent form's recordset whose primary key is in this subset of foreign key values.

    If each row in the subform's recordset relates to only one row in the parent form's recordset, then you can navigate to that row by calling the FindFirst method of the parent form's recordset object.  If each row in the subform's can relate to one or more rows in the parent form's recordset, i.e. the subform's table is modelling a many-to-many relationship type, then you need to filter the parent form to those rows whose keys are in the subset of rows returned by the subquery.  My FindRecord demo, to which I referred you earlier, does the latter, though it would also work in the former scenario, where each row in the subform's recordset relates to only one row in the parent form's recordset.  The code in my demo to filter only the parent form is:

        Const MESSAGETEXT = "No matching records found."
        Dim ctrl As Control
        Dim strFilter As String
        
        Set ctrl = Me.ActiveControl
        
        strFilter = "ProjectID IN(SELECT ProjectID " & _
            "FROM ContactProjects WHERE ContactID = " & ctrl & ")"
        
        If Nz(ctrl, 0) = 0 Then
            ' turn off filter
            Me.FilterOn = False
        Else
            If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
                ' filter form to name selected in combo box
                Me.Filter = strFilter
                Me.FilterOn = True
            Else
                ' inform user if no matching records found and show all records
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Me.FilterOn = False
                Me.Requery
            End If
        End If

    Additionally the following code in the parent form's Current event procedure moves the record pointer in the subform to the matching record if the parent form's unbound cboLastname control, in which the search criterion is selected, is not Null:

        ' select contact in subform if combo box is not Null
        If Not IsNull(Me.cboLastname) Then
            With Me.fsubContacts.Form
                .RecordsetClone.FindFirst "ContactID = " & Me.cboLastname
                If Not .RecordsetClone.NoMatch Then
                    .Bookmark = .RecordsetClone.Bookmark
                End If
            End With
        End If

    The code which also filters the subform to the matching record is:

        Const MESSAGETEXT = "No matching records found."
        Dim ctrl As Control
        Dim strFilter As String
        
        Set ctrl = Me.ActiveControl
        
        strFilter = "ProjectID IN(SELECT ProjectID " & _
            "FROM ContactProjects WHERE ContactID = " & ctrl & ")"
        
        If Nz(ctrl, 0) = 0 Then
            ' turn off filter
            Me.FilterOn = False
            Me.fsubContacts.Form.FilterOn = False
        Else
            If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
                ' filter form to name selected in combo box
                Me.Filter = strFilter
                Me.FilterOn = True
                
                ' filter subform to selected contact
                Me.fsubContacts.Form.Filter = "ContactID = " & ctrl
                Me.fsubContacts.Form.FilterOn = True
            Else
                ' inform user if no matching records found and show all records
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Me.FilterOn = False
                Me.Requery
                Me.fsubContacts.Form.FilterOn = False

            End If
        End If

    In this case no code is needed in the parent form's Current event procedure to move the record pointer of course, as the subform will be filtered to only one row.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, January 25, 2017 6:52 PM Clarified.
    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    Wednesday, January 25, 2017 6:48 PM
  • This is the link.

    Click on the protest update form.

    click on find protest. you can get a protest number from the charges table.

    https://1drv.ms/u/s!AuTUAvOR9ddmf_rYQ4xYdZAQlRM

    Thanks!!

    Hi,

    I finally managed to download your file but there was something wrong with your project because I can't compile it.

    In any case, I agree with Ken's assessment but here's the approach I propose you give a shot:

    Private Sub cmdFind_Click()
    '1/25/2017
    'thedbguy@gmail.com
    
    Dim rs As Object
    Dim lngBldgID As Long
    Dim lngProtestNo As Variant
    
    lngProtestNo = InputBox("Enter protest number:")
    
    lngBldgID = Nz(DLookup("Bldg_ID", "Charges", "Protest_Number=" & CDbl(lngProtestNo)), 0)
    
    If lngBldgID = 0 Then
        MsgBox "Bldg ID not found!", vbInformation, "No Match"
    Else
        Set rs = Me.RecordsetClone
        With rs
            .FindFirst "Bldg_ID=" & lngBldgID
            Me.Bookmark = .Bookmark
        End With
        Set rs = Nothing
    End If
    
    End Sub
    

    (untested)

    Hope it helps...

    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    Thursday, January 26, 2017 3:08 AM
  • Morning,

    Thanks for all your help.

    This worked. I am also going to look into ken's approach too.

    Question: Why did you use building ID? I am guessing because everything is linked by building ID on the main form.

    The reason for the compile error is that I deleted some files to be able to make ot a smaller file size


    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    • Edited by Chibut Thursday, January 26, 2017 2:44 PM
    Thursday, January 26, 2017 2:39 PM

All replies

  • Well, the thing is, if the main form does not contain the Order Number, then searching on it won't produce anything. Were you getting an error regarding something like "cannot find" field?
    Tuesday, January 24, 2017 8:28 PM
  • No. I did not get any message.

    How do I make it to be able to search the subform.


    • Edited by Chibut Tuesday, January 24, 2017 8:34 PM
    Tuesday, January 24, 2017 8:33 PM
  • If the code is executed from the main form, i.e. the button is on the main form, then using Me.RecordsetClone will refer to the main form's record source. If you want to use the record source of the subform, then you'll have to either move the code to the subform or make sure you're referring to the subform's record source. For example, maybe something like:

    Me.SubformName.Form.RecorsetClone

    Just a thought...

    Tuesday, January 24, 2017 9:04 PM
  • How do I make it to be able to search the subform.
    Take a look at FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates two routines for searching by a value in a subform, one of which filters the parent form only, the other which also filters the subform to the row with the selected value.  The basic methodology is that the parent form is filtered to those rows whose primary key is in a set of foreign key values returned by a subquery on the subform's underlying table.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, January 24, 2017 10:42 PM Hyperlink added.
    Tuesday, January 24, 2017 10:42 PM
  • I changed it to this but now I get the error message not found.

    I do have the value. Am I missing something in the declaration?

    Dim FindID As String
      On Error GoTo Err_Handler
      FindID = InputBox("Enter the Order number you're looking for:")
    Me![details subform1-update].Form!Order_NUM.SetFocus
     with Me![details subform1-update].Form.RecordsetClone
       .FindFirst "[Order_NUM]=" & FindID

     If .NoMatch Then
      MsgBox "Sorry, couldn't find that Order Number."
      Else
      Me.Bookmark = .Bookmark
      End If
      End With
     Exit_Here:
      Exit Sub
     Err_Handler:
      Resume Exit_Here


    • Edited by Chibut Wednesday, January 25, 2017 3:38 PM
    Wednesday, January 25, 2017 2:45 PM
  • Hi,

    Which line gets highlighted when you go to debug?

    Wednesday, January 25, 2017 3:30 PM
  • How do I do the debugging? I always have problems with it in access.

    Wednesday, January 25, 2017 3:48 PM
  • When you get the error message, do you see a "Debug" button?
    Wednesday, January 25, 2017 4:07 PM
  • The error message that I get is the message in my msgbox

     MsgBox "Sorry, couldn't find that Order Number."

    THE ORDER number Is only on the subform.

    I know that the value is my table.

    I have a feeling that it might have something to do with this statement. The data type is a number.

    Am I suppose to reference it differently?

     .FindFirst "[Order_NUM]=" & FindID


    • Edited by Chibut Wednesday, January 25, 2017 4:18 PM
    Wednesday, January 25, 2017 4:11 PM
  • I see. I would recommend stepping through your code to trace the problem. Do you know how to place a break point in your code? If not, you could try the following:

    1. Modify your code to insert a Stop line as follows.

    ...
    .FindFirst "[Order_NUM]=" & FindID
    Stop
    If .NoMatch Then
    ...

    Once the code hits the Stop line, execution will pause. You can hover your mouse over FindID and see what value it is holding.

    By the way, if you're trying to move the record pointer in the subform, you'll also need to change Me.Bookmark to refer to the subform.

    Hope it helps...

    Wednesday, January 25, 2017 4:24 PM
  • I inserted a break point it did not work.

    does this have something to do because the code is under access class object?

    when I do debug.print order number, the value is blank but this should not be.

    The findID prints the value that is in the input box.

    Wednesday, January 25, 2017 4:35 PM
  • Hi,

    Are you able to share a copy of your db, so we can help you troubleshoot it?

    Wednesday, January 25, 2017 4:40 PM
  • sure. let me work on condensing the data..

    Do I send it to u directly or post a link here?

    • Edited by Chibut Wednesday, January 25, 2017 4:56 PM
    Wednesday, January 25, 2017 4:51 PM
  • This is the link.

    Click on the protest update form.

    click on find protest. you can get a protest number from the charges table.

    https://1drv.ms/u/s!AuTUAvOR9ddmf_rYQ4xYdZAQlRM

    Thanks!!

    Wednesday, January 25, 2017 5:26 PM
  • Hi,

    Thanks! I am having a hard time downloading it right now. Please give me some time to take a look. I'll let you know...

    Wednesday, January 25, 2017 6:13 PM
  • You need to understand that a linked subform's recordset is not all rows from the table to which it is bound, but the subset of rows which reference the parent form's current record.  Consequently if you search for an order which is not related to the parent form's current record, the order will not be found.  As I said in my earlier reply, the way to do it is to build a subquery in the code which returns a set of foreign key values where OrderNum matches the search criterion, and search for those rows in the parent form's recordset whose primary key is in this subset of foreign key values.

    If each row in the subform's recordset relates to only one row in the parent form's recordset, then you can navigate to that row by calling the FindFirst method of the parent form's recordset object.  If each row in the subform's can relate to one or more rows in the parent form's recordset, i.e. the subform's table is modelling a many-to-many relationship type, then you need to filter the parent form to those rows whose keys are in the subset of rows returned by the subquery.  My FindRecord demo, to which I referred you earlier, does the latter, though it would also work in the former scenario, where each row in the subform's recordset relates to only one row in the parent form's recordset.  The code in my demo to filter only the parent form is:

        Const MESSAGETEXT = "No matching records found."
        Dim ctrl As Control
        Dim strFilter As String
        
        Set ctrl = Me.ActiveControl
        
        strFilter = "ProjectID IN(SELECT ProjectID " & _
            "FROM ContactProjects WHERE ContactID = " & ctrl & ")"
        
        If Nz(ctrl, 0) = 0 Then
            ' turn off filter
            Me.FilterOn = False
        Else
            If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
                ' filter form to name selected in combo box
                Me.Filter = strFilter
                Me.FilterOn = True
            Else
                ' inform user if no matching records found and show all records
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Me.FilterOn = False
                Me.Requery
            End If
        End If

    Additionally the following code in the parent form's Current event procedure moves the record pointer in the subform to the matching record if the parent form's unbound cboLastname control, in which the search criterion is selected, is not Null:

        ' select contact in subform if combo box is not Null
        If Not IsNull(Me.cboLastname) Then
            With Me.fsubContacts.Form
                .RecordsetClone.FindFirst "ContactID = " & Me.cboLastname
                If Not .RecordsetClone.NoMatch Then
                    .Bookmark = .RecordsetClone.Bookmark
                End If
            End With
        End If

    The code which also filters the subform to the matching record is:

        Const MESSAGETEXT = "No matching records found."
        Dim ctrl As Control
        Dim strFilter As String
        
        Set ctrl = Me.ActiveControl
        
        strFilter = "ProjectID IN(SELECT ProjectID " & _
            "FROM ContactProjects WHERE ContactID = " & ctrl & ")"
        
        If Nz(ctrl, 0) = 0 Then
            ' turn off filter
            Me.FilterOn = False
            Me.fsubContacts.Form.FilterOn = False
        Else
            If Not IsNull(DLookup("ContactID", "ContactProjects", "ContactID = " & ctrl)) Then
                ' filter form to name selected in combo box
                Me.Filter = strFilter
                Me.FilterOn = True
                
                ' filter subform to selected contact
                Me.fsubContacts.Form.Filter = "ContactID = " & ctrl
                Me.fsubContacts.Form.FilterOn = True
            Else
                ' inform user if no matching records found and show all records
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Me.FilterOn = False
                Me.Requery
                Me.fsubContacts.Form.FilterOn = False

            End If
        End If

    In this case no code is needed in the parent form's Current event procedure to move the record pointer of course, as the subform will be filtered to only one row.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, January 25, 2017 6:52 PM Clarified.
    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    Wednesday, January 25, 2017 6:48 PM
  • This is the link.

    Click on the protest update form.

    click on find protest. you can get a protest number from the charges table.

    https://1drv.ms/u/s!AuTUAvOR9ddmf_rYQ4xYdZAQlRM

    Thanks!!

    Hi,

    I finally managed to download your file but there was something wrong with your project because I can't compile it.

    In any case, I agree with Ken's assessment but here's the approach I propose you give a shot:

    Private Sub cmdFind_Click()
    '1/25/2017
    'thedbguy@gmail.com
    
    Dim rs As Object
    Dim lngBldgID As Long
    Dim lngProtestNo As Variant
    
    lngProtestNo = InputBox("Enter protest number:")
    
    lngBldgID = Nz(DLookup("Bldg_ID", "Charges", "Protest_Number=" & CDbl(lngProtestNo)), 0)
    
    If lngBldgID = 0 Then
        MsgBox "Bldg ID not found!", vbInformation, "No Match"
    Else
        Set rs = Me.RecordsetClone
        With rs
            .FindFirst "Bldg_ID=" & lngBldgID
            Me.Bookmark = .Bookmark
        End With
        Set rs = Nothing
    End If
    
    End Sub
    

    (untested)

    Hope it helps...

    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    Thursday, January 26, 2017 3:08 AM
  • whew ken!!!!

    You just sent me back to school...lol

    I am going to read thru it step by step...

    Questions: Do you have suggestion for a reading material that can help me get better?

    Thanks for all your help with most of my questions!!


    • Edited by Chibut Thursday, January 26, 2017 2:39 PM
    Thursday, January 26, 2017 2:38 PM
  • Morning,

    Thanks for all your help.

    This worked. I am also going to look into ken's approach too.

    Question: Why did you use building ID? I am guessing because everything is linked by building ID on the main form.

    The reason for the compile error is that I deleted some files to be able to make ot a smaller file size


    • Marked as answer by Chibut Thursday, January 26, 2017 2:40 PM
    • Edited by Chibut Thursday, January 26, 2017 2:44 PM
    Thursday, January 26, 2017 2:39 PM
  • Morning,

    Thanks for all your help.

    This worked. I am also going to look into ken's approach too.

    Question: Why did you use building ID? I am guessing because everything is linked by building ID on the main form.

    The reason for the compile error is that I deleted some files to be able to make ot a smaller file size


    Hi,

    Glad to hear you got it to work. I couldn't test it because of the VBA compile problem.

    Yes, I used Bldg ID because it's the link between the form and the subform.

    Ken and I were happy to assist. Good luck with your project.

    Thursday, January 26, 2017 5:09 PM