Filter on combo box

[Neal]

Hi there
I have a qyery displayed as a datasheet on a main form. There's a combo box
to pick the date on the main form.

How do I filter the subform (query) based on the Date selected in the combo
box?

Main form called frmDateResults with Combo Box name SelectedDate
Sub form name (record source) qryAllResults

I've tried to set the filter on the subform to
qryAllResults.Date=frmDateResults.SelectedDate

Am I barking up the wrong tree? How do I do this?

Thanks, Neal

 

[Al Camp]

Neal,
Try...
qryAllResults.Date = Forms!frmDateResults!SelectedDate

 

[Neal]

Thanks for your reply. Your syntax looks better than mine, but it doesn't
seem to work.

I am wondering if it has something to do with me picking the date from the
combo box (which gets the dates from a query) and it not updating or
re-querying or something like that.

Do I have to add some code to then filter?

Cheers, Neal

 

[Al Camp]

Neal,
Are you using the query design grid, or are you writing SQL manually?
For now, try using the query design grid. You can always grab the
View/SQL SQL statement when you've got it working.

What do you mean by doesn't work? Error message? Nothing happens?
Doesn't filter?

You should rename your table field from Date (qryAllResults.Date) to
something else like PurchaseDate, or DOB, or whatever makes sense. Date is
a reserved word in Access, and represents the current date. It can appear
to work in some instances, but fail in others. (let's use DOB as the new
name)

Next, make sure the combo column that is bound to the SelectedDate is
truly the DOB. Select a DOB in the combo, and the view the form in
datasheet view. Is the DOB really the date you selected?

In query grid design mode, the criteria against DOB would be...
=Forms!frmDateResults!SelectedDate

Give that a go...

 

[Neal]

Thanks for your continued support (-:
By does not work, I mean nothing happens after I select my date from the
combo box. The data in the subform is not filtered, it remains the same.

The main-sub form looks something like this...
_________________________________
| Pick a date 3/3/2006 \/ |
_________________________________
| Round Date Name Time |
| 1 3/3/2006 Bob 34.26 |
| 1 7/3/2006 Neal 32.45 |
_________________________________

Sorry that's so crude, but the top simply has a combo box to pick the date
(the down arrow of the combo box represented by the \/ - the name of this
object is SelectedDate and is unbound). The dates that show in the combo
box come from the table that shows all the dates that a race took place.
After the user picks the date, we only want to see those ones in the sub
part of the form below.

To make this form, I made a blank form, and in design view, dragged the
query on to it. Then added a combo box on the top. The 'filter' I am
trying to get to work, is neither in SQL nor in the query design grid. I
change to the form design view, then click on the top left of the query so
that I get the properties for qryAllResults. On the second line of the
properties is the Filter line. In there I have entered
Forms!frmResults.Date =Forms!frmDateResults!SelectedDate

The data comes via a linked table to a .csv file, so I can't change the
field name from Date, but have changed it in my query to RaceDate.

If there is a better way to do this, I'd be happy to try.

Thanks again for your efforts.

Neal

 

[Neal]

Ahh, I'm getting there! Followed your suggestion, and changed the query to
add the line you suggested in the query grid. It works - as long as I press
F9 after selecting my date. I'll have a look to see if I can now get that
to happen automatically. Somting like
Me!frmDateResults.requery
but that doesn't work.

Thanks, Neal

 

[Al Camp]

Neal,
Because your altering the underlying recordset for the datasheet, a
Requery on the AfterUpdate event of your combo should do it.
If not, try resetting the RecordSource for the form with the
"combo-filtered" SQL statement.