Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009

    Unanswered: My first list box and it's not great for epileptics

    Hi. I'm trying to make a form that has a list box on the left of all the records. So the user clicks on a record in the list, and all the details come up on the right.

    Trouble is, If i set the list box's control source to ID, then i cant' change it by clicking in the list box (i have to use record navigation buttons).

    So the control source is blank, and I'm using the following after update code:
    Private Sub lstEmployees_AfterUpdate()
         Dim rs As DAO.Recordset
        If Not IsNull(Me.lstEmployees) Then
            'Save before move.
            If Me.Dirty Then
                Me.Dirty = False
            End If
            'Search in the clone set.
            Set rs = Me.RecordsetClone
            rs.FindFirst "[ID] = " & Me.lstEmployees
            If rs.NoMatch Then
                MsgBox "Not found: filtered?"
                'Display the found record in the form.
                Me.Bookmark = rs.Bookmark
            End If
            Set rs = Nothing
        End If
    End Sub
    This works fine, but it's very annoying because the list refreshes every time you click on a different record, causing a "flashing" effect as the list goes white for a split second and then repopulates.

    Is there a way I can use a list box bound to ID, and still be able to change it?

  2. #2
    Join Date
    Nov 2009
    I had a similar setup for my first database... this brings back memories.

    My Control source for the list box was based on a query as there was a search facility to narrow down the list in the listbox:

    SELECT Search.Item, Search.Location, Search.Practical, Search.Campus, Search.notes, search.quantity FROM Search ORDER BY Search.item;

    But you can just use the standard make listbox wizard to do this bit.

    Then on the OnClick method I had the following coding:

    DoCmd.SearchForRecord acTable, "Inventory", acFirst, " [item] = '" & List49 & "' And [Location] = '" & List49.Column(1) & "'"

    Bit of Explanation needed:

    acTable, "Inventory", = Looks at a table, the table is called "Inventory"
    acFirst, = It starts searching from the beginning of the list (first record)
    " [item] = '" & List49 & "' And [Location] = '" & List49.Column(1) & "'"

    [Item] is the name of my item which is shown on the right where the details are.

    As there could have been an Item with the same name stored in a different location I had to add the [Location] part, but you can ignore that.

    I think how I got this was I made 2 listboxes using the wizard, 1 that gathered all the data values from the query (option1) and the other that controls the form (option3). I then copied and pasted the properties of 1 into the other (I think I copied Option1's Rowsource coding into listbox into Option2) and then tweaked it to suit, although I seem to remember it didn't need much tweaking (only to include Location).

    This was a while ago so might have missed something out, but see how it goes.

    Last edited by pb190; 12-10-09 at 08:17.

  3. #3
    Join Date
    Jun 2009
    This is a good solution but unfortunately still has the same problem. Becuase it's still searching for a record, the list is refreshed and causes the flashing.

    I basically want a list box to work just like the datasheet part of a split form, where I select the row and all the details show in the form. Only problem is i need the datasheet to be on the left, which isn't possible with split form.

  4. #4
    Join Date
    Nov 2009
    Ah Soz,

    Just tried it on my work computer and the refresh is very obvious, looks like my home computer is better than my work one :s

    I just had a play with my other listboxes in other databases and it seems that they all flash when there is a selection made on the list, even if there are no other processes occuring after you click on them. Just make a few quick selections on a brand new listbox and you will see some of them flash, I think because there is a find record that the flash is just slightly longer.

    So I am afraid I don't think i can help.

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 15
    Use a SubForm in DataSheet view instead of a list, it's very easy and powerful, as you can sort the list etc. See the attached example.
    Attached Files Attached Files
    Have a nice day!

Posting Permissions

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