Solved

How to change record source in a report via vba

Posted on 2004-08-31
5
368 Views
Last Modified: 2009-12-16
I have the following question.

I have a report that is suposed to run once for a series of 5 vendors.  Right now I have 5 reports; one for each of the 5 vendors.  Each report has a query as its record source.

I have   rpt_vendor1,   rpt_vendor2,   rpt_vendor3, rpt_vendor4,  and   rpt_vendor5  as my report names.

The query names are:

qry_vendor1
qry_vendor2
qry_vendor3
qry_vendor4
qry_vendor5


What I would like to do is open a report in design view using a Docmd.  After the report is open, I want to be able to change the record source and run the report for each of the 5 vendors, in a loop if possible

I can open the report, but cannot figure out how to change the record source, print the report, change the record source for the next vendor and print the report, etc.

I tried a few things using me! but could not get anything to work.

Please help

Berm



0
Comment
Question by:berm100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11948601
Pass in each queryname as part of the openreport statement eg.

  for i = 1 to 5

   myQueryName="qry_vendor" & i
   docmd.OpenReport "reportname",,,,,myQueryName

  next

Then interrogate the OpenArgs property within the report's Open event:

  if me.openargs & "" <> "" then me.recordsource=me.openargs





0
 
LVL 39

Expert Comment

by:stevbe
ID: 11951672
You could call each in sucession passing the query name in the open args (like rockmuncher suggested), the code here will open the report in previewe mode and wait until the user closes it before previewing the next one

Call OpenReportAndWait("rptVendor", strArgs)


I would use slightly different test for the report open event

Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs & vbNullString) > 0 Then
        Me.Record1Source = Me.OpenArgs
    End If
End Sub

Private Sub OpenReportAndWait(ReportName As String, OpenArgs As String)
    Dim lngLoop As Long
    Dim blnClosed As Boolean
   
    DoCmd.OpenReport ReportName:=ReportName, _
                     View:=acViewPreview, _
                     OpenArgs:=WhereCondition
   
    'loop until user closes the report
    Do
        'only check every 1000 loops
        If lngLoop Mod 1000 Then
            'let foreground process execute
            DoEvents
            'is the report still open?
            If Reports.Count > 0 Then
                blnClosed = False
            Else
                blnClosed = True
                Exit Do
            End If
            lngLoop = 0
        End If
        lngLoop = lngLoop + 1
    Loop
End Sub

Steve
0
 

Author Comment

by:berm100
ID: 11953292
Rockmuncher,

When you interrogate, I have a few questions:

You say to use the code:

if me.openargs & ""<>"" then me.recordsource=me.openargs

I tried this a variety of ways and it did not work.  What does ""<>"" mean?  Was I supposed to use this exact syntax or am I not understanding something.  Also, should this statement come below next like in your example or above next or does it not matter.

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11953665
rockmuncher was saying that you need to add code to your report's Open event and both our code examples do the same thing ...

mine
Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs & vbNullString) > 0 Then
        Me.Record1Source = Me.OpenArgs
    End If
End Sub

his
Private Sub Report_Open(Cancel As Integer)
    If Me.OpenArgs & "" <> "" Then Me.RecordSource = Me.OpenArgs
End Sub

Steve
0
 
LVL 7

Accepted Solution

by:
rockmuncher earned 125 total points
ID: 11958056
Sorry about the delay.  Decided I needed some sleep!

>> What does   somevariable & ""<>""   mean?  <<
  it's a useful way of looking for empty strings AND null values at the same time.  Essentially the left side of the statement converts any null value to an empty string value.  A string comparison can then take place (eg <>"").  So it literally means if the variable is not null AND is not an empty string.

Looking at steve's snippet above you can see he does EXACTLY the same thing using vbNullString, which is a visual basic constant that contains a null string (ie. "").  Steve checks the length of the resultant string, rather than comparing it to an empty string.  If it contains any characters then he takes action.



>> I tried this a variety of ways and it did not work.  <<
Did steve's suggestion help?  If not, do you get an error message that might help us to work out what's going wrong?
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question