Loading Data From Related Tables To Form Text Boxes

[ColinKJ]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm new to Access, and I'm in the process of building a form.<o></o>
<o></o>
I've had some very usefull help from Norie and Bob Larson so far, now I'm looking for assistance in getting some more data from some related tables to display in two more Textboxes.<o></o>
<o></o>
So far, when the form is opened, there is a Textbox named Contract No, when you Double Click in this Textbox, a Listbox is displayed showing a list of Contracts, No's and Names. When you Double click on a selection from the List, the Contract No is displayed in the Contract No Textbox, and the Contract Name in the Contract Name Textbox, the Listbox is then hidden.<o></o>
<o></o>
Now, based on the Contract No selected, I want to populate two more TextBoxes, Account Manager, and Business Unit.<o></o>
<o></o>
I have a Table called tbl_Contract_Details which contain fields that identify the Account Manager ID and the Business unit ID related to the contract No's. I then have two Tables tbl_BUList and tbl_AccMgr from which I want to get the Business Unit Name and Account Manager Name based on the related ID’s and put them in the two Textboxes.<o></o>
<o></o>
I hope I've explained that ok, any help much appreciated.<o></o>

 

[boblarson]

What you should do is to include the Contract details with the list box's row source and you could just display them in the text boxes using

=[ListBoxNameHere].[Column](3) (where 3 is the fourth listbox column, for example)

Since you don't need to store that information since this data is obviously tied to the contract, then you should just display it and not store it again. Does that make sense?