Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    46

    Unanswered: Listbox not refreshing on unbound form

    Hello all,

    I have an unbound Main form that contains an employee listbox. The listbox's rowsourcetype and rowsource are populated via code when a Group in the main form's search combo box is selected.

    I view a separate employee (unbound) detail form when an employee is selected (click event) from the listbox. On the employee detail form, employees may be added or deleted. In the activate event of the Main form, I am running the proc that sets the listbox's rowsourcetype & rowsource.

    My problem is that 50% of the time when the employee detail form is closed (after an add or delete), the listbox still shows the employee I just deleted or doesn't show the one I just added. I'm at a loss because I've tried repainting, refreshing, requerying, etc. - nothing works 100% of the time.

    Any help or thoughts would be greatly appreciated........

    Thanks,
    bmartin

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    requerying what? the form or the listbox?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2002
    Posts
    46
    both - here's my code that executes in the main form activate event:

    Public Sub EnrMain_ResetControls()
    EnrList.RowSourceType = "Table/Query"
    EnrList.RowSource = "SELECT Empname FROM Employees WHERE EmployeeGroupID = " & Frm_EM_GroupID & ""
    EnrList.Enabled = True
    EnrList.Requery

    DoCmd.RepaintObject acForm, "EnrMain"

    End Sub

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Move your listbox stuff after your repaintobject call.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2002
    Posts
    46
    No luck with that. I wouldn't think I would need to use RepaintObject at all.

    It's very haphazard with no real pattern as to when it refreshes correctly and when it doesn't. If I set a breakpoint in the activate event and trace it through the code, it seems to work correctly. Another thing I've noticed is that on the ones that I delete that continue to display on the main form, if I don't do anything, after a minute or two, the employee that was deleted will display with #deleted# on their line on the listbox.

    I was wondering if there was something I'm missing with the main form being unbound?

    Thanks

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Listbox

    It sounds to me like a refresh or setting the RowSource at the right time problem but the question is when and how to do it. I notice that you do set the RowSourceType and RowSource and was just wondering if you were to set a variant variable in your vba code (a Function), have the RowSourceType always set to a ValueList (or Table/Query - not setting it in code) and in code, populate the ComboBox or ListBox RowSource with the variant variable created from the Function separating the items with a ; in the code creating the variant variable and then set the RowSource of the (ComboBox or listbox?) with that variant variable. Thus your variant variable would look something like this after it's created (ColumnHeads = Yes) on the ComboBox: CustomerID;CustomerName;Address;City;State;Zip;3;J ohn Jones;123 Test Street;Madison;WI;55555;4;Bill Gates;222 SomeStreet;Cottage Grove;WI;53714. You would want to make sure that the column counts and widths match with those in the variant variable.

    I also wonder (if this is a Combobox) that setting the RowSource when the ComboBox gets the focus (On_Focus) would help although this would mean a fair amount of delay when that field got the focus and I don't really like doing this.

    With ComboBoxes/ListBoxes, we often do this where we'll open the recordset and populate a variant variable and then set the RowSource of the ComboBox/ListBox to that variant having the RowSourceType already set as a ValueList but we do this on loading the form or after another ComboBox is set to a certain value. The problem is if there is a large dataset populating the variant variable which will cut off after XXX number of rows added as their is a limitation on how much information you can populate the variant variable with.

    Hope this helps somewhat. I'd be curious on what you find as a solution.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2002
    Posts
    46
    That may work, but I'm a little skittish about using a variant as the data source seeing as how the listbox may get quite lengthy.

    Just for fun, I added a button on the form that's executing the same proc that's being executed in the Main form's Activate event (that's 50/50) & it refreshes perfectly from there.

    It's almost like the database is asleep when the activate event happens or it hasn't been refreshed. (or it's fallen & it can't get up)

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the On Activate event always firing? Or is it a timing thing? (The record hasn't been updated/deleted when the requery happens).

    In your code the Requery is redundant. When you set the RecordSource property a Requery is automatically done.

    Just for grins, what if you 'paused' the execution of the On Activate event for a second? Does the Requery always work? Try this:

    Code:
    Public Sub Pause(sinSeconds As Single)
    
        Dim sinStartTime As Single
        Dim sinFinishTime As Single
        
        Dim lngCount As Long
        
        'Get the current number of seconds since the beginning of the day
        sinStartTime = Timer
        sinFinishTime = sinStartTime + sinSeconds
        
        'Adjust for midnight
        If sinFinishTime > 86400 Then sinFinishTime = sinFinishTime - 86400
            
        Do Until Timer > sinFinishTime
            lngCount = 0
            DoEvents
        Loop
        
    End Sub

  9. #9
    Join Date
    May 2002
    Posts
    46
    That seems to work for the newly added employees to display, but not for the deleted ones.

    If I go to another app (like outlook) and come back, the #deleted# msg displays where the deleted employee was. It's almost like the first activate event isn't picking it up, but the 2nd one is. I know the activate event is firing because the delay is happening.

  10. #10
    Join Date
    May 2002
    Posts
    46
    An FYI update on my problem - I evidently have stumbled upon a solution. After the docmd.close statement in the exit button click_event of my employee detail form, I added the line:

    Forms!EnrMain!EnrList.SetFocus

    EnrList is the listbox that wasn't refreshing on my main form. This evidently forces the activate event to execute correctly for some reason.

    Thanks to everyone for your help!

    Brian

Posting Permissions

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