Can you auto write AddItem VBA code to a form to populate a Combo

[BaBaBo]

Dear All:

I have a combo box on a form.
I have set up code to populate the list on the combo box as follows:

Private Sub Form_Load()
cmbFirstName.AddItem "David"
End Sub

Then there is a command button that you can use to add other names to the
list.
Private Sub cmdAddFirstName_Click()
Dim iCounter As Integer
If Me.txtInput.Value = "" Then Exit Sub Else 'Checks to see if nothing in
the text box.

For iCounter = 0 To (FirstName.ListCount - 1)

If FirstName.ItemData(iCounter) = txtInput.Value Then
MsgBox "Duplicate item. Can't add item."
Exit Sub 'Duplicate was found.

What I want to do is add code that will automatically add the code:

cmbFirstName.AddItem "New Name"

to the forms code for each new name added to the combo box.

Thanks in advance all.




The combo box is not bound to a table to get

 

[John W. Vinson]

Dear All:

I have a combo box on a form.
I have set up code to populate the list on the combo box as follows:

Private Sub Form_Load()
cmbFirstName.AddItem "David"
End Sub

Then there is a command button that you can use to add other names to the
list.
Private Sub cmdAddFirstName_Click()
Dim iCounter As Integer
If Me.txtInput.Value = "" Then Exit Sub Else 'Checks to see if nothing in
the text box.

For iCounter = 0 To (FirstName.ListCount - 1)

If FirstName.ItemData(iCounter) = txtInput.Value Then
MsgBox "Duplicate item. Can't add item."
Exit Sub 'Duplicate was found.

What I want to do is add code that will automatically add the code:

cmbFirstName.AddItem "New Name"

to the forms code for each new name added to the combo box.

Thanks in advance all.




The combo box is not bound to a table to get

Well... your code would be a HECK of a lot simpler if it were. You could use
the NotInList method to add a new record.

Even without that... are you sure you want to limit the combo to one record
per first name? Is that David Richardson, David Smith, or David Koresh? Or was
that a misleading example?

 

[Klatuu]

That would not be the way to do it.
There correct place to do it would be in the Combo's Not In List event. You
don't need a button.

 

[BaBaBo]

Dear John and Klatuu:

First to John's point I am just trying to work this out so yes the first
name is a bit of a red herring.

Secondly to Klatuu I agree about the "...not needing a separate button", I
agree. This question has been sparked by book "Microsoft Access VBA
Programming for the absolute beginner" p116-118.

I understand the use of the "NotOnList" propert. For what I am trying to do,
"NotOnList" is not central to the question, althouh it is certainly related.

I know I can populate a combobox by setting the Row Source Type to "Value
List" and having the new entries stored in the Row Source. The problem is
added items drop off the list once I have closed, then reopend the form.

Finally I do not want the comboboxes Row Source Type to be "Table/Query", as
this causes a run time error when using the AddItem method.

What I want to do is find a way to:

1 On adding a new value to the text box you are asked if you are certain
that this is what you want to do.

2. Not use a table as the record souce for the combo box.

3. Have any items added to the combobox dropdown list, using the "NotOnList"
property, to not drop off the list when you close the form.

To meat the three cryteria above, my solution is to have the following code:

FirstName.AddItem "NewName"

somehow added to the form automatically throught VBA code.

To repeat my self for clarity:

1. Person enters a name tha is not on the list, say the name is "Tom".
2. Message Box opens asking if they realy want to add the name to the combo
box list.
3. Person presses vbYes button.
4. VBA code automates the writing of a line of code into the form code: e.g.
FirstName.AddItem "Tom"


Question: Is the above possiable with VBA code?

Question: What code would you use to do add this?


Thank you both for you responses. Much appreciated.

 

[David W. Fenton]

I know I can populate a combobox by setting the Row Source Type to
"Value List" and having the new entries stored in the Row Source.
The problem is added items drop off the list once I have closed,
then reopend the form.

Finally I do not want the comboboxes Row Source Type to be
"Table/Query", as this causes a run time error when using the
AddItem method.

Stop using Access.

You obviously don't want to follow its rules, so you're never going
to be happy.

More explicitly, Access is a database application development tool.
You store your data in tables. The Access combo box is designed
primarily to display data from data tables. When you add a new
record to a combo box, you have to append the data to the source
table. When the table has one column this is quite easy (use the
data the user typed in and add the record in code). When it has more
than one column, you'll need to open a form in dialog mode to add
the data.

The help files are actually pretty good on this -- I learned how to
do this from using them.

And, for what it's worth, I used to think that this was more
complicated than it needed to be, that the NotInList event should be
smarter (if the combo box was based on a single-column table, the
default should be to prompt and add, without any need to write
code), but I've since concluded that it's problematic to do that (as
there could be validation rules and the like that would cause it to
fail, and then what?).

AddItem was added in A2002 or A2003, but I think it's there to make
list and combo boxes more friendly to people who aren't used to
programming database applications.

Learn to use data-driven combo and listboxes and you'll be a much
more versatile programmer.

 

[Bob Quintal]

Secondly to Klatuu I agree about the "...not needing a separate
button", I agree. This question has been sparked by book
"Microsoft Access VBA Programming for the absolute beginner"
p116-118.

Do yourself a favor and burn that thing.

I know I can populate a combobox by setting the Row Source Type to
"Value List" and having the new entries stored in the Row Source.
The problem is added items drop off the list once I have closed,
then reopend the form.

That is because you should use a table to permanently store the items
in the list.

Finally I do not want the comboboxes Row Source Type to be
"Table/Query", as this causes a run time error when using the
AddItem method.

So use an append query instead, or a popup form tied to the lookup
table triggered by the notinlist event.

What I want to do is find a way to:

1 On adding a new value to the text box you are asked if you are
certain that this is what you want to do.

2. Not use a table as the record souce for the combo box.

3. Have any items added to the combobox dropdown list, using the
"NotOnList"
property, to not drop off the list when you close the form.

To meat the three cryteria above, my solution is to have the
following code:

to meat.... hamburger.

You cannot meet the three criteria, items 1 and 3 are easy when you
use a table.

To repeat my self for clarity:

1. Person enters a name tha is not on the list, say the name is
"Tom". 2. Message Box opens asking if they realy want to add the
name to the combo box list.
3. Person presses vbYes button.
4. VBA code automates the writing of a line of code into the form
code: e.g. FirstName.AddItem "Tom"

just change the VBA code to:
If msgbox("Really?",vbyesno) = vbyes then
strSQL = "Insert into tblFirstNames (firstName) values(""" _
& me.combobox.text & """);"
CurrentDB.execute strSQL
me.combobox.requery
end if