Subform only shows single record

Question

I have a subform(Users subform) embedded in a form(users). What I am trying to achieve is on load, the subform should show ALL users in the table and the controls on the main form to act as a filter.
i.e.
if user A is selected from a combobox in the main form, only User A's details show in the subform
if the selection (ie userA) is cleared from the combobox in the main form, ALL records will popup again in the subform.

This sound very simple but for starters, when I load my form, only 1 record is showing in the subform, If i make a selection in the user combobox, then that user's records show ONLY. It defeats the purpose of having a subform if all I can see is one record it a time with or without making a selection.

I am using Access '03. Can anyone help with this. I have a sample database here that has managed to achieve this but I cant seem to find a difference in their settings compared to mine.

Answer 1

I presume that you have the sub form set up as either a "Continuous Form" or a "Data Sheet".

Does your filter still have something in it when your form opens? You might want to explicitly clear it when the master form opens and then refresh the data.

Edit
Here's something I've done before:

Private Sub cmdCannedFilter_Click()
On Error GoTo Err_Click    
Dim strFilter As String
    strFilter = "(1 = 1)"
    strFilter = strFilter & " " & _
            "and (((someTable.Active)=Yes) "   

    Me![List_SubForm].Form.FilterOn = False
    Me![List_SubForm].Form.Filter = strFilter
    Me![List_SubForm].Form.FilterOn = True

Exit_Click:
    Exit Sub

Err_Click:
    MsgBox Err.Description
    Resume Exit_Click
End Sub

In this case, this is running from a button on the master form, and List_SubForm is (obviously) the subform. I think the key thing for me was having to turn the filter off and then on again. I don't remember why.

The (1 = 1) is because I did something similar somewhere else where I was building up the query on they fly and it meant I didn't have to decide each time if I needed to plug an and in there or not.

Are you doing something similar?