add items to list if they are not on the list

  • Thread starter Thread starter Gianna
  • Start date Start date
G

Gianna

How can a user in a form, add an item to a List Box if
that item is not present on that List Box. In the form I
have the Limit To List set to no, the help in 2000 says I
must create an event procedure and use the notonlist
option. How and where do I do that? Used to be you set
limit to list No and you were on your way. Thanks for any
help.
 
Hi, Gianna.

If you set Limit to List to No, a user can add anything
that he wants to into the control, but the value will not
be added to the combo box' Row Source.

If you want the value added to the table so that it will
become a new combo box item, the value must be added via a
NotInList event procedure such as below. In this case,
set the LimitToList property to Yes. A new item will
trigger the procedure. Be sure to set up a reference to
the DAO library if you don't have it now.

Private Sub cboBidType_NotInList(NewData As String,
Response As Integer)
' When copying to new procedure, change strTblName,
strThing, and fieldname
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
Dim strTblName As String

strTblName = "tblBidTypeList" ' Your Lookup Table Name
strThing = "Bid Type" ' The type of "thing" in
the Lookup table
strMsg = "'" & NewData & "' is not in the lookup list
stored in " & strTblName & vbCrLf & vbCrLf
strMsg = strMsg & "Add to lookup table? "

' Ask the user if he'd like to add the new value to the
lookup table
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New " &
strThing & "?") = vbNo Then
' Don't add it, return user to the combo box
Response = acDataErrContinue
Else
' Create a recordset clone, and add the new record
Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!BidType = NewData
rs!BidTypeID = DMax("[BidTypeID]", "BidTypeList") + 1
rs.Update

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

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub

HTH
Kevin Sprinkel
 
After seeing JulieD's post, I was reminded that the code I
posted was a modification of Dev Ashish' posting referred
to in Julie's response. I should have included his header
in both my application and in my posting:

'************ 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
'

Kevin Sprinkel
 
Back
Top