ComboBox issue

[koturtle]

I need to create a combobox that a user can add a record via a prompt
to save that record to the list. I want the option to use that value
but not add it to the list. I am using Dev's code below to add a new
item to the combobox if the user replies yet. However i want the
option for the user to say no but still use the value once. He
doesn't want it added to the combo list but wants to use it.

Is this possible?

TIA




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

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' This value does not exist in this list
"
strMsg = strMsg & " Do you want to add this value to the list?"
strMsg = strMsg & " Click Yes to add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblquantity", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Quantity = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If


Rem rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

 

[Jeff Boyce]

I'm having trouble with imagining the logic/sequence of your situation.

The reason for using a combobox is to limit the items folks can select to a
valid list.

The reason for using the NotInList event is to allow for the creation (and
use) of items not originally listed.

So what kind of situation calls for the one-time-use of a new item
(implication is that it is NOT a valid item other than for just that one
situation).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[koturtle]

Thanks for the reply.

For an example if the user wants to select a size of screw from the
drop down and there are 10 normal choices.
However, once in a while there might be a size that he needs to select
that is not in the list and that he doesn't want added to the list.

Maybe he wants to keep the list short for selecting the most common
sizes.

thanks again,

KO

 

[Jeff Boyce]

The difficulty I'm having is in imagining why the less-common should NOT be
added. Less-common, in my world, doesn't mean "never", just "rarely".

Would you be able to accomplish (roughly) the same thing if all the "common"
sizes were listed first, then the uncommon ones? You could do that with a
query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

.... and another thought ...

If PersonA decided to use (but not add) a Size13, and that was not shown on
the list, then PersonB decided to use (but not add) a Size 13 (?!), do they
both (both versions of Size13) mean the same thing? Again, this is a good
argument for limiting the list to only include valid values (and since they
were using Size13, that value would be valid, too!)

Just one person's opinion ...

Regards

Jeff Boyce
Microsoft Office/Access MVP