New items in bound combo box...

[Trygve Lorentzen]

Hi,

I have a combo box to select persons with 2 columns, personid and name. The
'personid' is hidden by setting the width of the first column to 0. I'd like
to be able to create new persons in this combo box by typing a name that
does not exist in the list, but I'm not allowed to set LimitToList property
to 'No'. It says I have to increase the width of the bound column. If I do
that, only the 'personid' is shown in the combo box (both id and name are
shown when combo box is "dropped down"). Is there a way to make this work
without coding? If not, how can I best implement this behaviour in VBA?

Any help will be GREATLY appreciated, thanks in advance

Cheers,
Trygve

 

[Neil]

Hi,

I have following code in the NotInList event for my combo. Maybe you could
use it as a template?

----------------------------------------
Dim rs As DAO.Recordset
Dim intResponse As Integer
' Ask user to confirm addition
intResponse = MsgBox("'" & Format(NewData, ">") & "' is not in the
Position Lookup table." _
& vbNewLine & "Would you like to add it now?", vbYesNo +
vbQuestion + _
vbDefaultButton2, "Add new data?")
' Check msg response
If intResponse = vbYes Then
' Add to table
Set rs = CurrentDb.OpenRecordset("tblPosition", dbOpenDynaset)
rs.AddNew
rs!strPosition = UCase(NewData)
rs.Update
' Set response to show data has been added succesfully
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
----------------------------------------

Just replace "tblPosition" with your table name. Also note that for the
above code to work you need a reference to the Microsoft DAO 3.6 Object
Library (found in Tools->References).

HTH,

Neil.

 

[Jeff Boyce]

Trygve

I don't understand. You say "I'm not allowed to set LimitToList to 'No'" --
what happens when you try?

By the way, the method you will need to use to add a new value leaves
LimitToList as "Yes". You may be hitting the problem because the combo box
can't figure how to add the PersonID value, given what you've typed.

Check Access HELP on the NotInList event -- that's what you'll need to use
(this suggestion assumes you have a table of "persons"). The generic
approach is:

You enter a new name in the combo box, triggering the NotInList event
The code you create in the NotInList event prompts for whether you wish

to add a new name -- if "yes"...

The code pops up a data entry form for your tblPerson.
You add a new person and close the data entry form.
The code in your NotInList event includes code that says "there's a newly added value - use it".
The NotInList event code completes, putting you back to your original

form, with the newly added name.

 

[Neil]

Sorry,

You would also need to replace strPosition (rs!strPosition) to the name of
the field in your table.

HTH,

Neil.