<Add New> in Combo Box or List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to learn how to add a "<Add New>" value to the dropdown list of a
combo box or a list box. Whenever you click on this <Add New> you can run
code obviously to add a new value to the dropdown list. I read an example
somewhere, but I can't seem to find it... Murphy's law eh... :/ Can someone
please point me in the right direction.
Rgds.,
Walter
 
I want to learn how to add a "<Add New>" value to the dropdown list of
a combo box or a list box. Whenever you click on this <Add New> you
can run code obviously to add a new value to the dropdown list. I
read an example somewhere, but I can't seem to find it... Murphy's law
eh... :/ Can someone please point me in the right direction.
Click to expand...

If you are using a ValueList, then you just add it at the end of the
string.

If you have based the rowsource on a recordset, you need to adjust it to
add the extra column:

SELECT ALL ID, ColourDescription FROM Colours
UNION
SELECT NULL, " <add new> "
ORDER BY ColourDescription ASC;

It's quite hard to get the extra line to sort to tbe bottom of the list,
but it's probably possible.

To respond to the user choosing it, you need to intercept the OnChange
event. Get the appropriate input from the user (perhaps with a custom
dialog form, e.g.) and then add it to the list. This is either patching
it into the Rowsource string if you are using a ValueList, or doing a SQL
INSERT command if it comes from a recordset.

Finally, calling the .Requery will update the rows in the listbox. For
extra points, you can select the new row programatically so that the user
doesn't have to find it.

Hope that helps


Tim F
 
Hey Tim,

At risk of spoon feeding, can u pls disect this further? ;)
I somewhat understand what you are saying...

The table I am working with has 2 fields, 1 is the primary key, and the
other is "make". The name of the table is "tblMakes".

How would you apply what you said using the above info?

Can you pls give me an example of some code with an event triggered by the
user clicking on the <add new>... preferably something simple like openning
another form.

Rgds.,
Walter
 
At risk of spoon feeding, can u pls disect this further? ;)
I somewhat understand what you are saying...

The table I am working with has 2 fields, 1 is the primary key, and
the other is "make". The name of the table is "tblMakes".

How would you apply what you said using the above info?
Click to expand...

Okay: start with the listbox/ combo box...

RowSourceType = Query
RowSource =
SELECT ALL PrimaryKey, Make FROM tblMakes
ORDER BY Make ASC;
' press <shift+F2> and type this into the box
ColumnCount = 2 ' two fields returned by the query
ColumnWidth = 0; ' hide the first (PK) column from the user
BoundColumn = 1 ' the control "sees" the PK column

Now, the Event ** This is air code, not tested: beware!!"

Private Sub lisMakes_AfterUpdate()
const strFormName = "dlgGetNewMake"
dim userNewMake as String
dim jetSQL as string
dim db as Database ' remember reference to DAO..!
dim row as Integer ' count rows in the list box

' open the form and wait
DoCmd.OpenForm strFormName,,,,, acDialog ' count the commas!

' You have to create the dlgGetNewMake form.
' It should HIDE itself if the user presses OKAY,
' and UNLOAD itself if the user presses CANCEL
'
' When it returns:
If IsLoaded(strFormName) Then ' google for ways of doing IsLoaded..
' recover the user input from the textbox
userNewMake = Forms(strFormName)!txtMake
' dont forget to unload the form
DoCmd.Close acForm, strFormName

' now make the command
jetSQL = "INSERT INTO tblMake(Make) " & _
"VALUES(""" & userNewMake & """)"

' check it: remove this line once everything is working
Debug.Assert vbYes = msgbox(jetSQL, vbYesNo, "is this okay?")

' and run it
Set db = CurrentDB()
db.Execute jetSQL, dbFailOnError ' should be error trapped here
' tidy up
Set db = Nothing

' now you need to make the new line appear
lisMakes.Requery

' look through the list and select it
for row = 0 to lisMakes.Count -1
If lisMakes.Column(1, row) = userNewMake Then
lisMakes.Selected(row)=True
Exit For
End If
next row

End If
End Sub

Can you pls give me an example of some code with an event triggered by
the user clicking on the <add new>... preferably something simple like
openning another form.
Click to expand...

The opening of the form is the easy bit.. <grin> Actually, nothing here
is that hard if you take it bit by bit.

All the best


Tim F
 
Back
Top