Open Report using a form

[MoulayKool]

Hi,
I need help creating the following: How to open a Report using a form (the Form contains 1 drop down boxes to choose from).

Form1 contains a button that opens another form (form2) with 1 drop down box. Form2 contains drop down box and a button to open the report. The drop down box contains list of Cities to choose from. If the user wants the report to show only New York City, he/she'll have to choose New City from the drop down box, else if the user wants to show all cities he/she should leave the drop down blank.

if this can be done using vba not Macros...Macros tend to give me a lot of trouble debugging...

Thanks sooo much in advance

Ismail

 

[ragnarok75]

Check out this link. You can download a sample database which I think will help. - I was able to use the code here to do something similar...

http://www.fontstuff.com/access/acctut19.htm

 

[MoulayKool]

Thanks so much.

 

[fneily]

You can create a query, called a union query, to place the words, let's say, ALL_Cities on the dropdown list. Create a new query, then next to the view button (top left), click on the dropdown arrow and select SQL VIEW. You cannot create a union query in Design view. Copy or type the following:

SELECT DISTINCT [City] FROM YourTableName WHERE [City] Is Not Null UNION Select '<All_Cities>' From YourTableName
ORDER BY [City];

Place the name of this query on the ROWSOURCE of your combobox. Then when you open the combo, at the very top you'll see the selection. Now you must code for it.

I did something like this:
If Me![citycombo].Value = "<All_Cities>" Then
holdcnt = "([City] Like " & "'*' Or [City] Is Null)"
Else
holdcnt = "[County] = " & "'" & Me![countycombo] & "'"
End If

Then I used it in a WClause for a report.

 

[MoulayKool]

fneily,
thank you much for your help..
I am stuck..Please bear with me I am not an expert in vba.

1- I created the union query
2- created the form put the drop down box
3- placed the query on the ROWSOURCE of the combobox

all 3 steps are working perfect.

My question is:

Do I put your code in 0n_click of a button that i added in the form. Also, I don't see in your the name of the report that I am trying to open.

If Me![citycombo].Value = "<All_Cities>" Then
holdcnt = "([City] Like " & "'*' Or [City] Is Null)"
Else
holdcnt = "[County] = " & "'" & Me![countycombo] & "'"
End If

 

[fneily]

On the form where they select the city, you'll have a command button. On the OnClick event (design view, click the event tab, click next to OnClick. Click the button with three dots and select Code Builder) you'll put something like the following (The Private Sub and End Sub statements will already be there)

Private Sub Command17_Click()
Dim WClause, RName
Dim holdcnt As String
If Me![citycombo].Value = "<All_Cities>" Then
holdcnt = "([City] Like " & "'*' Or [City] Is Null)"
Else
holdcnt = "[City] = " & "'" & Me![citycombo] & "'"
End If

WClause = holdcnt
RName = "YourReportName"

DoCmd.OpenReport _
ReportName:=RName, _
WhereCondition:=WClause, _
view:=acViewPreview

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[MoulayKool]

Beautiful. Excatly what I am looking for. I learned something new today thanks to you fneily.

ragnarok75,
The site you recommended is great, it looks like I'll be learning a lot using it.

Again thank you both.

Ismail