Updating a query criteria from a listbox

[Chris Gorham]

Hi,

I've written a query that is dependent on a date selected from a listbox
the result from the listbox is linked to the querie as follows:
[forms]![Main Menu]![Date_Select]

This works great when I clickthe button that loads the subform that uses the
results from this querie...

However when I select a different date in the listbox and click again the
new date is not being passed down to the querie.

I've tried everything I know and it just doesn't want to work...

The listbox is linked to a table containing all the dates to be displayed -
if that makes any difference.

Any help appreciated....Chris

 

[Chris Gorham]

got this sorted...should have used a combobox...it works great...!!

 

[Tom Wickerath]

Hi Chris,

You command button code currently loads the subform? Try requerying the
subform. Something like this:

Me.NameOfSubformControl.Form.Requery

where NameOfSubformControl is the name of the control that contains the
subform, which may, or may not, be the same name as the subform itself.

You can also just try reassigning the recordsource for the subform, like this:

Me.NameOfSubformControl.Form.RecordSource = NameOfYourQuery

I actually use this second technique, with the subform's recordsouce set to
a query that initially returns zero records, such as:

SELECT * FROM TableName WHERE 1=0

So, I think you could use this as an initial recordsource, in lieu of using
your click event code to load the subform. You might also want to cause your
subform to be requeried as a part of the list box After_Update event
procedure. (I assume that your list box is currently set with the Multi
Select property = None, so that you are only allowed to select one date at a
time). If you'd like to look at an example that uses VBA code to iterate the
..Itemselected property for a listbox configured in multiselect mode, check
out this sample:

http://home.comcast.net/~tutorme2/samples/Chap08QBF.zip

In this case, the Category to Include list box would be roughly analogous to
your current list box.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________