Listbox/Textbox doesn't refresh on form

[Adrianna]

HELP! I have tried using a listbox and a textbox to display table names as they are exported to Excel. At the end of a successful export, the table name is either added to the value list in the listbox, or added to the end of a growing textbox ( I prefer the listbox, but it's not important). This is designed to show the users the status of the export without requiring them to click OK on a MsgBox. The issue is that the code loops though and on completion when a MsgBox pops up to say everything was successful, the textbox/listbox adds all table names at once.

I tried to put the individual MsgBoxes back in and amazingly enough, the Text/Listbox performs it's incremental growth of table names. I obviously don't want bother users with all 32 individual MsgBoxes. But Me.Form.Refresh doesn't even do the trick.

Any Ideas?

Okay...I thought that I would add that without the message boxes..the code seems to cause write errors in the proceedure that is looping through. SOMETIMES it runs without a hitch and other times, it thinks it's over writing itself. If I 'step through' the code in the debugger...I NEVER get the errors.

 

[yeatmanj]

Can you post the code here?

 

[Adrianna]

I doubt that the forum would be very happy if I tried to post the code here. THe code calls on several modules and in total, the code is very long.

I can explain....all I'm doing is running Me.ListCompleted.AddItem strCompleted
after returning from the Function that actually writes the recordset from the dynamic queryrecordset (openning excel, checking to seeif the file exists, checking to see if the worksheet exists, clearning existing data, writing and formatting column headings, etc).
So, when it returns from the function I just add the table name of the current recordset to the Value list (also tried a textbox), then I set my recordset = Nothing and start the loop again.

I will mention that EVERYTHING runs exactly like it is supposed to if I use a break ANYWHERE in the code. I'm assuming that's because it slows the process down. Without a break...nothing appears until all loops have been completed and the event's completion MsgBox appears.

 

[yeatmanj]

I have had this problem before. I am looking back at some of my code to see if I can remember how I resolved it. Will post solution if I can find it.

Scratch this (just reread your post): Can you tell where in the processes it is over running itself? Does the error always appear in the same place?

 

[yeatmanj]

You may need to insert a function at the end of the loop that causes it to pause for a specified period of time (maybe 1 second).

I am assuming that you have stepped through the loop. Did you use On Error Resume Next in the code? If you did you may have to REM it out during the step through.

 

[Adrianna]

Gggrrr..second time I've lost a message here today just after typing it.....*sigh*

Anyway, I would like to know how to put a pause into the code without requiring user intervention. Resume On Error is in affect, but the program is truely working 100% correctly during step through, as I have checked the results. The code actually runs fine sometimes...although the data is static and nothing should suddenyl cause errors.

The occurrances are completely random. I have tracked the points in the loop at which they occur and they are rarely the same.

Wow...I can't remember what I wrote the first time I formulated this response, but my brain is currently fried. I think I need to call it a night.

If you have any ideas, please let me know!

 

[yeatmanj]

I remembered that I do have a listbox that updates after each archiving of a record. I used the me.repaint method. There are two options here. form.Repaint and DoCmd.RepaintObject [objecttype, objectname].

I remembered that me.refresh wasn't working to refresh the listbox. Repaint did work so I went with that.

 

[Adrianna]

DoCmd.RepaintObject for my form didn't seem to take, but the form.repaint worked like a charm.

I guess I always used to use refresh to update everything...I'd never used repaint before, but it works great. Thanks!