Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Setting report recordsource

Posted on 2006-04-20
32
Medium Priority
?
636 Views
Last Modified: 2011-09-20

Hello EE,

   I have a search form that finds machines in the database. At any point the user might want to print a report with the machines found and data about each machine. The sql from the search form needs to become the record source of the report. The report's recordsource is a query called qry_UnitFinderReport. I have the following code:

Private Sub CommandPrintReport_Click()  
    DoCmd.Echo False, ""
    DoCmd.RunCommand acCmdAdvancedFilterSort
    SendKeys "qry_UnitFinderReport{enter}y", False
    DoCmd.RunCommand acCmdSaveAsQuery
    DoCmd.Close , ""
    DoCmd.Echo True, ""
    DoCmd.OpenReport "rptUnitFinder", acPreview, "", ""
End Sub

The problem is that on the computer I made the form and report on, the record source of the report is set up properly with the dialog box to overwrite the old record source being confirmed with the "y" in "SendKeys "qry_UnitFinderReport{enter}y", False"

On other computers where I open the search form and click the report button, a dialog box asking to save the query as "Query1" pops up - instead of saying "qry_UnitFinderReport" and then automatically selecting enter and then "yes". Because of this, the users are confused. Is there anything wrong with the code above? How can this issue be fixed?

Cheers,
Max.

0
Comment
Question by:maxy88
  • 19
  • 13
32 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16500461
All you need to do is use the OpenReport command with the proper arguments (see below)

Private Sub CommandPrintReport_Click()  
        DoCmd.OpenReport "rptUnitFinder", acPreview, , strSearch*
End Sub

*where strSearch is the variable that is holding your  search string at the time the print report button is clicked.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16500528
If in fact your search string is more like a filter overriding your record source, rather than a where clause.  Use the syntax this way.

Private Sub CommandPrintReport_Click()  
        DoCmd.OpenReport "rptUnitFinder", acPreview, strSearch*
End Sub

*here strSearch is used in the filter argument ( note one less comma)  as opposed to the where argument.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16502577

I tried both variatons and it did not work.

DoCmd.OpenReport "rptUnitFinder", acPreview, Me.RecordSource

and

DoCmd.OpenReport "rptUnitFinder", acPreview, , "WHERE blah blah"

The way I was doing it worked except that I was getting the annoying confirmation box which wouldn't go away.
Did I do anything wrong with the above? Any other ideas?

Max.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16503259
Max,
I was telling you to store your sql string in a variable called strSearch (vs saving as a query), and reference that variable (as is) in the argument syntaxes I gave you. Don't embellish it in a "where blah blah" or me.recordSource. And don't forget to dim your variable.

Like this:
DoCmd.OpenReport "rptUnitFinder", acPreview, strSearch

or this:
DoCmd.OpenReport "rptUnitFinder", acPreview, ,strSearch

if none of the above work, please post the sql string you are trying to save as a query.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16504687
Max,
Forgot to answer your ?<<<<Did I do anything wrong with the above? Any other ideas?>>>>
In giving you the syntax above, I am trying to give you the easiest, most efficient way to do it.
However, yourmethod might work if you left out the double quotes as shown:

change this:
        DoCmd.OpenReport "rptUnitFinder", acPreview, "", ""
to this:
         DoCmd.OpenReport "rptUnitFinder", acPreview


0
 
LVL 6

Author Comment

by:maxy88
ID: 16508087

puppydogbuddy,

   I tried again and it does not work. According to the definition of OpenReport the third parameter is the filterName.

filterName: A filter that restricts the report's records. You can enter the name of either an existing query or a filter that was saved as a query. However, the query must include all the fields in the report you are opening or have its OutputAllFields property set to Yes.

In this case I need an existing filter or query. I am not looking for a filter. The form I have is a search form as mentioned so the record source changes all the time, based on what items were found. I need to pass this new record source to the report. Because of this in my code above I am trying to save the current record source into the qry_UnitFinderReport query and override it. This query is an existing query and similar to the one you named strSearch. It can be used by passing it through like you said or just by overriding it since it is the record source of the report. Works either way. The problem is overriding the existing query which is what I'm trying to do without the hassle. The code above I'm trying to get to work keeps poping up the dialog box and asks for a confirmation. This dialog box is confirmed by the "{enter}y" parameter string but sometimes it does not work. Hence my problem.

