Retrieving a Value

[sajarac]

Hi there, silly question here:

I would like to retrieve a values from another table and here is my situation:

I have a subform with a combo box, that combo box is a list of the sales rep and also I have a textbox and it htat box I have: =[Sales Rep].[Colum][2] by doing this I can retrieve the region of each sales rep.

Now I have another table "Sales Rep Manager" in that table I have:
ManagerID
Region
Code

What I am trying to do is in the subform when I select a sales rep a field check what ids the region and brings the code that match that region.

Sorry, but I cannot figure this.

Any help will be very appreciated.

Regards,

 

[jdraw]

I suggest you post a copy of your database (zip format).

 

[essaytee]

Lookup Dlookup, this may help http://access.mvps.org/access/general/gen0018.htm

 

[CJ_London]

easiest way is to include your manager table in the rowsource to your combo and bring through the code as an additional column

 

[sajarac]

Hi, I can do the first part, but I don't know about this: "bring through the code as an additional column"

 

[CJ_London]

rowsource for your combo is presumably something like this

SELECT tblSalesPeople.ID, tblSalesPeople.SalesName, tblSalesPeople.Region
FROM tblSalesPeople

Modify to something like

SELECT tblSalesPeople.ID, tblSalesPeople.SalesName, tblSalesPeople.Region, tblManagers.Code
FROM tblSalesPeople INNER JOIN tblManagers ON tblSalesPeople.Region=tblManagers.Region

and increase column count from 3 to 4.

Then in another control to display the code you would have

=myCombo.Column(3)