If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems with Report
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 a 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. |
Thread Tools | |
Display Modes | |
|
|