Multiple data source on a Report

[ddeldeb]

I'm building a report off of a table. I want to add a text box to the report and have it get a value from a separate query. I then need to use that value in a calculation on the report. I entered the SQL right into the control data field for the text box, but I still get the #name error. How do I add an other data source to a report??

Thanks Dave,,

 

[SydneyGeek]

I'd tackle this by turning the text box into a combo box. You won't tell the difference when the report prints, but you can place SQL statements in the Control Source (AFAIK, you can't do this in a text box)

Say you have a query in your new combo (cmbSomeName) with 6 fields, and you want to use data from the 2nd field in another text box.
Set the Control Source for the text box to:
=cmbSomeName.column(1) -- columns in combos and lists are zero-indexed.

Hope this helps :idea:

 

[ddeldeb]

Thanks, didn't get to try it yet but I'm sure it will work. I'll have the box hidden on the report anyways, all I needed was a way to get the one value sucked into the report.

Thanks Dave,,

 

[ddeldeb]

Tried this out last night and No go ! . I tried a combo and list box on the report and got nowhere. I did some testing on a form trying to get the value from the list or combo box to show up in a text box field, and it won't work until you actualy do a "click" event on either the combo or list box. And you can't click on a report. It seems I'll either have to do some code behind the report, or put a field on the form I generate the report from, with the value I want, and get it from there.

Any other ideals?? Thanks Dave,,

 

[SydneyGeek]

I gave it a try on a dummy report in Northwind. It did work, so maybe I wasn't clear enough on what I did.
To make it work, the Combo needs to be in the Detail section of the report -- therefore, a different value for each record. If you put it in a header section you may strike problems.

Here's a quick summary of my test:

In Northwind, create a new report using the wizard. I chose Orders as the record source for the report and selected CustomerID, City and Country as the fields. No sorting or grouping, Tabular layout in Portrait mode.

Note that CustomerID is already a ComboBox. I went into the CustomerID combo and edited the RowSource, adding ContactName as the third field. Closed and Saved.

Now add a new Textbox to the Detail section of the report. Set its ControlSource to be

=[CustomerID].Column(2)

Select Enter to save the changes. Run the report and you should see the contact name appearing in the report. Yep, I cheated for simplicity and used the same recordset but I have built reports and froms before where the combo brings together data from up to 3 different recordsets.

Good luck

 

[ddeldeb]

Sorry for the delay (this is more of a side project) I was able to get the combo box to work, kinda. It won't work with the data I want to get but I can select other data and have it work. I'll need to do some more testing then get back with another post.
Thanks for the help so far.

 

[bat17]

Have you had a look at Dlookup to see if you can get your data from the query with that?

Peter

 

[ddeldeb]

No I have not... yet. 2 things I am looking at are, putting a hidden text box on the form I run the report from, with the query result I want. I then should be able to pass that value from the form over to a text box on the report, right?

Or else I can use VBA code behind the report, to run my SQL statement and and do something like Report1!txtbox1.text = " sqlreturn " Right?

Thanks Dave,,

 

[Parra]

It has been my experience that it is easier to work off queries rather than tables.

Just my input. It would be easier to do what you need from a query, because you can add criteria to it.