(Sub?)Query within a Report [Resolved]

[Kinsy]

I have a report that needs 4 pieces of information.

Retailer Name:

The report runs a query that gets the ID number of the Retailer used. Obviously this is meaningless to the user and i want the report to fetch the Retailer Name from the tblRetailers table.

Ive written the SQL and put it in the report_activate event as a string, but im not so sure how i can execute the SQL to get me the desired result.
Code:-

Dim intRetailID As Integer
Dim strRetailerSQL as String

strRetailerSQL = "SELECT RetailerName FROM tblRetailers WHERE RetailID = "
intRetailID = Me.txtRetailID.Text
strRetailerSQL = strRetailerSQL & intRetailID
Me!txtRetailerName.ControlSource = strRetailerSQL

Putting it in Report_Activate or Report_Page wont work as VBA says i cant set the ControlSource property while printing.

Putting it in Report_Load means that the code can't access txtRetailID.

I want to do something similar to this for a couple of other fields - 2 ContactName fields in 2 different records in a table called tblContacts.

I think all i need is either a way to set the ControlSource property early, while still getting the value out of txtRetailID..

OR

Get the RetailID from the query rather than a text box, allowing me to use it in the subquery to fetch the RetailerName.

[Ecniv]

Options :
- shove the data in a temp table and run the report from that.
- parameters??


Vince

[Kinsy]

Thats a bit of an inelegant solution.

Is there no way i can change a text box's RecordSource just after the query has been run, without incuring a cannot change while printing error?

[Kinsy]

Im SO impressed with these forums (large sarcastic *cough*).

Anyway ive managed to come up with a NICE and SIMPLE answer.

I can use the CurrentDB method with the OpenRecordSet property to get what i want, like so:-

Function GetRetailerName(strResult as String)
Dim strSQL as String
Dim intRetailerID as Integer

intRetailerID = 1

strSQL = "SELECT RetailerID, RetailerName " & _
               "FROM tblRetailers " & _
               "WHERE RetailerID = "
strSQL = strSQL & intRetailerID

strResult = CurrentDB.OpenRecordSet(strSQL).OpenRecordSet.Fields("RetailerName")

txtTextBox1.Text = strResult

End Function

Simple.