?
Solved

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

Posted on 2004-11-04
22
Medium Priority
?
1,295 Views
Last Modified: 2008-01-09
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
                    btnExit.SetFocus
                    Exit Sub
            End Select
        Next intCount
    Else
        MsgBox "Please select an MS Project to delete from the database.", vbOKOnly, "Select Project"
        lstDeleteProject.SetFocus
        Exit Sub
    End If
    Call LoadDeleteList
    Me.Repaint
    Me.Refresh
    btnExit.SetFocus
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
    rsRecordset.MoveFirst
    'load the returned data in the listbox
    Do While Not rsRecordset.EOF
        lstDeleteProject.AddItem (rsRecordset!PROJ_NAME)
        rsRecordset.MoveNext
    Loop
    lstDeleteProject.Requery
    rsRecordset.Close
    cnConnection.Close
    Set rsRecordset = Nothing
    Set cnConnection = Nothing
End Sub
0
Comment
Question by:1burke
  • 11
  • 8
  • 3
22 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12495695
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:

lstDeleteProject.Requery

To update the contents of your listbox.
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12495944
Is "DeleteFromDatabase" a function?  And if so, can you post the code for this function?
0
 

Author Comment

by:1burke
ID: 12496076
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:1burke
ID: 12496138
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.


Syntax

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


Remarks
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"

0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 500 total points
ID: 12496150
What about doing this then:

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

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

Author Comment

by:1burke
ID: 12496361
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?
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12496461
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.

0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12496500
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)

0
 

Author Comment

by:1burke
ID: 12496509
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)?
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12496988
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?
0
 

Author Comment

by:1burke
ID: 12497005
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.
0
 
LVL 6

Assisted Solution

by:Plamodo
Plamodo earned 500 total points
ID: 12497207
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)

0
 

Author Comment

by:1burke
ID: 12497464
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.
0
 

Author Comment

by:1burke
ID: 12497539
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!
0
 

Author Comment

by:1burke
ID: 12497583
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.
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12497765
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) :

http://www.experts-exchange.com/Applications/MS_Office/Microsoft_Project/Q_21194807.html

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!
0
 

Author Comment

by:1burke
ID: 12498767
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?
0
 

Author Comment

by:1burke
ID: 12498941
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.
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12499632
will_scarlett7.. check out this question:

http://www.experts-exchange.com/Databases/MS_Access/Q_21195325.html

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:

http://www.experts-exchange.com/Databases/MS_Access/Q_21194719.html#12498941

0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12499641
oh wait.. 1burke, you *did* split the points on this question.. whoops.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12502177
Yes... It looks like it worked...
0
 

Author Comment

by:1burke
ID: 12502411
Okay, thanks for the info.  I'll try to get the other one canceled.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question