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?

Answer 2

It sounds like you have two different recordsets here. User details show when a user is selected. When no user is selected, you want to see all users. If that is the case, use two subforms and switch between them - we'll call them fAllUsers and fUserDetails. Lets call the subform control name sfUser. I am assuming that the the primary key on the user form and a foreign key on fUserDetails is userID. Make sure the subform does not have any fields linked with the parent.
In the parent:

Private Sub Form_Current()
    Dim sForm as string
    Dim sFilter as string
    Dim lUserID as long
    lUserID =nz(me!userID,0)
    sForm="fUserDetails"
    sFilter="userID=" & lUserID
    if lUserID<>0 then 
        sForm="fAllUsers"
        sFilter=""
    end if
    with sfUser
        .sourceobject=sForm
        .filter=sFilter
        .filteron=true
    end with
End Sub

Hope that helps.

Answer 3

The main form should have no recordsource, and there should be no link criteria.

The after update events of your filter controls should update the filter for the subform (as CodeSlave's code does it), you can assign the recordsource of the subform (I tend to prefer the latter, as then I don't have to worry about all the issues that come with filters).

I have done this many times in the past, but usually didn't use a subform. I am not fond of datasheets and use continuous forms, and put the filtering controls in the form's header. You then avoid the difficulties of referring to the subform control (though that's pretty easy as you can use a With/End With block, as seen in Praesagus's post).

The key point is that you want the main form UNBOUND and you want the controls where you put in criteria to trigger the filtering of the subform.