• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Cross Reference Report

I have an Access 2007 Report that is a list of addresses grouped by zone.  It is numbered using a running total in the report.  Is there a way to create a cross reference that lists the name only sorted alphabetically that also shows the record number as shown in the running total.  This report changes all the time so I can't use a record id or anything like that.
Paul Cahoon
Paul Cahoon
  • 7
  • 3
1 Solution
Helen FeddemaCommented:
It might be best to do the numbering in VBA code, writing the number to a temp table along with the other fields needed on the report.  That way you could reliably retrieve the number associated with each name.  The code that creates the table could a make-table query, or an append query to append to a new temp table, and it would need to be run before opening the report, say from a button on a menu or another form.  You might need to also save the date the report was created to the temp table, at least if you need a record of the report as of a specific time.
Helen FeddemaCommented:
One simple way to get incremented numbers of this sort is to use a temp table with an AutoNumber ID.  Append data to the other fields in the temp table, sorted by name.  
Paul CahoonAuthor Commented:
Can you briefly give me an example of the code that I could start with?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Helen FeddemaCommented:
Here is a procedure that appends data from tblContacts (sorted by company name) to a temp table with a CompanyNo AutoNumber field, and uses it as a report's record source.
Public Sub CreateTempTableForReport()

On Error Resume Next

   Dim strSQL As String
   Dim strTargetTable As String
   Dim strSourceTable As String
   Dim strReport As String
   Dim rpt As Access.Report
   strTargetTable = "tblTempReport"
   strSourceTable = "zstblTempReport"
   'Make fresh copy of temp table
   DoCmd.DeleteObject objecttype:=acTable, _
   DoCmd.CopyObject newname:=strTargetTable, _
      sourceobjecttype:=acTable, _
   'Append sorted data to it from tblContacts
   strSQL = "INSERT INTO tblTempReport ( SSN, FirstName, LastName, " _
      & "Salutation, StreetAddress, City, StateOrProvince, PostalCode, " _
      & "Country, CompanyName, JobTitle, WorkPhone, WorkExtension, " _
      & "MobilePhone, FaxNumber, EmailName, LastMeetingDate, ReferredBy, " _
      & "Notes, NextMeetingDate )" _
      & "SELECT SSN, FirstName, LastName, Salutation, StreetAddress, City, " _
      & "StateOrProvince, PostalCode, Country, CompanyName, JobTitle, " _
      & "WorkPhone, WorkExtension, MobilePhone, FaxNumber, EmailName, " _
      & "LastMeetingDate, ReferredBy, Notes, NextMeetingDate " _
      & "From tblContacts ORDER BY CompanyName;"
   DoCmd.RunSQL strSQL
   'Assign temp table as report's record source
   '(or you can permanently assign it as this report's record source, in
   'which case just open it
   strReport = "rptContactsByCompany"
   DoCmd.OpenReport ReportName:=strReport, _
      View:=acViewDesign, _
   Set rpt = Reports(strReport)
   rpt.RecordSource = strTargetTable
   DoCmd.OpenReport ReportName:=strReport, _
End Sub

Open in new window

Helen FeddemaCommented:
Modify as needed for your object and field names.  
BTW, the reason I make a fresh copy of the temp table, rather than just clearing and refilling it, is that I want the numbering to always start at 1.
Helen FeddemaCommented:
zstblTempReport has an AutoNumber field called CompanyNo, and other fields from tblContacts that are needed for the report.
Helen FeddemaCommented:
Here is a screen shot of the very simple report:
Paul CahoonAuthor Commented:
That is just what I needed!  I modified the object names and set up the report just the way I wanted it.  How can I suppress the dialog box asking me if I want to append records to the table?

Also, every time I close the report, it asks me if I want to save the design.  Any suggestions on why that would be happening even though I haven't done anything except open it?
Helen FeddemaCommented:
To suppress the warnings, add a line like this to the code (before running the SQL statement)
DoCmd.SetWarnings False
To avoid the message about saving the report (you did change something -- the record source), just save it permanently with tblTempReport as its record source, and delete the part of the code that opens the report in Design view and sets that property.  If need be, make a copy of the report for this purpose.
Paul CahoonAuthor Commented:
That is exactly what I needed.  Thanks for your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now