table links

[Playmaker]

Hi, I'm still learning VB programming, but I just started my first project at my new job, and I need help. I'm trying to populate a text field or label with text that it gets from another table in my database. I'm using Visual Basic 6 w/ SQL Server 7 as database.
For example: I have my main table which is my CUSTOMERS table with all the usual information. One of the fields in that table is OCCUPATION CODE. Whatever the customer's occupation is, instead of having the whole description, I use a code to represent it like '45' which is 'Agriculture Industry'. In another table called OCCUPATION CODES, is where I have two fields, the occupation code and the occupation description. On my form I want to be able to type in the code like '45' and have the label or text field automatically give the description. How can that be done? Thanks...

[Clunietp]

Just look it up in the table and display it in your other textbox/label:

Assuming cn is your connection object (already connected to SQL server) and rs is a recordset object. This example uses ADO 2.x


dim cn as adodb.connection
dim rs as adodb.recordset

set cn = new connection
cn.open <ConnectionStringHere>

set rs = cn.execute("Select * from [Occupation Codes] Where CODEID = " & val(txtOccupationID.text),,adcmdtext)

if rs.bof = false or rs.eof = false then
txtOccupationName.text = rs.fields("Occupation Name").value
else
txtOccupationName.text = "Unknown"
end if


Otherwise, if you do not need to update the data you are currently using in your recordset, you can use a join to get that information. See the help file to see how to do a join and set that as your recordsource...

HTH

Tom

[This message has been edited by Clunietp (edited 12-27-1999).]