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 CahoonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.