Display results of query from a form in the same report each time the query is run

[mcervenka1]

I have created a query which is run from a form. When the query is executed the results are displayed. I would like to have those results in a formatted report that can be printed if desired and not displayed on the screen. In addition once the formatted report has been created, I would like for the result for every query thereafter to be printed using the same formatted report. Now the hard part, would like to include the VBA in the coding that is assigned to the command button assigned to execute the query on the form, to produce the formatted report

[twinnyfo]

mcervenka1,

It may be that you are not fully aware of how queries and reports are related to each other.

A query simply gathers and presents data from your database, based on certain criteria. A report merely uses the results of the query.

So, if you have your query, you have the majority of your work done for this post. Simply create a report and set its record source to the query you have described. Add the fields from the qurey onto your report and format the report as desired. Once you save the report, any time you run the report, it will be based on the most current data pulled by the query. I hope this makees sense.

Your "hard part" is actually the easy part. Simply use DoCmd.OpenReport [ReportName], acNormal to run and print the report.

There may be slightly more to it, but that is the general idea.

We will be glad to guide you through any more challenging aspects of this....

[mcervenka1]

makes sense. Please allow me try to clarify. I am running the query from a form. Entering the parameter information into the form and the push button to execute the query. Where would I add your coding? I am using macro's. Please see attachment containing: Form, Property Sheet, embedded macro commands, and query

[twinnyfo]

My first question would be, "Is it necessary to build your Query in the VBA?"

It is possible to create a query that uses the values from your Form. The Criteria in the query would be [Forms]![FormName]![TextBoxName].

It is possible to have the VBA create the Query Definition and then the Report can use that definition, but it is a bit more involved than what I have suggested.

Let us know the direction you want to go.

-------
Also, as a side note, I would recommend NOT using Macros, as they severely limit the capabilities of Access. Even the brightest experts on this forum are not usually very adept at using macros because of their inherent limitations. I do not believe you can do what you are suggesting by using macros (but I may be wrong). Stick to VBA and you have more control over all aspects of your project.

[NeoPa]

Hi M.

It seems you really are opening an actual QueryDef in Datasheet View.

If I understand you correctly, instead of seeing the data in this format you'd prefer to see it embedded within a report which is properly designed to show the data in a more human-friendly way?

If that's the case then simply do as TwinnyFo says :
Design a report that uses that QueryDef (Often simply referred to as Query.) as its RecordSource. Then, when it comes to pressing the button on your Form, instead of opening the QueryDef you'd open the Report instead.

I have to say that I'd certainly agree with what Twinny says about avoiding the use of macros in Access, but I've said that before and ultimately it's your choice. To be fair, whichever approach you use should be fundamentally very straightforward.

[mcervenka1]

NeoPa. I found "Convert Form's Macro to VBA" in Access. Is this a good tool to use to convert my form?

[twinnyfo]

mcervenka1,

Yes, that is a good place to start converting your macros to VBA. In nearly all cases, the form will continue to work as previously, but the macros will be removed. I have heard of some instances in which the form does not work as expected because of the conversion process--however, I have not experienced that and don't expect that you should have many problems, based on your descriptions so far.

let us know how things come out and we can continue to work from there!

[mcervenka1]

Neopa and twinnyfo...the conversion ran like a charm,, EXCEPT when I enter the values (start date and end date) for the query in my form and then a parameter value pop up box ask to enter the same values (start date and end date), then the report will display. I think I know why, when reading the VBS for the form, I see where the DoCmd is executing the query causing the parameter value popup box because there is no coding for Filter that would allow a search on the values entered on the form. Am I close??? I am basing my guestimation on the coding in the Record update VBA we discussed on the posting for displaying only single records or all it's doubles and not the rest of the records in the query. Other wise, if I didn't convert to VBA the form would allow me to enter the value and the query would execute and shows the results on the screen.

[twinnyfo]

mcervenka1,

First (again) - Please do not cut and paste screenshots of your code. Simply cut and paste the code itself.

Second, there is nothing in the code you provided that would indicate why there is a pop up requesting the values in the text boxes. However, my guestimation is that when you converted to VBA, it "may" have created a new form? Thus, the query you are calling (Exams Surrender Query), is searching for values on a form that is not currently open.

This is a hunch, but a probable one. Let us know what you find.

[PhilOfWalton]

Sorry to butt in, but I think you are missing the point.

The VBA should be opening the report, NOT the Query

There should be a query as the recordsource for the report that includes criteria that refer to the Forms!FormName!StartDate and Forms!FormName!EndDate.

Don't forget that both these dates will need a hash (#) on either side of those date fields

Phil

[twinnyfo]

True, Phil,

I was focused on getting the Query/Form discrepancy fixed first. Hopefully, the query will produce the results we want. Then when the query is assigned to the Report (which hopefully it already is), we call the report from the Form.

[mcervenka1]

twinnyfo . you were right. I copied the form and made used the marco VBA converter on the copy. I copied to test the changes on the copy and if it worked, I would make changes on the original form. When I did use the converter on the original form, it worked perfectly and did not show the parameter value popup box.

[twinnyfo]

And, so now back to Phil's observation. You should call the report, not the query from your Form. As long s that Query is assigned to the Report's Record Source, all should be fine.

[NeoPa]

PhilOfWalton:
Don't forget that both these dates will need a hash (#) on either side of those date fields

That is absolutely not as simple as that. Only American date formats (and those that use a similar one) can reliably work simply by adding hashes (#) as delimiters, and they aren't portable. There are multiple valid date formats for SQL, but English, and any European, formats cannot safely be used. Please see Literal DateTimes and Their Delimiters (#) for a full explanation.

[NeoPa]

MCervenka:
NeoPa. I found "Convert Form's Macro to VBA" in Access. Is this a good tool to use to convert my form?

As far as tools go it'll do the job. However, do bear in mind that such code will always be very clumsy. It's also very limited. You get only what a macro can give you, which limitation is one of the reasons not to use them if you can avoid them. That said, as a first step it's not a bad approach.

Where possible though, learn some of the basics from them and then move on as quickly as you can. You're far better off creating your logic in VBA to start with. Many of the macro conversions use RunCommand() which should be avoided wherever there's an alternative - and there usually is.