Refresh contents of a list box displayed on form, using VBA

I have a simple set of routines that allows me to select multiple MS Project files saved in an Access DB format and delete them.  On loading the form (all of this is done in Access) a list box is loaded showing the name of all the MS Project files saved in the DB.  I can select multiple ones and delete them from the DB.  The problem I having is getting the list box on the form to reload and refresh showing the updated list without the ones I just deleted.  Below are the two functions I use - 1st is the procedure called when clicking the "Delete" button; next is the routine I call to reload the list box:

[as you should be able to see, I try the repaint and refresh actions, but neither of these work.  I have actually put a debug stop in the coding right after the point where the selected MS Projects are actually deleted from the table and I can confirm that they are deleted.  When I do this, then once the load list box routine runs, I get the proper list in the list box.  This leads me to believe that there is some time delay between when the deletions are "committed" in the DB that is longer than it takes to requery for the list box info.  Thus, for some reason, I'm getting "ghost" records.  Don't know if this is the case though.  Also, I tried using a Table/Query binding and using the refresh method and got the same result.  Plus, I'd like to stick to VBA manipulation of these controls.  Any ideas, help are welcome.]

Private Sub cmdDeleteProj_Click()
    Dim intCount As Integer 'counter
    Dim varItem As Variant 'for loopping through the items selected in the listbox
    Dim rtnDelete As Variant 'returns 3 options from the delete dialog box
    Dim astrDeleteProjects() As String 'array to hold names of selected items
    If lstDeleteProject.ItemsSelected.Count > 0 Then
        'Establish 1) the number of selected items, 2) size the array to the number of selected items, _
                   3) fill the array with the MSP_PROJECT!PROJ_NAME value from the selected items
        intCount = lstDeleteProject.ItemsSelected.Count
        ReDim astrDeleteProjects(intCount)
        intCount = 0
        For Each varItem In lstDeleteProject.ItemsSelected
            astrDeleteProjects(intCount) = lstDeleteProject.Column(0)
            intCount = intCount + 1
        Next varItem
        'Loop through the array of selected items: 1) ask to delete, if No, ignore and go to next, 2) if Yes, delete _
                                                   3) if Cancel, exit sub and re-load the listbox
        For intCount = 0 To lstDeleteProject.ListIndex - 1
            strFileName = astrDeleteProjects(intCount)
            strFilePath = Application.CurrentProject.FullName
            rtnDelete = MsgBox("Are you sure you wish to delete the MS Project file " & vbCr & _
                "'" & strFileName & "' from the PMA 202 database", vbYesNoCancel, "Delete Project?")
            Select Case rtnDelete
                Case 6 'Yes for Delete
                    DeleteFromDatabase Name:="<" & strFilePath & ">\" & strFileName & "", FormatID:="MSProject.MDB8"
                Case 2
                    Call LoadDeleteList
                    Exit Sub
            End Select
        Next intCount
        MsgBox "Please select an MS Project to delete from the database.", vbOKOnly, "Select Project"
        Exit Sub
    End If
    Call LoadDeleteList
End Sub

Private Sub LoadDeleteList()
    Dim cnConnection As ADODB.Connection
    Dim strConnection As String
    'Set connection info
    Set cnConnection = New ADODB.Connection
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\PMA202_Test.mdb;User Id=admin;Password=;"
    cnConnection.Open strConnection
    cnConnection.CursorLocation = adUseClient
    'set recordset Info
    Dim rsRecordset As ADODB.Recordset
    Set rsRecordset = New ADODB.Recordset
    lstDeleteProject.RowSource = "" 'clear any previous items in the listbox
    'get the recordset data
    With rsRecordset
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open "Select PROJ_ID, PROJ_NAME From MSP_PROJECTS Order By MSP_PROJECTS.PROJ_NAME", CurrentProject.Connection
    End With
    'load the returned data in the listbox
    Do While Not rsRecordset.EOF
        lstDeleteProject.AddItem (rsRecordset!PROJ_NAME)
    Set rsRecordset = Nothing
    Set cnConnection = Nothing
End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maybe I am reading your code wrong, but why don't you just set the RowSource of you ListBox to "Select PROJ_ID, PROJ_NAME From MSP_PROJECTS Order By MSP_PROJECTS.PROJ_NAME;" and then after you delete records of modife the underlying table at all simply do a:


