Results 1 to 8 of 8
  1. #1
    shiwawa is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    4

    Main Form ComboBox to Filter Records in SubForm

    I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing something as this looks quite simple.



    I have attached a screenshot to help.

    What I need to for the records to be filtered by the Section Field using either of the three options in the ComboBox. The ComboBox looksup data from a Sections table (SectionID, Section).

    Main Table Name: Orders (Master Link: Order ID)
    SubForm Table Name: Order Details (Child Link: Order ID)
    ComboBox Object Name: CboFilter

    I will appreciate any help.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you included the CboFilter ComboBox in the RecordSource query for the SubForm as a Criteria?

  3. #3
    shiwawa is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    4
    I used an AfterUpdate Procedure shown below:

    Code:
    Private Sub CboFilter_AfterUpdate()
       On Error GoTo Err_cboFilter_AfterUpdate
       ' Purpose: Change the form's RecordSource to only products from this supplier.
       Dim strSQL As String
       Dim bWasFilterOn As Boolean
       ' Save the FilterOn state. (It's lost during RecordSource change.)
       bWasFilterOn = Me.FilterOn
       ' Change the RecordSource.
       If IsNull(Me.CboFilter) Then
          ' If the combo is Null, use the whole table as the RecordSource.
          Me.RecordSource = "Orders"
       Else
          strSQL = "SELECT DISTINCTROW Orders.* FROM Orders " & _
                   "INNER JOIN [Order Details] ON " & _
                   "[Orders].[Order ID] = [Order Details].[Order ID] " & _
                   "WHERE [Order Details].[Section] = " & Me.CboFilter & ";"
          Me.RecordSource = strSQL
       End If
       ' Apply the filter again, if it was on.
       If bWasFilterOn And Not Me.FilterOn Then
          Me.FilterOn = True
       End If
       
    Exit_cboFilter_AfterUpdate:
       Exit Sub
       
    Err_cboFilter_AfterUpdate:
       MsgBox Err.Number & " : " & Err.Description, vbInformation, _
              Me.Module.Name & ".cboFilter_AfterUpdate"
       Resume Exit_cboFilter_AfterUpdate
    End Sub

  4. #4
    shiwawa is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    4
    I think I didn't answer your question rightly. If I understand your question, I used an SQL query in the Combo Box row source to generate the drop down list of the Combo. The SQL query is shown below:

    SELECT DISTINCT [Order Details].Section, Sections.Section
    FROM Sections INNER JOIN [Order Details] ON Sections.SectionID = [Order Details].Section
    ORDER BY Sections.Section;

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To what is the Bound Column of your ComboBox set?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks like your code should work. What is it doing?

  7. #7
    shiwawa is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    4
    To answer your first question, I am using an unbound object for the ComboBox.

    Second Question:

    I am prompted to enter a Parameter value for Orders.OrderID
    and then another prompt for Order Details.OrderID.

    I then get a run-time error '3071' message.

    Upon debug, it looks like the error is on this line:

    Me.RecordSource = sSQL

    Not sure what is worng.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sorry, I should have been a little clearer in my question. The Data tab of the property sheet for the ComboBox has both ControlSource and BoundColumn properties listed. An unbound control has no ControlSource but a ComboBox still has a BoundColumn. It is what is returned when you ask for the .Value property (the default). The fact that it is asking for parameters indicates that something is not right. Maybe something not spelled correctly. You are also trying to set the RecordSource of the SubForm, right? Me.RecordSource = sSQL is for the MainForm. This link should help with the syntax.

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

Similar Threads

  1. Filter Form records with Combo Box????
    By jgelpi in forum Forms
    Replies: 0
    Last Post: 05-19-2009, 07:05 AM
  2. Replies: 0
    Last Post: 08-17-2008, 12:19 PM
  3. Exporting main table AND subform
    By jenny28 in forum Import/Export Data
    Replies: 0
    Last Post: 05-10-2007, 11:40 AM
  4. Combobox synchronization in a subform
    By pe_z in forum Forms
    Replies: 0
    Last Post: 02-26-2006, 01:48 PM
  5. Subform vs main form calculation
    By fadone in forum Forms
    Replies: 17
    Last Post: 12-21-2005, 07:27 AM

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