How to connect a form to a report? (newbie question)

[danielb1952]

I have created a form that uses the results of two queries: the first query is used to populate a combo box and then the selected value in the combo box is used as a parameter for the second query. This works fine.

I have also created a report that displays the results of a third query, where the third query is identical to the second query except that it prompts the user to enter a parameter, instead of getting the parameter from the combo box. This also works fine.

But now I want to tie the report, query and form together. In other words, I want the user to select from a combo box populated by the first query and then next they will see a report based on the second query (or modified version thereof).

How can I do this?

(Sorry, I am an Access newbie, although I have programming experience in my background.)

[ajalwaysus]

If you are wanting to show a report that has the same data as the form once you make a selection from the combo box, then you could simply make the record source of the report the same as the form. This way when you open the report, it will grab the value of the combo box the same way your form does now.

Am I over simplifying this, or is this what you had in mind?
Let me know if this is correct.

-AJ

[danielb1952]

AJ,

Not sure if I explained it correctly. The combo box is on the form. So trying to follow your instructions, I changed the Record Source of the report to the combo box, using a form of [Forms]![frmMyForm]![Combo0], but this results in a Sorting and Grouping error for the report, and also results in a error when I run the report: "The record source [Forms]![frmMyForm]![Combo0], specified on this form or report does not exist".

These additional details may help to explain the situation:

(1) The Row Souce Type and the Row Source of the combo box are""Table/Query" and "qryFirstQuery" .

(2) The OK button code of the combo box calls OpenQuery on qrySecondQuery.

(3) The Criteria for the parameter in qrySecondQuery is [Forms]![frmMyForm]![Combo0].

[NeoPa]

So, the second query is run simply as a query right?

Then you want to run a report based on the same data as that shown when you run the second query?

[danielb1952]

NeoPa,

I apologize for my ignorance of this subject, but I'm not sure what you mean by "run simply as a query". The second query, in fact, cannot run by itself but must be invoked only by virtue of running the form. Perhaps that's the problem, that I haven't constructed the query and/or form correctly?

And yes, I want to run a report based on the same data as that shown when you run the form which invokes the second query. But note that the form first allows the user to make a selection in a combo box, and the selected value is then used as a criteria of the second query.

I'd be perfectly happy to modify the form and/or queries if that's what necessary. I only want as an end result the ability to allow the user to select from the combo box and then to have the act of selection automatically run the second query and then to automatically have the results of the second query displayed by a report.

[Delerna]

(3) The Criteria for the parameter in qrySecondQuery is [Forms]![frmMyForm]![Combo0].

This means that you can use qrySecondQuery as the data source for your report and as long as the form is open then the report will show the data according to the selection in the combo box.

So, put a button on the form that opens the report when clicked and all should be correct

The user will choose something in the combo box
click the button
and the report will open and show the data accordingly

Is that what you are after ??? I am just rewording what has already been posted

[NeoPa]

When I say "run simply as a query" I am referring to opening a query (QueryDef object in the Queries Window) manually. IE. when someone selects the query and hits enter to run it. It opens up and shows its contents to the operator.

Including a reference to a form's control is certainly a possible way to achieve this, but I would suggest a better method would be to apply a filter when the report is opened from your code. I'm happy to help with this, but first I suggest you check out Example Filtering on a Form. This covers the basic concepts that you will be using (assuming you'd like to go this way). We will apply the same methods to opening the report as the article illustrates for opening another form.

[danielb1952]

I've got it, thanks to all who contributed! I took Delerna's suggestion of adding a button to the form that opens the report when clicked.

Now on a related topic, does anyone know if there's a better workaround to the scientific notation problem (MSKB 180590, "Output to XLS Changes Text to Scientific Notation Number" ) than adding an apostrophe to the value? Some of my values have the form nnnnDnnnnn, e.g., "2006D00041 ", which gets converted to scientific notation when exporting the report results to Excel.

[Delerna]

When importing from Excel to Access the data type for the field is figured from the value in the first row of the excel document.
So, if I have a column of numbers in excel and I want that column to come in to access as text I insert a row at the top of excel and put data into each cell to force whatever type I want for each field. Once the excel data is in Access I delete that row.


I am wodering if the same might be true in reverse.
If you ensure that a row that has a value that cannot possibly be interpreted in any way, other than as text, is at the top,then excel might treat the whole column as text.

One way to do that would be with a union query

[code=sql]
SELECT "Force This Field To Be Text" as FieldName, ...
UNION ALL
SELECT * FROM YourOriginalQue ry
[/code]

I don't know if it will work.