VB6/Access 2000 - Second Recordset as Lookup Table

[jtrapat1]

Can someone help?
I'm accessing two tables from one form.

I declare my main recordset to get most of the fields needed for my form.
Dim RS as ADODB.Recordset

Now I need to declare another recordset to another table (a lookup table)so I can translate the value of the dropdown combobox into a textfield.

I don't know how to go about doing this.

I tried declaring another recordset,
Dim RS2 as ADODB.Recordset
but then all of the routines associated with the initial recordset, like AddNew, LoadRecord, etc. will be screwed up.

Any suggestions?

 

[edderic]

Declaration is not right most :

Dim RS2 as ADODB.Recordset (fout)

=
Dim RS2 as New ADODB.Recordset

Eric De Decker
vbg.be@vbgroup.nl

Licence And Copy Protection AxtiveX.

Download Demo version on my Site:
Promotions before 02/28/2001 (free source codebook),visite my site

http://www.createsoftware2000.com

 

[RickSpr]

You don't need the second recordset. You can use the AfterUpdate event of the combo box to set the text box value. There are 2 ways:
(1) Change the combo box's Row Source to a join of its current table with the lookup table (joining on the code field you want to look up), and include the text field you want from the lookup table as a column in the combo box. If you don't want it displayed, set its column width to 0 (e.g. set the combo box's Column Width property to ";0&quot. Then in the AfterUpdate obtain the value from the combo box column with:
Me!txtSomething = Me!cboLookup.Column(1, Me!cboLookup.ListIndex)
or (2) Use a DLookup function in the AfterUpdate to look up the data and assign it to the text box:
Me!txtSomething = DLookup("TextField", "LookupTable", "LookupTable.CodeValue = " & cboLookup.Value&quot Rick Sprague