SELECTION in List BOXES

[Compuboy]

My Database keeps track of blood samples and the date they were frozen.

Right now I have a combo box connected to a listbox...so that when you select a date in the combo box all the blood samples frozen on that date appear in the list box. However, I also added a "ALL" entry in the combo box. I want it so that when this is selected all of the blood samples in the database will appear in the list box.

Right now under properties for the listbox I have this for row source:

SELECT [CORD Data Table].[Cord Number], [CORD Data Table].Date FROM [CORD Data Table] WHERE [CORD Data Table].Date=forms![cord data form]!datecombo

How can I modify this statement or even go into the function AfterUpdate for the Combobox so that when I select ALL in the combobox all the values will appear in the list box???

THANKS.

 

[PHV]

In the datecombo_AfterUpdate event procedure you may try something like this:
yourListBoxName.RowSource = "SELECT A.[Cord Number],A.Date FROM [CORD Data Table] A WHERE A.Date=" & Iif(datecombo="ALL", "A.Date", "#" & Format(datecombo, "m/d/yyyy") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

If combo is called cboSelectDate and list coontrol is called lstDates then

cboSelectDate.After_Update code becomes

If cboSelectDate = 'All' Then
lstDates.RowSource = "SELECT [CORD Data Table].[Cord Number], [CORD Data Table].Date FROM [CORD Data Table] "
Else
lstDates.RowSource = "SELECT [Cord Number], [Date] FROM [CORD Data Table] WHERE [Date] = #" & cboSelectDate & "#"
End If




Also - check out the FAQ
faq700-2190 Avoid space characters in any 'Name' - Why ?



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[KenReay]

Hi

or

SELECT [CORD Data Table].[Cord Number], [CORD Data Table].Date FROM [CORD Data Table] WHERE [CORD Data Table].Date=forms![cord data form]!datecombo OR forms![cord data form]!datecombo = 'All'

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[LittleSmudge]

Nice one Ken.

A static RowSource that covers all the options. I've not seen it done that way before, but I'll add it to the resource list now.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Compuboy]

Thanks guys..it works