Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox is null when using code to select a row (2002/SR-1)

    I'm using this code which is attached to a command button to filter a form and make it visible.

    <pre>Dim frm As Form
    Set frm = forms!frmMaster

    If lstInDefault.ItemsSelected.Count = 0 Then
    MsgBox "You must make a selection!", vbExclamation, "Nothing Selected"
    ElseIf lstInDefault.ItemsSelected.Count > 1 Then
    MsgBox "You selected multiple records, please select just one." _
    , vbExclamation, "Too Many Selections"
    Else
    frm!cmdClose.Tag = "InDefault"
    frm.Filter = "[DocNum]=" & "'" & Me.lstInDefault.Column(0) & "'"
    frm.FilterOn = True
    Me.Visible = False
    frm.Visible = True
    End If

    Set frm = Nothing</pre>


    Then when the user closes the form that is made visible, I have code that requeries the
    listbox and selects the row that was originally selected from the listbox. The problem is
    if I click the command button to execute it again, the value of Me.lstInDefault.Column(0)
    from the code above is Null. If I actually click to make a selection everything works fine.

    Any suggestions??

    Thanks,
    Don

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Listbox is null when using code to select a row (2002/SR-1)

    The code you posted implies the listbox is a multi-select listbox. This is a dumb question, but if you want only one item to be selected why is listbox multi-select?? You can simplify matters by setting listbox multi-select property to "None". Sample code for single select listbox:
    <pre>Dim frm As Form
    If IsNull(Me.lstCustomers) Then
    MsgBox "Please select name from list.", vbExclamation, "NAME NOT SELECTED"
    Me.lstCustomers.SetFocus
    Else
    DoCmd.OpenForm "Customers" 'just in case not already open
    Set frm = Forms![Customers]
    Me.Visible = False
    With frm
    .Filter = "[CompanyName] = " & Chr(34) & _
    Me.lstCustomers.Column(2) & Chr(34)
    .FilterOn = True
    .Visible = True
    End With
    End If
    Set frm = Nothing</pre>

    NOTE: Am using Northwind database for test purposes, opening Customers form filtered by Company Name. My other question is, how are you resetting value after listbox on 1st form is requeried? Here is example of how this may be done from 2nd form

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox is null when using code to select a row (2002/SR-1)

    Thanks for the reply Mark.

    I'm using a multi-select listbox because there are many other command buttons to perform other operations like reporting
    and such which allows the user to select multiple records. When the 2nd form is closed I have code that checks which row
    is selected in the hidden with the listbox and store it in a variable, then I perform a requery on the listbox, and then use the
    variable to select row that was originally selected and then make the 1st form with the listbox visible again.

    <pre>Private Sub cmdClose_Click()
    Dim lngRowSelected As Long

    If Me.cmdClose.Tag = "InDefault" Then
    lngRowSelected = RowSelected
    Me.cmdClose.Tag = ""
    forms!frmInDefault!lstInDefault.Requery
    forms!frmInDefault!lstInDefault.SetFocus
    forms!frmInDefault!lstInDefault.Selected(lngRowSel ected) = True
    forms!frmInDefault.Visible = True
    Me.Visible = False
    Else
    forms!frmSwitchBoard.Visible = True
    Me.Visible = False
    End If

    End Sub</pre>


    <pre>Public Function RowSelected() As Long
    RowSelected = forms!frmInDefault!lstInDefault.ListIndex
    End Function</pre>


    I use the Function RowSelected to get the ListIndex of the listbox before I perform the requery.
    I'm having some trouble trying to explain this. I'll have to sleep on it and try and explain my situation
    better so that someone can help me with this.

    Thanks for taking the time Mark.

    Don

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox is null when using code to select a row (2002/SR-1)

    lstInDefault.Selected(lngRowSelected) = True
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Listbox is null when using code to select a row (2002/SR-1)

    I'm not a big expert on multi-select listboxes, getting all those properties to work together in synch can be a hassle. In a few cases where I used them usually had 2 listboxes, one single-select, one multi-select, and toggled visible properties for each on & off as required. This is not always practical.

    Tried changing my listbox to multi-select and used code similar to yours for 2nd form's close event & had same problem, the row would be highlighted but when tried to apply filter again got an Invalid Use of Null error (ie, no value for column property). You also need to reset ListIndex property in addition to selecting item. Ex:

    forms!frmInDefault!lstInDefault.SetFocus
    forms!frmInDefault!lstInDefault.ListIndex = lngRowSelected
    forms!frmInDefault!lstInDefault.Selected(lngRowSel ected) = True

    After adding this line to code the function appeared to work correctly.

    HTH

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox is null when using code to select a row (2002/SR-1)

    Mark,

    Resetting the listindex of the listbox works great! I wouldn't have thought of that!
    Thanks so much for your help! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Don

Posting Permissions

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