Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    30

    Unanswered: help with listbox code please

    I have a listbox [listregistered] that is populated based on selections from a group option [grpclasses] and a combobox [combodates]. I have it set up so I can move items from listregistered to a second listbox [listattended] with a command button. My problem is that when I click the commandbutton the items moves to listattended but is not removed fromlistregistered as I would like. However when I test the code using two stand alone list boxes without the cascading group option and combo box it works fine. I think it has something to do with my code for the combobox [combodates]. Below is my code for the combobox and the code for moving the list items. Any help would be appreciated.

    Private Sub Combodates_AfterUpdate()

    If Me.grpClasses = 1 Then
    Me.Listregistered.RowSource = "SELECT contacttable.id, contacttable.lastname, contacttable.firstname FROM contacttable WHERE contacttable.photo1register = #" & Format(Me.Combodates, "mm/dd/yyyy") & "#"

    End If

    End Sub

    ------------------------------------

    Private Sub SelectedItem(bolSelect As Boolean, Optional strCtl As String)
    On Error GoTo SelectedItem_ERR

    Dim ctl As Control
    Dim varItm As Variant

    DoCmd.SetWarnings False
    If strCtl <> "" Then
    ' set the ctl variable to the appropriate
    ' listbox
    If strCtl = "Listregistered" Then
    Set ctl = Listregistered
    Else
    Set ctl = Listattended
    End If
    ' If no selections is made in the appropriate
    ' listbox then tell the user and set the focus
    ' to that listbox
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please make a selection"
    ctl.SetFocus
    GoTo SelectedItem_EXIT
    Else
    ' Loop through each item selected
    For Each varItm In ctl.ItemsSelected
    ' Change the Selected field in the table to the value
    ' of bolSelect, where the value of dvtype in the table
    ' equals the one(s) selected in the listbox
    DoCmd****nSQL "UPDATE Contacttable SET SelectedItem = " & bolSelect & " " & _
    "WHERE ID = " & ctl.ItemData(varItm) & ";"
    Next varItm
    End If

    Else
    ' Change the Selected field in the table to the value
    ' of bolSelected for all dvtypes in the table
    DoCmd****nSQL "UPDATE Contacttable SET SelectedItem = " & bolSelect

    End If

    Listregistered.Requery
    Listattended.Requery

    SelectedItem_EXIT:

    On Error Resume Next
    DoCmd.SetWarnings True
    Exit Sub

    SelectedItem_ERR:

    MsgBox Error$
    Resume SelectedItem_EXIT

    End Sub

    -------------------------------

    Private Sub cmdMoveToListattended_Click()

    SelectedItem True, Listregistered.Name

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you issue a Requery instruction to both queries in the end of the process?
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    30

    requery at end of private sub SelectedItem

    Toward the end of Private Sub SelectedItem I have a requery for both listboxes. I am not sure if that suffices for what you are referring to.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, I did not noticed that (by the way, it's always easier to follow some listed code when it's properly indented: you should try to use the [ CODE][ /CODE] tags -without the space- when posting code).

    As far as I can understand, when you click the button cmdMoveToListattended you call the function SelectedItem() passing the name of Listregistered (that's "Listregistered" I presume) to it:
    Code:
    Private Sub cmdMoveToListattended_Click()
    
        SelectedItem True, Listregistered.Name
    
    End Sub
    This means that the condition:
    Code:
    If strCtl <> "" Then
    is always True, that the selection of the list Listregistered is always processed, but also that the selection of the list Listattended is never processed:
    Code:
            If strCtl = "Listregistered" Then
                Set ctl = Listregistered
            Else
                Set ctl = Listattended
            End If
    and finally that the instruction:
    Code:
    DoCmd****nSQL "UPDATE Contacttable SET SelectedItem = " & bolSelect
    is never executed.
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    30
    if this is what is happening what can I do at this point. I apologize for the code spacing.

Tags for this Thread

Posting Permissions

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