Private Sub CommandPrintReport_Click()  
    DoCmd.Echo False, ""
    DoCmd.RunCommand acCmdAdvancedFilterSort
    SendKeys "qry_UnitFinderReport{enter}y", False
    DoCmd.RunCommand acCmdSaveAsQuery
    DoCmd.Close , ""
    DoCmd.Echo True, ""
    DoCmd.OpenReport "rptUnitFinder", acPreview, "", ""
End Sub

"qry_UnitFinderReport{enter}y" is the culprit here and I don't know why it's behaving like this. Why does it give me a message box with "Query1" as the name of the query to save when this clearly says name it "qry_UnitFinderReport" and override it as well?

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16508475
Max,
You did not post the sql of your query as requested, but I think you might be getting the message because the save command doesn' a name associated to it.  
  try this: DoCmd.RunCommand acCmdSaveAsQuery "qry_UnitFinderReport"
 
another idea:
 try to dynamically change the recordSource (assumes sql string saved in variable strSearch)
              Report!rptUnitFinder.RecordSource = strSearch
               DoCmd.OpenReport "rptUnitFinder", acPreview

 
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16509224
Max,
I found your answer. see this link!

           http://www.tkwickenden.clara.net/codeex/96-99.htm
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16509308
As shown in the code sample above, this is the line you were missing:

              DoCmd.Rename strQueryName, acQuery, "query1"
0
 
LVL 6

Author Comment

by:maxy88
ID: 16509842

Hmmm.... there could be cases, since there are many users, that for some people the default query will be saved as query2 or query3 so renaming query1 to what we want won't work because it should be query2 or query3 that needs to be renamed. Is there a way to get the name of the query that was just saved?

Max.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16509880

Like personA might experiment with the database and save a query as query1 for another purpose, then when the report is generated by personB a msg box will pop up warning them about overriding, etc. This is the same problem I'm trying to avoind with the other code. It would be nice to dismiss this msg box behind the scenes. Need something that would say "Y" or {enter} and then show the report. Any ideas?

Max.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16510072

The funny thing is that SendKeys does not send anything to the computer screen on the other computers.... it only works on the computer I use to develop the form. What's going on? Maybe that's why I'm having all these problems.... Like the original code works fine on the development computer, but nothing works on the other ones - not even the SendKeys or any of the sample code you provided. I'm not sure what's going on. Any ideas? This is really strange.

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16511356
Max,
SendKeys is known to be unreliable because it goes to whatever window is opened when you send it, and you don't always have control over that.  That is why I am trying to steer you to a solution that does not use SendKeys.  Try the following, but please understand that it is "air code" that we can refine depending on what happens :

Private Sub CommandPrintReport_Click()

DimstrQueryName As String

 'Turn off screen refresh
  Application.Echo False
  'Open the filter design window
  DoCmd.RunCommand acCmdAdvancedFilterSort
  'Apply the selected filter
  DoCmd.RunCommand acCmdApplyFilterSort
 'Save revised filter as a query
  DoCmd.RunCommand acCmdSaveAsQuery
 'rename query to query that is record source for report
  DoCmd.Rename strQueryName, acQuery, "qry_UnitFinderReport"
  DoCmd.Close "qry_UnitFinderReport"
 'Turn the screen refresh back on
  Application.Echo True
  'Run report
  DoCmd.OpenReport "rptUnitFinder", acPreview
 
 Exit Sub
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16511398
The command "DoCmd.RunCommand acCmdApplyFilterSort" eliminates the need for sendkeys, so I believe we are making progress.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16511865

I see, ok, in that case I agree but what is "strQueryName", how would you know which query to rename to the right one? If we can figure this out I think we are done!

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16512198
Max,
Since I can't see the code executing at your end, I can't tell what "DoCmd.RunCommand acCmdSaveAsQuery" is saving to.  I was trying to set the code up to save to a variable named strQueryName, so that I could just rename the variable to the name of the query used as the record source for your report.  Let me know what is happening at your end.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16529039

puppydogbuddy,

I tried your code and I got the following error message: "The command or action 'SaveAsQuery' isn't available now.
Any ideas?

