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.
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.