Linking to tables

[euang]

Hi,hope someone can help.<br><br>I think what I am trying to do should be relatively simple but am having problems.<br><br>I have two tables in my database, one called detais and one called industrytypes.&nbsp;&nbsp;In the detais table a there is a field called category which consists of text such as Advertising, New Media.&nbsp;&nbsp;There is also a field called catcode which holds a number which corresponds to the content of the category filed ie Advertising&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.&nbsp;&nbsp;My client is inputing the category data manually and also has to check which code they have to put in.&nbsp;&nbsp;Is there a way of storing all the category names in another table (industrytypes) which have their own unique ID and then comparing the entry in the category field with the fields in the other table and then returning the relevant ID into the catcode filed in the details table?????<br><br>I've tried relationships and queries but just don't seem to be able to get the hang of this.&nbsp;&nbsp;I WANT TO LEARN!!!!<br><br>Hope someone can help, even if it's poining me in the direction of tutorials.<br><br>Thanks in advance<br><br>Euan

 

[DougP]

Does the Name and number always match in other words is:<br>&quot;Advertising&quot; always equal to number &quot;1&quot;?<br><br>Or does that change?<br><br>If it does NOT change then I would make table that has all of those in it.<br><br>like so:<br>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Number<br>Advertising&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>Costs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br>other&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<br><br>then make a form that has a combo box with 2 columns.<br>so they pick the name in the list and the number that goes with that name is automatically put in the table for them.<br>they don't have to look it up.<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[PaulF]

one method you might try is to create a query that has the code and its description listed.&nbsp;&nbsp;Set this query to display Unique Records.&nbsp;&nbsp;Then instead of having the customer use a textbox to enter the data, provide them with a ListBox or ComboBox with the info from the query used as the Row Source.&nbsp;&nbsp;Then they can make their selection from the&nbsp;&nbsp;items provided.&nbsp;&nbsp;If you want to update two fields then you can set the controlsource for the ComboBox/ListBox to unbound, and in the AfterUpdate() event of the ComboBox/ListBox update the textboxes (which can be hidden, visible property set to false), to reflect the correct column number.<br><br>(i.e.&nbsp;&nbsp;if the ListBox/ComboBox name is cboID and it contains two columns, first one for Code Description, second for Code Number and you have two hidden text boxes named txtCodeDesc and txtCode)&nbsp;&nbsp;use the following code:<br>txtCodeDesc = cboID.Column(0)<br>txtCode = cboID.Column(1)<br><br>PaulF<br>