Also, the logic behind the code you provided is that it saves the query as "Query1" and then it renames the query to the record source query. Finally it opens the report which will use the new modified query. Do you think this is a good approach to the problem. I mean what if there is already a query called "Query1" that someone created to experiment with. The code would then try to rename the wrong query and thus the report will open with nothing to show. Any thoughts?

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16530165
Maxcy,
Try the code below.  The problem is that strQueryName was never declared as a parameter to the sub. To make it clearer, I broke your code down into 2 sub procedures.  The first sub contains the code for generating the query that  will be used as the source query for the report.  Notice that the CreateQuery Sub proc includes a declared  parameter called strQueryName as a variable that will be output from the procedure, more specifically, output from the line  DoCmd.RunCommand acCmdSaveAsQuery.  Once it has output to strQueryName, it can be renamed to the name that is bound to the report recordsource.    

Sub CreateQuery(strQueryName As String)   '<<<<<note that strQueryName declared as parameter that will be used to create the query.

 'Turn off screen refresh
  Application.Echo False
  'Open the filter design window
  DoCmd.RunCommand acCmdAdvancedFilterSort
  'Apply the selected filter
  DoCmd.RunCommand acCmdApplyFilterSort
 'Save revised filter as a query
  DoCmd.RunCommand acCmdSaveAsQuery
 'rename query to query that is bound to record source for report
  DoCmd.Rename strQueryName, acQuery, "qry_UnitFinderReport"
  DoCmd.Close "qry_UnitFinderReport"
 'Turn the screen refresh back on
  Application.Echo True
 End Sub

Private Sub CommandPrintReport_Click()
  'Run report using query bound to report recordsource
  DoCmd.OpenReport "rptUnitFinder", acPreview
 Exit Sub
0
 
LVL 6

Author Comment

by:maxy88
ID: 16530508

puppydogbuddy,

  If you are thinking that I ran your sample code above without setting strQueryName, then you are wrong on this one :) I replaced it with a string containing the correct name. There is no need of a strQueryName variable and two subroutines when you can just call the code where you want it....

The error I reported above comes from this call, which I am surprised that you did not comment on. It seems to me that it works fine for you:

DoCmd.RunCommand acCmdApplyFilterSort

Anyways, the whole idea about your approach is to save the query as some name and then rename it to something else. The line:

DoCmd.Rename strQueryName, acQuery, "qry_UnitFinderReport"

does not provide anything for me here because qry_UnitFinderReport is the query that I need so renaming qry_UnitFinderReport to something else is of no use. The Rename parameters are [new query], ..., [old query]. qry_UnitFinderReport is what I want. I think what you meat was:

DoCmd.Rename "qry_UnitFinderReport", acQuery, "query1"

but this goes back to the previous discussion and question that you haven't answered. It could be that someone has already created a query and named it query1. In this case the code:

DoCmd.RunCommand acCmdSaveAsQuery

would save the new query as query2 and so the above line would rename the wrong query, query1, and this won't work. Your approach is different but it won't work in my case. The code I'm trying to use above, was suggested in another thread:

http://www.experts-exchange.com/Databases/MS_Access/Q_20271313.html?query=print+access+report+of+all+records+selected+in+current+form&clearTAFilter=true

It's just that sometimes it works and othertimes it does not. I need someone to tell me why this is happening....

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16531205
Max,
And where does the "old query" come from? It either:
1. was loaded from a saved query, or
2. was built on the fly in the current procedure and passed to a parameter(variable)and then saved.

Since you did not load a query from file, I assume #2 is applicable in your case.  In order to ouput an unsaved query to a parameter, the parameter must be declared. That s why I declared the parameter strQueryName.....to receive the query string. You are correct that I had "New Query"and "Old Query" reversed in the rename.  Also, take out the applyFilter statement.  It didn't work and is not needed because we did not define a filter.











The statement   DoCmd.RunCommand acCmdSaveAsQuery won't save
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16531734
Forgot the following:

syntax of rename statement:
DoCmd.Rename "qry_UnitFinderReport", acQuery, strQueryName

Passing the query string that you generated, on the fly, to a parameter is how you get around using the name "query1"  for" old query".  In order to pass parameter, the parameter must be declared in the sub. The code can be one procedure....I broke it into 2 to try and illustrate to you how the strQueryName fit in.

If you want all the code in the PrintReport_Click procedure, you still need to declare the parameter as shown below:
   Private Sub CommandPrintReport_Click(strQueryName)
0
 
LVL 6

Author Comment

by:maxy88
ID: 16533801

puppydogbuddy,

It happens like this: The search form loads up with a query called qry_searchQuery as the default record source. When the user types something to search for on the form, the query result is filtered further by making another query:

Dim sql As String
sql = "SELECT .... FROM qry_searchQuery WHERE ...."
Me.RecordSource = sql

