modifying the listbox rowsource when user chooses "other"

[Guest]

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.AddNew
rs![MyField] = .Value
lngNewID = rs![ID]
rs.Update
rs.Close

'Requery the list, choose this value, and hide the text box.
With Me.List0
.Requery
.Value = lngNewID
.SetFocus
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.