Dropdown box/filter?

[cthai]

Hello -

I have a report that generates case information using the Case Number - right now the reports open all cases... filter by the case number - we have a total of 5 hundreds cases... and when the report is generated there is about 300 pages. is there away we can have the user to select a case number from a dropdown box? and just show the case information according to the case number they selected from the report?

I try to create a combo box but it still doesnt allow the user to select the number, then i try the list box - but this just show a list of case numbers.


I'm starting to get confuse LOL sorry if this doesnt make sense...thank you in advance!

 

[cthai]

sorry i forgot to mention that i am using an sql to drive the case number

SELECT OpenItems1.casenumber FROM OpenItems1;

 

[randy700]

You mean something like this?

"SELECT casenumber 
FROM OpenItems1
WHERE casenumber = " & Me.ComboboxName

Randy

 

[cthai]

"SELECT casenumber 
FROM OpenItems1
WHERE casenumber = " & Me.RouteTo

Hi Randy

When I add the code above i get an error message:

Case Management [invalid SQL statement; expectted 'DELETE', INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'.]

 

[TheAceMan1]

How are ya cthai . . .

Required Info:
[ol][li]What is the [blue]Record Source[/blue] of the report?
[ol a][li]If its a queryname postback the SQL.[/li]
[li]If its a tablename postback the fields used in the report[/li][/ol][/li]
[li]Wether Query or SQL, identify primarykeys and their data types.[/li]
[li]Whats the data type of [blue]casenumber[/blue]?[/li]
[li]If more than one table is involved post any relationships[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chunter33]

In regards to the SQL Randy gave you, you need to remove the quotation mark next to the word SELECT.

Like so:

SELECT casenumber FROM OpenItems1WHERE casenumber = " & Me.ComboboxName

 

[Gammachaser]

Assuming you use a command button called cmdPrint to launch the report and the ComboBox is on the same form as the Command Button, use this:

Private Sub cmdPrint_OnClick
DoCmd.OpenReport "[i]rptMyReport[/i]",acViewPreview,"Casenumber = " & [i]cboMyComboBox[/i]

As long as Casenumber is part of your RecordSource, you will get only those records related to that Casenumber. If you want the report to go straight to the default printer substitute acViewNormal for acViewPreview.

The above example assumes that Casenumber is numeric. If Casenumber is text you would use:

DoCmd.OpenReport "[i]rptMyReport[/i]",acViewPreview,"Casenumber = '" & [i]cboMyComboBox[/i] & "'"