Combo Box and New Records....

[stlrain95]

I want to use a combo box to Auto Fill....but I would also like to have the option that if the "customer" is not in the combo box, when I type in a new one it will show up in the combo box next time....is this possible?

I tried it on a dummy form and it would replace what was in the combo...I don't want that...I want it to add.

Also, on my production form, the wizard doesn't give me the same options for some reason.

Help is appreciated.

 

[PHV]

Take a look at the LimitToList property and the NotInList event of the ComboBox object.

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

 

[mp9]

You need to use your combo's OnNotInList event - you could code an event procedure to add the typed-in value to whatever table/data source you're using as the row source of the combo.

OnNotInList is documented fairly well in Access help.

http://www22.brinkster.com/accessory/

 

[stephenk1973]

If customer are being added to a table you can set the "row source" of the combobox to be based on the customers listed in that table. Something along the lines of....

select distinct customers from table;

So long as the "limit to list" property is set to "no" you should have no prblems add more customers in.

Regards

Stephen

 

[stlrain95]

Okay...here is what I tried....

I made the LimittoList = YES
Then changed the combo to a value instead of a record.

I typed in some customers under record source.

And added this.

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!CmbCustomer
' 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

Just one problem...it doesn't retain the name. I would prefer to have this come out of a table instead of a value.

 

[PHV]

Take a look at DoCmd.RunSQL "INSERT INTO ... VALUES ..."

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

 

[stlrain95]

PH...you have lost me...I have no idea what you are talking about?? Can you please explain?