Now the form displays all the records that contain data the user searched on. There is a button at the bottom of the form that can print a report. When this button is clicked, the current record source saved in Me.RecordSource must be saved as the query qry_UnitFinderReport. In the case that this query already exists from a previous operation it must override it. Hence why I used:

SendKeys "qry_UnitFinderReport{enter}y", False
DoCmd.RunCommand acCmdSaveAsQuery

Now back to your approach. I don't know what you are trying to do with:

DoCmd.Rename "qry_UnitFinderReport", acQuery, strQueryName

I don't understand what strQueryName is. It cannot be qry_searchQuery because that's just the default source and the report needs to print what the form is displaying. The filtered source query does not have a name, it was saved in Me.RecordSource so there is no query other than the default that can go in place of strQueryName.

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16534415
Max,
We've identified 2 things as being problematic with your code as you had it originally.
1. Send keys not reliable;send keys goes to active window ...which is not always the intended window.
2. you said the default for "old query" in the rename syntax was "query1" or "query2"....depending on whether the name was used already.

Since you are not using DoCmd.RunCommand acCmdLoadFromFile, I assumed you are building the sql string by selections made on the filter/sort screen and then saving the end result in the default "query1"......

I tried to use DoCmd.RunCommand acCmdApplyFilterSort in lieu of sendkeys and you said that was not working.

The use of strQueryName was to avoid your stated problem that the query string was being saved  to "query1"...."query2".... which you said could differ depending whether it was the name of an existng query. By declaring strQueryName as a temporary parameter variable, the query string can be output to the parameter instead of to the default "query1"... and renamed from there.
0
 
LVL 6

Author Comment

by:maxy88
ID: 16534990

Lets not confuse ourselves.... Right now I am using 1. as below and you suggested 2. using the rename strategy. The original code did not use both 1. and 2. at the same time.
>>1. Send keys not reliable;send keys goes to active window ...which is not always the intended window.
>>2. you said the default for "old query" in the rename syntax was "query1" or "query2"....depending on whether
>>the name was used already.

>>Since you are not using DoCmd.RunCommand acCmdLoadFromFile, I assumed you are building the sql string by
>>selections made on the filter/sort screen and then saving the end result in the default "query1"
The sql string is built dynamically. Please read my previous post. It is not saved in a default query1 but is assigned as the recordsource of the form via Me.RecordSource=sql. Please read my previous post. When I use the code below which you provided, but modified for the purpose of this example I get an error saying: ""The command or action 'SaveAsQuery' isn't available now". This is why I said earlier that it does not work....

  Application.Echo False
  DoCmd.RunCommand acCmdAdvancedFilterSort
  DoCmd.RunCommand acCmdApplyFilterSort
  DoCmd.RunCommand acCmdSaveAsQuery
  Application.Echo True

If I remove the line: "DoCmd.RunCommand acCmdApplyFilterSort" I get a message box asking me for the name of the query to save to. The default name is "QueryX". I think I've said this before but the code you provided is not doing what I need it to do.. I need something automatic, I don't need the user to fumble at the screen and then press the OK button and save the query as "QueryX" then rename that in the code to whatever is needed. I need a method to save the form source into the qry_UnitFinderReport query. I need this to be done automatically regardless of whether the destination query exists or not. Please read my previous post for the explanation on how this should work.

>>The use of strQueryName was to avoid your stated problem that the query string was being saved  to
>>"query1"...."query2".... which you said could differ depending whether it was the name of an existng query. By
>>declaring strQueryName as a temporary parameter variable, the query string can be output to the parameter
>>instead of to the default "query1"... and renamed from there.
I honestly don't understand what you are trying to say here. Where does strQueryName come from? Who gives it the name? What name? How do we get to that name? You made a subroutine and passed this as a parameter but what is it's value? Is it "Query1" is it "Query2" is it "qry_UnitFinderReport"? Please be more specific. I don't understand what name you are using and where you are getting it from. As mentioned before the source sql that I'm interested in is found in the Me.RecordSource location, there is no other place and this is not a query it's just a source object.

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16535280
Max,
I missed the fact that you got the record source approach I suggested in my post "Comment from puppydogbuddy Date: 04/21/2006 11:43AM EDT" to work.  Please post your code as you have it as of now.
 
Do you know what a variable is how it is used in code??  
0
 
LVL 6

Author Comment

