Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Combo Box list with new entries (MSAccess2002)

    I have a combo box that is linked to a table, in addition the user has the ability to input other entries not listed on the table. Can the new entries automatically be added to the combo box list?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    Should these new entries be added to the table? If so, you must write code in the On Not In List event of the combo box (we can help you with examples, if necessary). If the entries should be added to the list, but not to the table, it gets hairy...

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    Thanks Hans,
    I believe that i revised a code to fit my project. I will list it here for review, as there may be potential problems that i am aware of.
    My combo box is cmbOrigin, the table with the value list is Origin, field Origin

    Private Sub cmbOrigin_NotInList(NewData As String, Response As Integer)
    Dim ctl As Control
    Dim strSQL As String

    Set ctl = Me!cmbOrigin
    If MsgBox("The Origin entered, [" & [NewData] & "], is not on the list. Do you want to add it?", "" _
    & vbOKCancel) = vbOK Then
    ' Set Response argument to indicate that data is being added.
    Response = acDataErrAdded
    ' Add string in NewData argument to combo box recordsource.
    ' Substitute your recordsource for "[recordsource]", and
    ' your field names for "( fieldname )"
    strSQL = " INSERT INTO [origin] (origin)" & _
    " SELECT '" & (NewData) & "'"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    ctl.Value = NewData
    DoCmd.SetWarnings True
    Else
    ' If user chooses Cancel, suppress error message and undo
    ' changes.
    Response = acDataErrContinue
    ctl.Undo
    End If

    exit_cmbOrigin_NotInList:
    Exit Sub

    err_cmbOrigin_NotInList:
    ' I honestly do not remember why I had the following
    ' Error-trapping with numbers in here, but it works like a charm.
    If Err = 2113 Then
    Err = 0
    Resume Next
    Else
    MsgBox Str(Err)
    MsgBox Err.Description
    Resume exit_cmbOrigin_NotInList
    End If
    End Sub


    The next thing that i want to do is allow the user to make multiple selections from the list. Is this possible? I don't think that it is possible in a combo box. Access help indicates that it is possible with a List Box but I have not been able to get that to work. Any suggestions?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    You are correct that you can't select multiple items in a combo box.

    If you want to select multiple items in a list box, set the MultiSelect property of the list box to Single or Extended. Single means that each click selects or deselects an item. Extended means that you can select items with the mouse in combination with the Shift and Ctrl keys, exactly like you can select files in Windows Explorer. You need VBA code to process the selected items. Type ItemsSelected in the Visual Basic Editor (for example in the Immediate window) and press F1 to get help, with an example. Post back if you need more assistance.

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    Thanks for all of your help, your pointers were excellent! Thanks

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    I am attempting to adapt the following code to allow for multi select in my Listbox ListSender but it is not working. Once i get all the values how do i send that to the field in the table?

    Private Sub ListSender_Click()
    'Note: ListSneder is the name of the Listbox.

    Dim IntLoop As Integer
    Dim strList As String
    Dim strCompleteList As String

    'Ensures the variable strCompleteList is empty
    strCompleteList = ""

    'Sets up a loop. To be repeated the number of times as there are items in the list
    For IntLoop = ListSender.ListCount - 1 To 0 Step -1

    'Checks to see if the current list item is selected
    If ListSender.Selected(IntLoop) = True Then

    'If the current list item is selected, then the variable strList is assigned the value
    '(ItemData) of the list item that is the current number in the list
    'It then adds this value to the variable strCompleteList along with a comma
    'and a space to separate the selected items.
    strList = ListSender.ItemData(IntLoop)
    strCompleteList = strCompleteList & strList & "; "
    End If
    Next IntLoop

    'Final message box, containing the value of strCompleteList which contains all the
    'Selected items in the list box.
    MsgBox ("Your list contains the following selection " & strCompleteList)

    End Sub

    Hans - thanks for any help.

  7. #7
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    I combined and edited a couple codes to produce the above. It correctly mutliselects items in the list box and updates the CTI Table, Sender filed but i have the following problems:

    1. It does not update the current record displayed on the form but enters new entries into new records. How can i handle this?
    2. The entries that were originally highlighted when i go back to that record they are no longer highlighted on the form so on review the user does not know which items were selected previously.

    Any susggestions?

    Thanks.

    Private Sub cmdListSender_Click()

    Dim var As Variant
    Dim strList As String
    Dim strCompleteList As String
    Dim strsql As String
    Dim ctl As Control

    strCompleteList = ""
    Set ctl = Me!ListSender

    For Each var In Me.ListSender.ItemsSelected

    strList = ListSender.ItemData(var) ''
    strCompleteList = strCompleteList & strList & "; "

    Next

    MsgBox ("Your list contains " & strCompleteList)

    strsql = " INSERT INTO [CTI] (Sender)" & _
    " SELECT '" & (strCompleteList) & "'"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strsql
    ctl.Value = strCompleteList
    DoCmd.SetWarnings True

    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Combo Box list with new entries (MSAccess2002)

    Can you explain what you want this code to do? Its purpose is not clear to me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •