asked on

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

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.

Ted Palmer

ASKER

Before.bmp

Rey Obrero (Capricorn1)

try
me.repaint

Ted Palmer

ASKER

After.bmp

Rey Obrero (Capricorn1)

in some cases, you will need the

me.repaint
Doevents

depending on the codes running

Ted Palmer

ASKER

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

Rey Obrero (Capricorn1)

try requerying the listbox

me.ctlList.requery

Ted Palmer

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ted Palmer

ASKER

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

Ted Palmer

ASKER

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

Ted Palmer

ASKER

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

Ted Palmer

ASKER

Thank you, but I figured it out myself.