Change Combo Box "Not in List" with VBA (1 Viewer)

[331]

Hello everyone,
I have a combo box that is "Limited to List". When the User enters an item not on the list, a message asks "Do you want to add this to the list?"
If the User Clicks "Yes", the item it added to the list and the field will contain the new item.
If the User Clicks "No", a message tells them they must select an item from the list.
HERE's WHERE I NEED HELP: The Users would like to be able to Click "No" and enter their new item in the field and not have it saved in the List.

Here's my code, in the "On Not In List" Event:

Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboVendor_NotInList

' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.

' Amended by P. Stroud on 03/06/2008
' EOP Budget and Fiscal database - VISA Statement
' Add new items to the table/field: tblVendors.VendorName

Dim strMsg As String

#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Vendor") Then

Response = acDataErrDisplay

Else

#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblVendors")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="tblVendors", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If

rst.AddNew
rst("VendorName") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

Exit_cboVendor_NotInList:
Exit Sub

Err_cboVendor_NotInList:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cboVendor_NotInList

End Sub

 

[Mr. B]

If you want to "allow" your users to enter just any value in this field you will have to set the "Limit to List" to No. There is now way that I am aware of that will allow you to "have your cake and eat it to". You can't have it both ways.

If you wanted to set the "Limit to List" to No to allow users to enter any value you could then set the "Row Source Type" property of the combo box to "Value List" and hard code a list of values that they could choose from. This would still mean that anyone could type in any value that wanted to for the field but all users would only see the hard coded values in the dropdown list. This would also mean that only users who knew about the "limit to list" not being in effect would even attempt to type in some other value.

Just my thoughts.

 

[331]

Thanks Mr. B. Guess you're right, can't have my cake and eat it too. Was hoping there was some VBA code that would change the "Limit to List" = "Yes".

I did what you suggested and changed "Limit to List" = "No". However, then the "On Not In List" Event code won't execute, and the User is not able to get the message "Do you want to add this item to the list?". The list is in it's own table and it too long to hard code the values in a Value List.

Any suggestions about where to move the block of code that is in the "On Not In List" Event so it will execute? Perhaps "After Update"?

Again, Thanks!

 

[RossWindows]

You can set LimitToList = false and then use the beforeupdate event to check if the value they entered is in your table of acceptable values.
Then you can do what you want from there.

 

[331]

Thanks so much RossWindows, I'll give this a try.

 

[fluid]

check if the value they entered is in your table of acceptable values

How could I check to see if the entry is already on the list, and if it isn't, add it.

 

[RossWindows]

You can use DLookup to check for the existence of a value and use an append query to add a value to the table.