Store one field, Display another...

[blondy]

Hi all,

I have a number of tables with 2 fields ("ID" and "Description&quot eg.(IDNM and Name), (IDAG and Age_Group), (IDSP and Sport). I want to be able to choose the "Description" field from a drop-down but save the "ID" field in the table that I will use to join them together.

Can anyone help?

 

[RamziSaab]

hey,

make a combo box and on properties put the column count to 2. on the rowsource click on the '...' and make a the query u want wish has all the info... in the first field make sure u put description and the 2nd the ID.

Now go and paste this code on afterupdate

Private Sub cbotest_AfterUpdate()
Dim str2ndcolumn As String
Dim strname As String

strname = Me.cbotest.Value

For i = 1 To 1000
If cbotest.Column(0, i) = strname Then
str2ndcolumn = cbotest.Column(1, i)
MsgBox str2ndcolumn
End If
Next

End Sub

this will scroll and find when the two values are equal and return the value of the 2nd (hidden) column...the ID is stored under the name str2ndcolumn (but u can call it what u want)

If you have trouble tell me!

Ramzi

 

[RickSpr]

It's actually simpler than this.

In the combo's Control Source, select the field of the form's recordset that you want it to update.

In the combo's Row Source Type, select Table/Query.

In the combo's Row Source, put a SQL query that selects the two columns ID and Description and orders them by the description. Example:

    SELECT IDNM, Name FROM NameTable ORDER BY Name

If you already have a query object containing this statement, you can enter the query name instead.

In the combo's Bound Column, put 1 (representing the ID field). This identifies the ID column as the one that gets stored in the form's recordset.

In the combo's Column Widths property, put 0. This sets the width of the first column to 0, so it will be invisible, and also connects the text box part of the combo to the second column (the Description). Check out the help file topic on Column Widths to see how useful it can be.

Using the properties, there is no need to write any code for a combo box that updates a field in the form's recordset.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein