Combo Box add to list when list is not from table? 1

[kaiana]

Hi,

I have a combobox that I input the list that was to be selected from (ie. list is not derived from another table). I am now needing for items to be added to that list by the user. I have seen plenty of threads that show how to do this when the data is pulled from another table but not for my problem. I have tried to change the combobox to use data from a new table but then I lose the data already in the field. Any suggestions on how to get around this would be appreciated.

 

[PHV]

Directly from the help file:
Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya kaiana . . . .

When using the [blue]Not In List Event[/blue] make the [purple]Limit To List Property[/purple] is set to yes, or it won't work.

See Ya! . . . . . .

 

[kaiana]

PHV,

That worked beautifully, added the item to the list but only for as long as the form remained open. As soon as I closed the form and reopened, the item was no longer on the list.

Regards

 

[PHV]

If you want persistent data, you have to save it in someway. In access, the easiest way to save data is to store it in a table...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kaiana]

That is not what I wanted to hear. I have tried to link it to a new table but I lose existing data. I will keep trying. I run reports based on items in the combo so it needs to be saved as an item in the list.

Any other suggestions?

 

[PHV]

And how was this combo initially populated ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kaiana]

I typed the items into the row source.

At the time I was at the very beginning stages of programming and believed what I was told (no that will be the only things we will want in the list) you know the story.

Regards

 

[PHV]

Why not creating a new table and manually populate it with the items you hard coded in the rowsource property and then make the combo bounded to this table ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kaiana]

I have tried that experimentally. The situation that I have is that the old db is currently being used. I am creating a whole new (updated) fe and be and will then transfer the data to the new be. When I have played with creating a new table it has not input the appropriate item into the field and has left it blank or errored. I will keep trying if you think that it should work.

Thanks

 

[cykopat]

On your combo box use a query to select * from FieldlistTables and than change the number of columns in the row source and you can adjust the width of them to show your data...

 

[jino]

Hi, I have a question very similiar to the issue projected by Kaiana. My combo box(list of CustomerID's) gets its values from a table. I need to add new values to the combo box. I have set LimitToList property to yes so that my NotInList trigger would fire. But when a new CustomerID is being added, I want to prompt the user to add more data into the customer table (Name, contact info etc) by showing another form. How can this be achieved ? Any help appreciated.

Thanks

Jino

 

[PHV]

In the NotInList event procedure launch the customer form as modal and then requery the combo.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244