Adding value to combo box..

[stephendeloach via AccessMonster.com]

I have a combo box thats rowsource is a table. How can I be able to add new
entries into the combo box without going back into the table itself?

Stephen

 

[Guest]

There is an event on the combobox called "On Not in List" that fires when new
data is entered that is not in the datasource.
I suggest using this event to give the user some options about the data,
making sure the user really wants to add that data to the table and it's not
just a typing error. You can also put some validation in to make sure the
data looks like you want it to.
It's important that you maintain some control on the data, because if your
users are anything like mine, that list could become verrrrrrrry long! Good
luck.

 

[Mr. B]

There is an event on the combobox called "On Not in List" that fires when new
data is entered that is not in the datasource.
I suggest using this event to give the user some options about the data,
making sure the user really wants to add that data to the table and it's not
just a typing error. You can also put some validation in to make sure the
data looks like you want it to.
It's important that you maintain some control on the data, because if your
users are anything like mine, that list could become verrrrrrrry long! Good
luck.





- Show quoted text -

Stephen,

Just a little more help:

There is just a little more involved in utilizing the "On Not In List"
event that first meets the eye. Here is a link that will perhaps
provide you with some code that will help you get yours working:

http://www.mvps.org/access/forms/frm0015.htm

You may need to modify the code just a little but it should at least
get you started.

HTH

Mr B

 

[missinglinq via AccessMonster.com]

If the rowsource is a table, you've got to go "back into the table" in one
way or the other! You've got to add a new record to the underlying table and
then requery the combo box. If by "going back into the table" you mean
actually opening the table or opening a form to enter the data, you can avoid
this, I think, by using a query to insert the new record.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Check this link on "How to use the Microsoft Access Not In List event of a
combo box
" for a sample code on how to do this

http://www.databasedev.co.uk/not_in_list.html

 

[stephendeloach via AccessMonster.com]

Thanks for the replies. This is what I have come up with... When I add a new
address the "Unknown Address" box comes up and asks if i want to add the
address. I click Yes. Then "Run-time error 3192" Could not find output table
tblBillTo, i click debug and it takes me to the VB editor and this is what I
have..



Private Sub Combo14_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Address...")
If i = vbYes Then
strSQL = "Insert Into tblBillTo ([strCompany]) " & _
"values ('" & NewData & "');"
***** CurrentDb.Execute strSQL, dbFailOnError ***********
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub



******* Is the line highlighted **********

BillTo is the table where the Address is stored and Company is the name of
the Field List...

 

[Guest]

My first guess would be the property LimitToList for the combo box needs to
be set to no.

Thanks for the replies. This is what I have come up with... When I add a new
address the "Unknown Address" box comes up and asks if i want to add the
address. I click Yes. Then "Run-time error 3192" Could not find output table
tblBillTo, i click debug and it takes me to the VB editor and this is what I
have..



Private Sub Combo14_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Address...")
If i = vbYes Then
strSQL = "Insert Into tblBillTo ([strCompany]) " & _
"values ('" & NewData & "');"
***** CurrentDb.Execute strSQL, dbFailOnError ***********
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub



******* Is the line highlighted **********

BillTo is the table where the Address is stored and Company is the name of
the Field List...

Check this link on "How to use the Microsoft Access Not In List event of a
combo box
" for a sample code on how to do this

http://www.databasedev.co.uk/not_in_list.html

 

[stephendeloach via AccessMonster.com]

Now I am able to enter new addresses but the new address isnt in the BillTo
table?

My first guess would be the property LimitToList for the combo box needs to
be set to no.

Thanks for the replies. This is what I have come up with... When I add a new
address the "Unknown Address" box comes up and asks if i want to add the

[quoted text clipped - 40 lines]