• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

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.
0
Ted Palmer
Asked:
Ted Palmer
  • 8
  • 4
1 Solution
 
Ted PalmerAuthor Commented:
0
 
Rey Obrero (Capricorn1)Commented:
try
me.repaint
0
 
Ted PalmerAuthor Commented:
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Rey Obrero (Capricorn1)Commented:
in some cases, you will need the

me.repaint
Doevents

depending on the codes running
0
 
Ted PalmerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
try requerying the listbox

me.ctlList.requery
0
 
Ted PalmerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you upload a .mdb copy of the db
0
 
Ted PalmerAuthor Commented:
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
 
Ted PalmerAuthor Commented:
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
 
Ted PalmerAuthor Commented:
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
 
Ted PalmerAuthor Commented:
Thank you, but I figured it out myself.
0

Featured Post

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!

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now