Show Message in text box when program finish successfuly or not

[Guinaba]

Hi all,

I have the below code that when it runs updatea 14 tables, wondering how can I show a msg using the text boxes in the template below showing whether the subs end successfully or not.

Private Sub Command0_Click()

   'Sub 1
    A_Forecast
    'Text box showing the msg OK or failed
    A_ForecastTxt "OK" or "Failed"

   'Sub 2
    B_Forecast
    
    'Text box showing the msg OK or failed
     B_ForecastTxt "OK" or "Failed"

.
.
.

Sub14..
   
End Sub

 

[jackd]

I suggest you show some real data in order to get a focused result.

What exactly are the names of the subs?

 

[Micron]

Not enough info. What is A_Forecast? If a function call, have it return a string and put that value in the form field/control:

Me.txtBoxNameHere = A_Forecast

 

[Guinaba]

Hi @Micron,

A_Forecast is a subprocedure and I have 14 different ones that run populating 14 tables. As I am still catching up with my Access VBA not sure if that the best way but here is the solution that I have found. I could convert these subprocedures into functions but not sure how to do it.

Private Sub cmdUpdateTables__CmdUpdateTbls_Click()

   '    Me.TimerInterval = 50

    On Error Resume Next
    Coles_Forecast '1st Subprocedure
    If Err = 0 Then
        Me.ColesForecastTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.ColesForecastTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    Coles_Scan_Data '2nd subprocedure
    If Err = 0 Then
        Me.ColesScanDatatxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.ColesScanDatatxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    Indies_Data '3nd Subprocedure
    If Err = 0 Then
        Me.IndiesDataTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.IndiesDataTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    SYS35_File '4th Subprocedure
    If Err = 0 Then
        Me.SYS35FileTXT.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.SYS35FileTXT.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WRP35_File '5th Subprocedure
    If Err = 0 Then
        Me.WRP35FileTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WRP35FileTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_Demand '6th Subprocedure
    If Err = 0 Then
        Me.WWDemandTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWDemandTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_OBSL '7th Subprocedure
    If Err = 0 Then
        Me.WWOBSLTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWOBSLTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_Planned_Orders '8th Subprocedure
    If Err = 0 Then
        Me.WWPlannedOrdersTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWPlannedOrdersTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_Promo_Build '9th Subprocedure
    If Err = 0 Then
        Me.WWPromoBuildTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWPromoBuildTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_Ranging '10th Subprocedure
    If Err = 0 Then
        Me.WWRangingTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWRangingTxt.Value = "File Updated Failed" & " - " & Date
    End If
       
       
    On Error Resume Next
    WW_Scan_Data '11th Subprocedure
    If Err = 0 Then
        Me.WWScanDataTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWScanDataTxt.Value = "File Updated Failed" & " - " & Date
    End If
       
 
    On Error Resume Next
    WW_SOH_by_DC '12th Subprocedure
    If Err = 0 Then
        Me.WWSOHbyDCTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWSOHbyDCTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_TPRP '13th Subprocedure
    If Err = 0 Then
        Me.WWTPRPTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWTPRPTxt.Value = "File Updated Failed" & " - " & Date
    End If
   
    On Error Resume Next
    WW_Work_List '14th Subprocedure
    If Err = 0 Then
        Me.WWWorkListTxt.Value = "File Successfuly Updated" & " - " & Date
        Else
        Me.WWWorkListTxt.Value = "File Updated Failed" & " - " & Date
    End If
     
End Sub

 

[Micron]

So you're OK now I guess. To change sub to function simply change the words at the top and the bottom should change automatically. If you have lines like
Exit Sub in between you have to change them to Exit Function. Many ways to approach these things. Your function could return a value that you test for in the sub, or your function could return the string that you're using in the sub. I might do the latter and actually call another function that creates the string since each case is just the same thing being said. That way I only write the string once. Make sure you cause an error and test that. If there is no error handler in each called procedure, Access looks upstream for an error handler and if it doesn't find one, it creates its own error response. It is possible that the err number raised could go out of scope, but without seeing your code it's just a guess.

If your called procedures were functions that returned the string your click event could be as simple as

With Me
 .ColesForecastTxt = Coles_Forecast
 .ColesScanDatatxt = Coles_Scan_Data
 .IndiesDataTxt = Indies_Data 
 .etc
End With

 

[Guinaba]

So you're OK now I guess. To change sub to function simply change the words at the top and the bottom should change automatically. If you have lines like
Exit Sub in between you have to change them to Exit Function. Many ways to approach these things. Your function could return a value that you test for in the sub, or your function could return the string that you're using in the sub. I might do the latter and actually call another function that creates the string since each case is just the same thing being said. That way I only write the string once. Make sure you cause an error and test that. If there is no error handler in each called procedure, Access looks upstream for an error handler and if it doesn't find one, it creates its own error response. It is possible that the err number raised could go out of scope, but without seeing your code it's just a guess.

If your called procedures were functions that returned the string your click event could be as simple as

With Me
.ColesForecastTxt = Coles_Forecast
.ColesScanDatatxt = Coles_Scan_Data
.IndiesDataTxt = Indies_Data
.etc
End With

Thanks @Micron for your tips! I'll try do that that later!!