(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.