by:maxy88
ID: 16535620

I don't see any comment from you on: "04/21/2006 11:43AM EDT" and I didn't get anything to work.... The only thing that works is the original code I posted at the very top.... I am still using it, and hoping that somebody can figure out why it works on some computers and not on others. The code is below once again:

    DoCmd.Echo False, ""
    DoCmd.RunCommand acCmdAdvancedFilterSort
    SendKeys "qry_UnitFinderReport{enter}y", False
    DoCmd.RunCommand acCmdSaveAsQuery
    DoCmd.Close , ""
    DoCmd.Echo True, ""
    DoCmd.OpenReport "rptUnitFinder", acPreview, "", ""

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16543562
Max,
I have already explained to you why it works on some computers and not on others.  You are using sendkeys, which is not recommended in a production environment.  This is because the keystrokes are processed by whichever window is currently active on the desktop.  

The only reason that your post is proving to be difficult is because there is little or no documentation available on the use of RunCommand constants (e.g. acCmdSaveAsQuery).  I am going to do some research to see if I can find anything and I will get back to you.

And here is copy of the relevant portion of  the comment from me that you "can't see"
_______________________________________________________________________
Comment from puppydogbuddy
Date: 04/21/2006 11:43AM EDT
 
Max,
 
another idea:<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 try to dynamically change the recordSource (assumes sql string saved in variable strSearch)
              Report!rptUnitFinder.RecordSource = strSearch
               DoCmd.OpenReport "rptUnitFinder", acPreview
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16548706
Max,
Try this and let me know if it will enable you to avoid sendKeys.  If it works for that, then there is some progress.

    DoCmd.Echo False, ""
    DoCmd.RunCommand acCmdAdvancedFilterSort
    DoCmd.RunCommand acCmdLoadFromQuery
    DoCmd.RunCommand acCmdSaveAsQuery
    DoCmd.Close , ""
    DoCmd.Echo True, ""
    DoCmd.OpenReport "rptUnitFinder", acPreview, "", ""
0
 
LVL 6

Author Comment

by:maxy88
ID: 16554416

I tried the code above and I got a message saying to select the applicable filter and gave me a list of filters available. After I picked the one for the report, I was asked to enter the name of the query....

For specific searches, where only a few items were found on the form, when I press the report button, there is no filter in the list. I am forced to click on cancel after which I'm left out in the open with a message saying that the RunCommand action was cancelled. With the screen refresh turned off there is nothing to do but quit the entire application.

Max.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 375 total points
ID: 16555738
Max,
Try the code below and let me know............it should be an improvement over what you currently have for these reasons:
        1. Using the SetWarnings False eliminates many of the unnecessary prompts, incuding the prompt at the end, and
        2. reduces the number of keystrokes that need to be sent by SendKeys from 2 to 1 making the SendKeys less risky.
 
____________________________________________________________________________
    Dim stDocName As String
 
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdAdvancedFilterSort
    SendKeys "qry_UnitFinderReport{enter}", False
   
    DoCmd.RunCommand acCmdSaveAsQuery
    DoCmd.Close
    DoCmd.SetWarnings True
   
    stDocName = "rptUnitFinder"
    DoCmd.OpenReport stDocName, acPreview    



0
 
LVL 6

Author Comment

by:maxy88
ID: 16563711

puppydogbuddy,

The code above behaves the same way. It works on some computers and does not on others. Also, because you removed the "y" in the SendKeys command now the user is prompted to confirm overwrite messages of the query being saved, if that query was created before, the first time the report was printed. I had the "y" in there to take care of this case.... So I put it back the way it was - I'm back to square one.... Unfortunatelly I don't see any alternatives and we can go back and forth between this approach and the other one you proposed with no progress being made.... Since no one else has joined this discussion so far, I will close this thread and reward you for your help by accepting your last post. Thanks.

Max.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16563946
Max,
I was pretty sure theDoCmd.Set Warnings False.....would eliminate some of the prompts you were getting.....Oh well......Sorry I couldn't help you more, but there is little or no documentation for the  RunCommand constants in Access, and even less documentation for an alternative to SendKeys in your particular case.  

If what you have now becomes unacceptable to you, You might want to investigate a more flexible methodology, like dynamically building a Where clause from selections made from drop down lists (e.g. combo boxes). Using that methodology, you could open your report this way.

                    DoCmd.OpenReport "rptUnitFinder", acPreview, , strWhere
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

580 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