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

    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:
    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?"
            Else
                '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
    Posts
    49
    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.

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

  3. #3
    Join Date
    Jun 2009
    Posts
    89
    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
    Posts
    49
    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.
    Sorry

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
  •