Problems with Report

[Stanley Mok]

I am new to Access and encountered problems with reporting. I have 2 queries:

query1:

SELECT c.loc_name, s.site_name, COUNT(*) AS site_count
FROM City c, Site s
WHERE c.city_id = s.city_id
GROUP BY c.loc_name, s.site_name
ORDER BY c.loc_name, s.site_name

query 2:

SELECT DISTINCT city_anem FROM query1 ORDER BY 1;

There is a report CityReport using query1 as the recordset.

Both queries and the report works fine when open from the database window.

In order to separate the report by city, the following is added:

Module 1:

Public CurrentCity As String

Sub CityReport2()
Dim cities As DAO.Recordset
Dim filstr As String
Set cities = CurrentDb.OpenRecordset("City")
Do While Not cities.EOF
CurrentCity = cities("loc_name")
filstr = "loc_name='" & CurrentCity & "'"
DoCmd.OpenReport "CityReport", acViewNormal, "query 2", filstr,
acHidden
parks.MoveNext
Loop
End Sub

and in the report open event:

Private Sub Report_Open(Cancel As Integer)
Me.Caption = CurrentCity
End Sub

When I run CityReport2, sometimes there is a prompt for the values of
c.city_id and s.city_id.
If the prompt does not happen, the reports prints without the filtering and
the report
caption updates every two open. That is:

City 1
default caption
City 3
default caption
....

I verified the report is closed every time by putting some code in the
report close event.
I also tried using OpenArgs instead of global variable but it is null in the
open event.

My problems are:

1. Query prompts comes up.
2. Report filter doesn't work.
3. Report open event is not triggered every time.
4. OpenArgs not passed to report.

The version is Access 2002. I have do a lot of VB and ADO programming with
SQL Server but have not use Access before. Any help or information to better
understand the subject very nuch appreciated.