Solved

In MS-Access 2003, how do I repaint a form

Posted on 2011-02-17
12
516 Views
Last Modified: 2012-06-27
EE Access Experts:

Please see the attached screen shots -- 2 each. I have a form used to send automated e-mail invoices. In the ListBox on the left is a 3 column list. As each e-mail invoice is successfully e-mailed, the right most column in the ListBox (the 3rd column) titled EMailInvoiceDateTime is updated with the Datetime the message was sent. But that doesn't show up in the ListBox (see the Before screen shot) unless I move the focus to a different window or minimize the main application frame and "restore" it again (see the After screen shot).

It seems certain to me that there must be a VBA language statement that I can use to repaint the form just like when I move and restore the focus. But I can't figure out what it is. Can one of you tell me the name of the method I need to accomplish this?

I'll add the attachment image files after I save this question.
0
Comment
Question by:Ted Palmer
  • 8
  • 4
12 Comments
 

Author Comment

by:Ted Palmer
ID: 34917259
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34917271
try
me.repaint
0
 

Author Comment

by:Ted Palmer
ID: 34917292
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34917297
in some cases, you will need the

me.repaint
Doevents

depending on the codes running
0
 

Author Comment

by:Ted Palmer
ID: 34917728
capricorn1:

I tried both me.repaint with and without the Doevents and it did not work. Please see that attached code snippet. I am trying to display the datetime stamp on each line in the list box as it is processed, and not wait till the entire list is processed to show that imformation. I like it as feedback to the user.

Thank you,
Ted Palmer
For i = 1 To ctlList.ListCount - 1
        If Not boolSendEmailStarted Then
            Exit For
        End If
        ctlList.Selected(i) = True '<-- Discovering this was a pain in my behind. 2010-11-07 Ted Palmer
        For j = 0 To ctlList.ColumnCount - 1
            If j = 0 Then
                'Debug.Print "j = " & CStr(j) & "; ctlList.Column(j) = " & ctlList.Column(j)
            ElseIf j = 1 Then
                strOrderID = ctlList.Column(j)
            ElseIf j = 2 Then
                strTrackingNo = ctlList.Column(j)
            ElseIf j = 3 Then
                strEMailInvoiceDateTime = CStr(ctlList.Column(j))
            End If
        Next j
        'Me.Requery <-- It didn't do any good. Maybe next time. I wanted to refresh the data in the ListBox. Ted Palmer 2010-11-16.
        boolSendEmailResult = False
        If Len(strTrackingNo) > 5 Then
            boolSendEmailResult = EMailInvoice("W", strTrackingNo, strOrderID, "none") '<-- Sends the e-mail invoice #.
'        Else '2011-01-29 Ted Palmer: Suspend all error messages while sending e-mail per Jim Beachler.
'            MsgBox "For INVOICE # " & strOrderID & " no Tracking Number has been assigned. " _
'                & "So therefore the order has not been shipped, and an E-Mail Invoice can not be sent.", , "Title: Automatic Sending of E-Mail"
        End If
        If boolSendEmailResult Then
            strSQLupdate = "UPDATE ShippedPackageInfo SET EMailInvoiceDateTime = #" & CStr(Now()) & "# WHERE OrderID = '" & strOrderID & "';"
            Set HW = CurrentDb
            Set qdfUpdate = HW.CreateQueryDef("", strSQLupdate)
            qdfUpdate.Execute
            'ctlList.Column(j - 1) = CStr(Now()) '<-- Error message says "Object required". Nice try but it didn't work.
        End If
        Me.Repaint
        DoEvents
    Next i

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34917824
try requerying the listbox

me.ctlList.requery
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Ted Palmer
ID: 34918040
capricorn1:

I get the error message in the attached screen shot. The object ctlList is declared as Dim Control. I changed the declaration to Dim ListBox and that didn't help. I got the same error message.

Thank you
Ted Palmer
MethodOrDataMemberNotFound.bmp
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34918109
can you upload a .mdb copy of the db
0
 

Author Comment

by:Ted Palmer
ID: 34918252
capricorn1:

I can't upload the whole mdb file. It has too much client proprietary information. I have uploaded the source code for the whole function. I really appreciate your help on this.

Ted Palmer
'2010-11-01 Ted Palmer
Function btnSend_Click_Func() As Boolean
    On Error GoTo Error_Exit
    Dim dbsHWbe, HW As Database
    Dim tblPendingShipments As TableDef
    'Dim rstPendingShipments, rstPackages, rstWholesalePackages, rstShippedPackageInfo As Recordset
    Dim rstShippedPackageInfo As Recordset
    Dim rstAutoEmailInvoice As Recordset
    Dim qdfUpdate As QueryDef
    Dim strSQLupdate As String
    Dim strTrackingNo, strOrderID, strEMailInvoiceDateTime As String
    Dim boolSendEmailResult As Boolean
        boolSendEmailResult = False
    Dim i, j, k As Integer
    'Dim ctlList As Control
    Dim ctlList As ListBox

    
    '2010-11-07 Ted Palmer - save iteration sequence for testing.
    ' Enumerate through all columns of rows.
