Access Child/Master form, datasheet looks filtered

Master/Child links *should* display your datasheet filtered according to whatever record is displayed in the main form, and filtering/selecting records is generally done from the main form, not the subform.

That said, if you have Access 2007 or higher, try setting your subform/data sheet's Filter On Load propertty to "No".

Are you sure you have the master/child relationships setup properly?

You don't indicate what version of Access you are using, but this works just fine for me.

What is the SQL for the datasource for your main form?
What is the SQL for the datasource for your datasheet form?

Thanks for your responses.

@mbizup: I want to use the subform as a list of records, that will drive the parent form as well. The reason for this (possibly awkward) setup is that there are other subforms that will be driven by the parent form (but are based on a different, related table.)
Filter On Load is already set to "No". It doesn't look like a regular filter, either. I can't tell it to stop filtering - the datasheet says it only has 1 record.

@fyed: The datasources are both the same table, and the master/child IDs are the same as well.

I could use code to navigate the parent form when the subform is navigated. If I do that, I'll remove the link child/master fields.

That's actually how I've been doing it until now, but the code was starting to get clumsy, so I figured I'd see if there was a better solution.

A more common approach to what you are doing is to have a filter form with a listbox (for example) at the bottom and unboud textboxes, combos, etc for fields that the user can filter on.  Code behind an "Apply Filter" button would drive what is seen in the listbox, and the double-click event of the listbox used to open your form(s) with main and subform filtered accordingly.

If you are using the subform solely for navigation, I would advise using going to your Master form in design view, making sure Control Wizards are on, and adding a listbox. When the wizard starts, choose the option "Find a record on my form based on the value I select . . .". The wizard will walk you through choosing the fields you will show and add the proper code/macro to handle the navigation.

HTH,
pT72

I don't think a listbox will work, as there are many fields and many records, and I would want better searching/sorting/filtering capabilities.

I think I will remove all code, and start from scratch. Hopefully I can get the kinks out like that.

Another approach is to display your datasheet independently (not as a subform) - removing whatever criteria are restricting the records, and using the click event of one or more of the fields on the form to open and filter your mainform/subform:


Docmd.OpenForm "MainFormName",,,"ID = " & Me.[Child Link Field Name]

Huh?

>> Huh?

Ok - the idea is to use your subform/data sheet as a stand-alone filter form.  That will give you all of Access's built-in filtering and sorting options, right?

Then when the user selects a record by clicking on some prominent field in that record, use the click event of that field to open your original form (the mainform/subform combination), filtered to whatever record ID the user selected using the code in my last comment.

Similar to what you wanted to do using the subform - but using a seperate datasheet as a filter instead.

Sounds interesting, but I don't think it'll be practical for my use. Users will want to switch which record they are looking at often, and won't want to have to go to a new form.

What's the best way (via VBA) to navigate a form?

This is what I've been using - is there a better way?

    With Forms!myform.Recordset
        If .EOF Then Exit Sub
        .FindFirst "ID= " & Me.ID
        If .NoMatch Then MsgBox Me.ID & "  not found in parent form"
    End With

Select allOpen in new window

@fyed: So what that system is created for is not what I'd doing with it. Aha!

Thanks!

@Etch0,

correct, subforms are generally used for viewing one-to-many relationships.

Company/Employees
States/Cities
Sales/Sales details

Depending on what version of Access you are using, you could consider using a split form, or you might consider creating a listbox which displays some basic info on each record on the main form.  Put the list down the left side of the screen, then use the right side of the screen to display the entire record.

You could use the forms navigation buttons to navigate through the records, or use the click event of the listbox to move to the appropriate record.

Private sub yourList_Click

    With me.recordsetclone
        .findfirst  "[ID] = " & me.yourList
        me.bookmark = .bookmark
    end with

End sub

Adding the following code would ensure that if you use the forms navigation buttons to move between records, the item selected in the listbox would match the record displayed on the form.


Private Sub Form_Current

     me.yourList = me.ID

End Sub