modifying the listbox rowsource when user chooses "other"



I have a listbox that gets it's rowsource from a lookup table. When the user
clicks "other" in the list it sets the visible property of a textbx to true
and prompts the user to enter the other value and click an "OK" button. I
would like that new value to be entered into the listbox rowsource. I saw
this questioned answered recently but I can't find it now. I know it had to
do with concatenating the rowsource of the listbox with the text field value
but I can't seem to get the syntax correct. Any help would be greatly
appreciated. Thanks.

Allen Browne

Presumably you have an unbound text box where the user can enter the new
value, so your procedure will:
1. Add the value to the lookup table.
2. Requery the list box.
3. Set the list box's value to the newly created record.

You could use the After Update event procedure of the text box. Something
like this:

Private Sub txtNew_AfterUpdate()
Dim rs As DAO.Recordset
Dim lngNewID As Long

With Me.txtNew
If Not IsNull(.Value) Then
'Add the new value to the table.
Set rs = DBEngine(0)(0).OpenRecordset("MyLookupTable;", _
dbOpenDynaset, dbAppendOnly)
rs![MyField] = .Value
lngNewID = rs![ID]

'Requery the list, choose this value, and hide the text box.
With Me.List0
.Value = lngNewID
End With
.Visible = False
End If
End With
Set rs = Nothing
End Sub

The example assumes:
- The new value is entered in a text box named txtNew.
- The value gets addded to MyField in table MyLookupTable.
- This table has an AutoNumber primary key named ID.
- The list box is called List0.
- The bound column of the list box is the ID field.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question