'    For i = 1 To ctlList.ListCount - 1
'        ctlList.Selected(i) = True
'        For j = 0 To ctlList.ColumnCount - 1
'            Debug.Print ctlList.Column(j)
'        Next j
'    Next i
 
    ' Return Control object variable pointing to list box.
    Set ctlList = Forms!EMailAdmin!lbEmailTheseOrders
    Set dbsHWbe = CurrentDb
    Set rstShippedPackageInfo = dbsHWbe.OpenRecordset("ShippedPackageInfo")
    Set rstAutoEmailInvoice = dbsHWbe.OpenRecordset("AutoEmailInvoice")
    
    If Not rstAutoEmailInvoice!EnableSendEmail Then
        MsgBox "Sending of E-Mail has been suspended. To restore Sending of E-Mail, " _
                & "set column EnableSendEmail = Yes in table AutoEmailInvoice.", , "Title: Automatic Sending of E-Mail"
        GoTo Func_Exit
    End If

    For i = 1 To ctlList.ListCount - 1
        If Not boolSendEmailStarted Then
            Exit For
        End If
        ctlList.Selected(i) = True '<-- Discovering this was a pain in my behind. 2010-11-07 Ted Palmer
        For j = 0 To ctlList.ColumnCount - 1
            If j = 0 Then
                'Debug.Print "j = " & CStr(j) & "; ctlList.Column(j) = " & ctlList.Column(j)
            ElseIf j = 1 Then
                strOrderID = ctlList.Column(j)
            ElseIf j = 2 Then
                strTrackingNo = ctlList.Column(j)
            ElseIf j = 3 Then
                strEMailInvoiceDateTime = CStr(ctlList.Column(j))
            End If
        Next j
        'Me.Requery <-- It didn't do any good. Maybe next time. I wanted to refresh the data in the ListBox. Ted Palmer 2010-11-16.
        Me.ctlList.Requery
        boolSendEmailResult = False
        If Len(strTrackingNo) > 5 Then
            boolSendEmailResult = EMailInvoice("W", strTrackingNo, strOrderID, "none") '<-- Sends the e-mail invoice #.
'        Else '2011-01-29 Ted Palmer: Suspend all error messages while sending e-mail per Jim Beachler.
'            MsgBox "For INVOICE # " & strOrderID & " no Tracking Number has been assigned. " _
'                & "So therefore the order has not been shipped, and an E-Mail Invoice can not be sent.", , "Title: Automatic Sending of E-Mail"
        End If
        If boolSendEmailResult Then
            strSQLupdate = "UPDATE ShippedPackageInfo SET EMailInvoiceDateTime = #" & CStr(Now()) & "# WHERE OrderID = '" & strOrderID & "';"
            Set HW = CurrentDb
            Set qdfUpdate = HW.CreateQueryDef("", strSQLupdate)
            qdfUpdate.Execute
            'ctlList.Column(j - 1) = CStr(Now()) '<-- Error message says "Object required". Nice try but it didn't work.
        End If
        'Me.Repaint
        'DoEvents
    Next i

    rstShippedPackageInfo.Close
    rstAutoEmailInvoice.Close
    dbsHWbe.Close
    
    GoTo Func_Exit
    
Error_Exit: '2011-01-29 Ted Palmer: Keep this message. Sending of the list has already been stopped.
    MsgBox "#6 Exception thrown in function btnSend_Click_Func() in form EMailAdmin VBA for INVOICE # " & strOrderID & ". MS-Access error message follows: " & Err.Description
Func_Exit:
    Me.btnClose.SetFocus
    Me.btnSend.Enabled = False
    Me.lblInstructions.Caption = "The Send button has been disabled to prohibit sending the same list a second time. " _
    & "To attempt to send any invoices that were not sent this time, close and reopen this screen to get a new list. "
    Me.btnSend.ForeColor = -2147483630 'BLACK Bold
End Function 'btnSend_Click_Func()

Open in new window

0
 

Author Comment

by:Ted Palmer
ID: 34918442
capricorn1:

Perhaps you already know this but I realize that my wording in the question could be better. In the VBA source code the database table that is the data source via a query is updated. Not the ListBox directly. Thus Requery makes sense.

Ted Palmer
0
 

Author Comment

by:Ted Palmer
ID: 34921878
capricorn1:

I figured it out. But you can have the points anyway. Here is the VBA language statement that made it work as I requested.

ctlList.SetFocus

Thank you,
Ted Palmer
0
 

Author Closing Comment

by:Ted Palmer
ID: 34921889
Thank you, but I figured it out myself.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now