Combo parameter question 2

[HenryAnthony]

Hi,

I have a report based on a query that uses a combo box on a form as a criteria parameter. The parameter is:

Like nz([forms]![frmRptCatDate]![combo],"*")

I learned about Nz doing a search on this great forum, thanks everyone.

If no selection from the combo box is made, all records print. If a selection is made, the records are filtered. My question is, after a selection is made, how can I then select all records again?

Best regards,

Henry

 

[dhookom]

You can run some code to set the combo box value to Null. I generally add "All Records" to the combo box row source using a union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HenryAnthony]

Duane,

Where does this "All Records" value come from. I assume you store it in it's own table. Or am I missing something.

 

[PHV]

combo.RowSource = "SELECT boundfield, descriptivefield FROM yourTable UNION SELECT '*', '___ Select ALL ___' FROM yourTable"

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

 

[HenryAnthony]

This works.

combo.RowSource = "SELECT boundfield, descriptivefield FROM yourTable UNION SELECT '*', '___ Select ALL ___' FROM yourTable"

Instead of an asterisk "*" at the top of the list is there a way to add something more descriptive.

Best regards,

Henry

 

[dhookom]

Most of us hide the boundfield since it is usually a primary key field and isn't exposed to the user. The only column that can be seen is the descriptivefield.

You could change the SQL to:
combo.RowSource = "SELECT boundfield, boundfield & '-' & descriptivefield FROM yourTable UNION SELECT '*', '___ Select ALL ___' FROM yourTable"

Then set the Column Widths property to: 0";2"



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HenryAnthony]

OK you guys, works great now. Thank you both for all your help.

Henry