Combo box storing values into table

Hi all

I've been given the task of improving and adding a few features to an access db, and I've run into some potential problems...

One of the forms has combobox that stores a foreign key and a text value in the main data table. There is an associated numerical value, that I also want inserted into the main table from the combo box, but it only seems to allow the one value. Using Access 2003.

any help much appreiciated.

You need to do it in code First make sure the combo box rowsource contains all 3 values and is set to have 3 columns. These are referred to as YourComboName.Column(0) as the first (usually index) column and .column(1) as the second etc. In the afterUpdate event Open a recordset on your table with the index as WHERE clause and Update your field with the column values. If you need a code example let me know.

Thanks! I'll give it a go

/edit: Actually, if you had some example code to hand, that'd be really great.

sadly I don't seem to be able to attach a sample but here's some code for the after update event that works

Combo 10 is bound to the Main Table . It (the Combo) has 3 columns. The rowsource is a query on the 3 fields in the Lookup Table (LUTable)

SELECT LUTable.LUTableKey, LUTable.LUTableText, LUTable.LUTableNum FROM LUTable;

'In my case column width fot the key field is set to 0cm and is bound to the foreign key [LUTableKey] in the Main Table

Private Sub Combo10_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
'Dim strSQL As String
Dim rs As Recordset
Set db = CurrentDb

If Me.Dirty Then
Me.Dirty = False
End If

' these two textboxes are unbound and invisible

Me!txtLUTableText = ""
Me!txtLUTableNum = Null
Me!txtLUTableText = Me!Combo10.Column(1)
Me!txtLUTableNum = Me!Combo10.Column(2)

Set qdf = db.QueryDefs("Query1")

' Query1 is a stored query on the main table with criteria that
' references the main table index in a bound text box on the form
' The main table has the similar fields as the lookup table and 'whatever others. Mine includes "Description"

' Important housekeeping

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset

'this just makes sure a record exists

If rs.EOF And rs.BOF Then
MsgBox "Enter a Description"
Me!Description.SetFocus
Exit Sub
Else
rs.Edit

'pass the values from the unbound controls to the Main Table

rs!LUTableText = Me!txtLUTableText
rs!LUTableNum = Me!txtLUTableNum
rs.Update
End If

End Sub

hope you can follow this and it helps - cheers

Thanks! that looks great. I'll give it whirl