Solved

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

Posted on 2011-02-17
12
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 

Author Comment

by:Ted Palmer
ID: 34917259
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917271
try
me.repaint
0
 

Author Comment

by:Ted Palmer
ID: 34917292
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917824
try requerying the listbox

me.ctlList.requery
0
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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