How to filter datasheet, make it display records based on drop down selection.

So far in my experience with MS Access I have only used unbound controls and now I am trying to make use of a subform in datasheet view, which is bound to columns in a table, and I am confused about how this is supposed to work.

I see that as soon as I type values into the datasheet they are immediately saved in the table, and I don’t have to write code to save it. That’s fine and it is working. But I don’t understand how, e.g. when I open the main form, to make the subform datasheet display only the records that are related to the main form. My datasheet simply displays everything in the table that it is tied to. I assume that I need to apply a filter to the datasheet and I have tried to do that but it’s not working. The F1 help for Filter is:

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

I have tried to modify this to fit my needs but it’s not working.

I need to have the subform datasheet only display records where the CaseNumber (a field in the table) = txtCaseNumber (field on my main form). The way my database application works is that you select from a drop down list what Case you want to open. Say you select Case “ABC”; the app pulls all data related to Case ABC into the appropriate unbound controls on the main form by use of recordsets. But now I am trying to incorporate this bound subform into the main form and I want only those records that contain the selected case number to show up.

The table that my subform is bound to looks like this:

UniqueID  SUSPECT  CASENUMBER  VICTIM
1             Me           abc                  You  

Can you please explain to me how I should go about achieving the desired result?

Thank you very much!
JA67
Start Free Trial