To update the contents of your listbox.
Is "DeleteFromDatabase" a function?  And if so, can you post the code for this function?
1burkeAuthor Commented:
will_scarlet7:  I originally did do that, but I had the same problem (I also had some other problems I didn't mention that make the VBA routine a better choice).  I can't imagine why, but for some reason the requery option didn't work then and it doesn't work now.  Another clue is that when I had the rowsource = the SQL statement, what would appear in the list box after a deletion was "#deleted#" for the item(s) deleted.  Only after another form event (hitting another button for something, etc.) did the list box actually requery.  That caused me to try Repaint, etc. but nothing so far has worked.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

1burkeAuthor Commented:
Plamodo:  [below is info on the "DeleteFromDatabase" method]  This is a good track to follow because I don't really understand how all this works.  My assumption at this point is that the actual delete action performed on the DB using the MSProject "DeleteFromDatabase" method is somehow different that a normal deletion in Access VBA.  But again, it does actually delete (a full cascading delete).  Again, from my first post, if I stop the routine half-way (by debugging) between the deletion being committed and the list box reloading, the list box actually reloads correctly.  I wonder now if it has something to do with refreshing the tables themselves?  Is there such a thing?

The "DeleteFromDatabase" is a built-in function of the MS Project api.  Below is some junk about it from the Object Browser:

DeleteFromDatabase Method

Deletes a project stored in a database.


expression .DeleteFromDatabase(Name, UserID, DatabasePassWord, FormatID)
expression     Optional. An expression that returns an Application object.

Name Required String. The name of the source file or data source to open, and the name of the project to delete from the database.

UserID    Optional String. A user ID to use when accessing the database.

DatabasePassWord    Optional String. A password to use when accessing the database.

FormatID    Optional String. The file or database format. If Project recognizes the format of the file specified with Name , FormatID is ignored. Can be one of the following format strings:

Format String Description
"MSProject.mpd" Project database
"MSProject.odbc" ODBC database
"MSProject.mdb" Microsoft Access database

The Name argument must contain a file name string, or an ODBC data source name (DSN), and the project name string. The syntax for a data source is <DataSourceName>\Projectname. The less than (<) and greater than (>) symbols must be included and a backslash ( \ ) must separate the data source name from the project name. The DataSourceName itself can either be one of the ODBC data source names installed on the computer, a file DSN, or a path and file name for a file-based database.

In the following examples, [My Documents] is the full path of your My Documents folder, and [Program Files] is the full path of your Program Files folder:

"<Corporate SQL Database>\Factory Construction"

"<[My Documents]\PROJECT1.MDB>\System Roll-out Plan"

"<[Program Files]\Common Files\ODBC\Data Sources\Projects Database.dsn>\Project X"

What about doing this then:

Me!lstDeleteProject.RowSource = ""
Me!lstDeleteProject.RowSource = "Select PROJ_ID, PROJ_NAME From MSP_PROJECTS Order By MSP_PROJECTS.PROJ_NAME;"

This should technicaly set the Listbox to blank, refresh, then re-pull in the records and refresh again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1burkeAuthor Commented:
will_scarlet7:  Tried it and still get exactly the same problem.  In fact, I am back to the previous issue that now the Project deleted shows up as "#deleted" in the list box -- but only after a 30 second delay.  I'm using a MS Project api command, "DeleteFromDatabase", so maybe there's something extra that has to be done to refresh a control this way?
I'm thinking your API call might be a problem.

What about deleting from your table like this (replacing the API delete) :

currentdb.execute("DELETE * FROM [TableName] WHERE [FieldName] = '" & aString & "'")

You'll have to insert your own [TableName], [FieldName] and aString values.  If that makes a difference, I'd then head in the direction willscarlet has mentioned.

This syntax is actually better.. handles apostrophes in strings :

dim strQuote as string
strQuote = chr$(34)

currentdb.execute("DELETE * FROM [TableName] WHERE [FieldName] = " & strQuote & aString & strQuote)

1burkeAuthor Commented:
Plamodo:  That's a last resort (I'm checking on some Project Developer forums also).  If so, there are some 20 tables in a standard Project DB model.  What is the best way to perform a quick cascading deletion based on the top-level primary key (PROJ_ID)?
Oh ... are you saying that you have 20 cascading tables that need associated data deleted when a piece of data is deleted from this particular list table?
1burkeAuthor Commented:
Yep!  That's why I was hoping to use the api "DeleteFromDatabase" method - it takes care of all that for me.  It's no big deal, I can work around it somewhat.
Well.. the obvious is to have :

currentdb.execute("DELETE * FROM [Table1] WHERE [Field1]=" & somevalue")
currentdb.execute("DELETE * FROM [Table2[ WHERE [Field2]=" & somevalue")

... 20 times over...

Or you could set up all your table relationships to have "Cascade Delete Related Records" checked.. but I've been warned that this may delete more than you bargain for unless you know whats what.

Or you could put in a delay between the deleting of records and when you refresh your listbox.

The api is the wildcard here.  I also do things the way willscarlet7 suggested.  I can't imagine why it wouldn't work for you except that you have an api command handling table functions instead of built in access commands.  Seems to me that is a probable source of your troubles.  (but still just guessing)

1burkeAuthor Commented:
That's okay.  I put my original assumption to the test.  I added a Timer routine and set it for 5 seconds.  The user can't tell that the delete action has already taken place and I hog the application with the hourglass icon.  At the end of the 5 seconds, the list box resets fine.
1burkeAuthor Commented:
Plamodo:  Ah, I see you suggested my solution while I was implementing it but before I had a chance to see your last points.  I'll go ahead and accept it.

will_scarlet7:  I also took your advice on the RowSource setting.  It does avoid the other problems I was having.  I'll split the points.

Boy this is fun, this is the 2nd thread I've started today, and only on my first day!
1burkeAuthor Commented:
OOps, it would'nt let me split, said I was denied for this question.  Don't know why.

Plamodo - visit the MS Project page and look for my posting there on the same subject.
Thats the same question, right?  Maybe will-scarlet should post a comment there so he can collect his share of the split (if not, I'll post you some points, will) :

1Burke:  If you haven't already tried, you may be able to delay for less 5 seconds and still have things run up to scratch.  Often a lot of these things only need a half second to process... might be worth playing around with.

Thanks for the points!
1burkeAuthor Commented:
I think I already accepted one of his answers.  Unless he says different, I think it's closed.  Can an official moderator step in to help out?
1burkeAuthor Commented:
Plamodo & will_scarlet7:  If you guys can square things away about who already has which points, I can award the last set to whomever didn't get the first set.
will_scarlett7.. check out this question:

1burke:  You can post a question in community support asking them to delete your other question and to get your points refunded.  Quote this question link:

oh wait.. 1burke, you *did* split the points on this question.. whoops.
Yes... It looks like it worked...
1burkeAuthor Commented:
Okay, thanks for the info.  I'll try to get the other one canceled.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.