Comob Box List Options

[BrianFawcett]

I have a from where users pick from a list in a Combo Box which then generates a report.

The user chooses either orders received before the monthly financial close or received after.

The combo box is setup with a ROW SOURCE of BEFORE CLOSE;AFTER CLOSE.

These are the values in the CLOSE STATUS field from the data table.

How do I make a third choice that takes all records regardless of their close status?

 

[JamesMcS]

You could just type in the three values into the row source and put an if or select case in the open report event:

Rowsource = 1;"BEFORE CLOSE";2;"AFTER CLOSE";3;"ALL"
Row Source Type = Value List
Bound Column = 1

Then in your open report put something like

select case comboname.column(0).value
 
Case 1
 
...
 
Case 2
 
...
 
Case 3
 
...
 
End Select

 

[boblarson]

Take the criteria off of the query and then open the form like this:

Dim strWhere As String
 
Select Case Me.ComboBoxNameHere
 
Case "Before Close", "After Close"
    strWhere = "[Close Status]= " & Chr(34) & Me.ComboBoxNameHere & Chr(34)
Case Else
    strWhere = ""
End Select
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWhere

And that way it will open based on the combo if one is selected but if none, or another option, is selected then it will return all records.

 

[JamesMcS]

Or that way, being 100 times better

 

[BrianFawcett]

Bob, Thanks for you response. This code would go on the Run Report click button, correct?

 

[boblarson]

This code would go on the Run Report click button, correct?

That is correct.

 

[BrianFawcett]

Thanks, I will give it a try and let you know how it works.

 

[BrianFawcett]

Bob, One more question, what does the Chr(34) do in the code?

 

[boblarson]

it is a double quote. I use Chr(34) where some people use a single quote. I have found I have fewer problems with using the double quotes instead of singles, especially when there might be a single quote in the data. But you can use

"Something = '" & Me.Something & "'"

or

"Something = " & """ & Me.Something & """

or what I use

"Something = " & Chr(34) & Me.Something & Chr(34)


it's your choice; they all work the same essentially. The one with the single quotes is what a lot of people use but you have to know that there won't be a single quote in the data - like business names - Bill's Tavern, Sam's Place, etc.