Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012

    Question Unanswered: How can i make a combo selection filter my subform? (Access 2007)

    Hi all,

    Firstly, I am a Technical Writer and have a little Access experience but need a little help with a database i am designing for work.

    I am making a database that will link products with documents.

    I have searched many posts and forums but have had no success.

    Database Plan
    I have a Categories combo box (cboCategories), Products combo box (cboProducts) and a documents subform (tblDocumentsSubForm)

    I want to select a category in the Categories combo, have it filter selections for the Products combo box and then the selected Product filter the documents in the subform.

    What I Have Done
    I have already created a Category combo that when selected filters results for the Products Combo that works ok.

    What I Need Help With
    I am not sure how to link the Products combo to the document subform and only show the documents for the selected product.
    Currently all the documents are showing, not just the selected Product's documents.

    I think its something to do with the relationships or some sort of code on the Products combo but I cant figure it out.

    Any help would be appreciated. I have attached the database (Access 2007) and a screenshot of the main form.
    Attached Thumbnails Attached Thumbnails database screenshot.png  
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Cleveland USA


    I have a similar situation, although I have text boxes that are used to filter the records in a subform. I placed a button on the form that will filter based on those values.

    The idea is that the subform's recordsource property is blank. The user enters the two values, then clicks the "Filter" button, which will set the recordsource of the subform to the filtered data we want. The If Len(.... statement is to make sure the values are entered before doing anything. Below, Field1 is numeric and Field2 is a string; for string variables you have to add the quotes.

    Private Sub cmdFilter_Click()
    Dim strSQL As String

    strSQL = "SELECT * FROM qryFormType"

    If Len(Nz(Me.txtField)) = 5 And Len(Nz(Me.txtField2)) > 1 Then
    'add to SQL
    strSQL = strSQL & " WHERE Field1 = " & Me.txtField & " AND Field2= '" & Me.txtField2 & "'"
    MsgBox "Enter values into both boxes."
    End If

    Me!sub1.Form.RecordSource = strSQL

    End Sub

  3. #3
    Join Date
    May 2012

    Figured it out

    I have actually changed my database a bit and done away with the two combos. I figured out how to do what i wanted another way using queries and criteria that uses a combo box for the filtered item.

    With my subform, it was a problem with relationships as i suspected.

Posting Permissions

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