How to force last line of a scrollable text box to show on screen/form when using as a process log.

[sonntagc]

Hello. I am using a scrollable textbox control (txtProcLog) as a process log to provide the user of the program visual cues that the VBA code behind a button is still running. But the amount of lines i can show on the form is shorter than the number of lines that are produced when the code runs. For example, the textbox on the form shows about 12 lines, but the code runs calls to 27 queries. So once the number of queries exceeds the visible lines of the textbox, the user can't see that the code is still running or what query is currently being executed. Is there a way that after i append each new line to the contents of the text box, that the text box "scrolls up", so the newest text is still visible??

my code is below. values in all caps are constants that i use for formatting the text appended into the text box.

ELIPS = " ... "
SMDONE = " done."
LGDONE = "DONE."
PAD1 = 4 spaces
PAD2 = 8 spaces

THANKS for any help in advance.

Private Function Run_ALL_Process_Queries() As Integer
On Error GoTo Run_ALL_Process_Queries_Err
    Dim strQryArray(30) As String
    Dim strDetTimeStart As String
    Dim strDetTimeStop As String
	
    strQryArray(0) = "qMake_tmpCIL_Data"
    strQryArray(1) = "qAppend_QL_CIL_Combined_Full"
    strQryArray(2) = "qMake_tmp_Elig_GrpPlanLoc"
    strQryArray(3) = "qUpdt_QL_CIL_Data_Combined_GrpPlanLoc"
    strQryArray(4) = "qUpdt_QL_CIL_Data_Combined_PreX"
    strQryArray(5) = "qUpdt_QL_CIL_Data_Combined_GrpDateRng"
    strQryArray(6) = "qUpdt_QL_CIL_Data_Combined_Trim_Fields"
    strQryArray(7) = "qMake_tmpECS_ClaimLines"
    strQryArray(8) = "qUpdt_tmpECS_ClaimLines_Empty_Procs"
    strQryArray(9) = "qMake_tmpECS_ClaimProc_First_NonEmpty"
    strQryArray(10) = "qMake_tmpECS_ClaimLnCnt"
    strQryArray(11) = "qMake_tmpECS_ClaimProcs"
    strQryArray(12) = "qMake_tmpECS_ClaimProcCnt"
    strQryArray(13) = "qMake_tmpECS_ClaimProcCntMax"
    strQryArray(14) = "qMake_tmpECS_ClaimProcMain_Pick"
    strQryArray(15) = "qMake_tmpECS_ClaimProcMaxTarget"
    strQryArray(16) = "qUpdt_QL_CIL_Data_Combined_ClmLnCnt"
    strQryArray(17) = "qUpdt_QL_CIL_Data_Combined_ClmProcCnt"
    strQryArray(18) = "qUpdt_QL_CIL_Data_Combined_Procs_Count"
    strQryArray(19) = "qUpdt_QL_CIL_Data_Combined_Procs_Indiv"
    strQryArray(20) = "qUpdt_QL_CIL_Data_Combined_Proc_CodeText"
    strQryArray(21) = "qUpdt_QL_CIL_Data_Combined_Proc_MaxTarget"
    strQryArray(22) = "qUpdt_QL_CIL_Data_Combined_Proc_MainPick"
    strQryArray(23) = "qUpdt_QL_CIL_Data_Combined_Prov_PCR"
    strQryArray(24) = "qUpdt_QL_CIL_Data_Combined_CBM_Status"
    strQryArray(25) = "qUpdt_QL_CIL_Data_Combined_PlaceSvc"
    strQryArray(26) = "qUpdt_QL_CIL_Data_Combined_PatElig_Info"
    strQryArray(27) = "qUpdt_QL_CIL_Data_Combined_Addl_Elig"
    
        
    DoCmd.SetWarnings False
    
    With Me
        txtProcLog = txtProcLog & vbCrLf & vbCrLf & PAD1 & "Processing imported CIL data " & "(with following queries)" & ELIPS		
        For ix = 0 To 27
			txtProcLog = txtProcLog & vbCrLf & PAD2 & strQryArray(ix) & " {" & Now()            
            DoCmd.OpenQuery strQryArray(ix)
            txtProcLog = txtProcLog & " | " & Now() & "}" & ELIPS & SMDONE            
            DoEvents
        Next ix        
    End With
	Me.txtProcLog = Me.txtProcLog & vbCrLf & PAD1 & LGDONE


    Run_ALL_Process_Queries = vbOK
    
Run_ALL_Process_Queries_Exit:
    DoCmd.SetWarnings True
    Exit Function


Run_ALL_Process_Queries_Err:
    MsgBox Error$
    Run_ALL_Process_Queries = vbCancel
    Resume Run_ALL_Process_Queries_Exit


End Function

[Micron]

What prevents the user from moving the focus off of that control? As soon as that happens, the scrollbars would disappear would they not (can't recall)?
Would you consider a textbox laid over a textbox in a type of progress bar as a meter of completion? The bottom one is a fixed width. The length of the top one, whose background color is blue (or whatever fancies you) is changed by a factor of 100% divided by the number of operations. At the completion of each, you can update a label caption with the percentage or name of the operation. I use a small snippet of code to introduce slight pause and repaint the form before running the next operation. Would be easier I think, and would not lose focus.

[davegri]

[CODE
DoCmd.SetWarnings False
Dim txtProgress As String
Dim txtHeader As String
Dim txtCurrent As String

With Me
txtProcLog = vbNullString
txtHeader = pad1 & "Processing imported CIL data " & "(with following queries)" & ELIPS
For ix = 0 To 27
txtCurrent = pad2 & ix & " " & strQryArray(ix) & " {" & Now()
DoCmd.OpenQuery strQryArray(ix)
txtCurrent = txtCurrent & " | " & Now() & "}" & ELIPS & SMDONE
txtProgress = vbCrLf & txtCurrent & txtProgress
txtProcLog = txtHeader & txtProgress
Me.Repaint
Next ix
End With
Me.txtProcLog = vbCrLf & pad1 & LGDONE & Me.txtProcLog


Run_ALL_Process_Queries = vbOK
[/CODE]

Try it like this. The latest query run will show at the top instead of the bottom.

[sonntagc]

What prevents the user from moving the focus off of that control? As soon as that happens, the scrollbars would disappear would they not (can't recall)?
Would you consider a textbox laid over a textbox in a type of progress bar as a meter of completion? The bottom one is a fixed width. The length of the top one, whose background color is blue (or whatever fancies you) is changed by a factor of 100% divided by the number of operations. At the completion of each, you can update a label caption with the percentage or name of the operation. I use a small snippet of code to introduce slight pause and repaint the form before running the next operation. Would be easier I think, and would not lose focus.

Micron: thanks for suggestion. i didn't think about doing it that way. in regards to losing focus, when the code starts, the access program pretty much sucks up all the resources. we don't have very robust machines here. so, while the processes are running, you can click on the form but nothing happens until the code finishes. this is just a quick& dirty way to provide visual cue to the user that something is going on (beyond setting the hourglass). it's a daily process i'm trying to hand over to the junior ops guy. i will think about how to implement your suggestion. you wouldn't happen to have code snippet do you? or could you point me to a weblink that implements your suggestion? thanks.

[sonntagc]

davegri: thanks for the suggestion. i started working it that way already because i saw that by doing it so the top line is always visible. i may have to live with that. I actually have a group control where the user can pick to show the log in a "top-down" manner, which is what drove the original question, or in a "bubble up" view, which is actually what your suggestion is. i just may throw that out, and follow your suggestion to just make the current line the top line. thanks.

[Micron]

The update process runs the queries one by one. Initial settings on form open are set within the update code (some of it deprecated by me). Code below.
Form design view (header removed for proprietary reasons). There is an image control with dbl click event in the form header in case it fails to close and needs to be closed by an admin. The close button is only to be used at the end of the process (users are aware) mainly due to the fact that when queries are running, program control flips over to Jet and forms don't react right away to such things as button clicks anyway.


view showing overlapping controls positioned for explanation. Bottom one is a blue rectangle (I might have suggested a textbox in error).

DoCmd.OpenForm "frmUpdate"
    Set lbl = Forms!frmupdate.lblUpdateStatus 'message about what data is being returned
    Set lbl2 = Forms!frmupdate.lblStatus  'notifies percentage of completion
    Set rec = Forms!frmupdate.recStatus  'blue progress bar
    lbl2.Visible = False 'turned off 09/17/2014
    '''lbl2.Caption = "0% complete"
    rec.Width = 0
    lbl.Caption = ""
    DoCmd.RepaintObject acForm, ("frmUpdate")

There are 7 updating steps, typical:

step2:
    lngCustErr = 10020
    With lbl
        .ForeColor = vbWhite
        .Caption = "GETTING REQUISITION DATA..."
    End With
    rec.Width = 525
    DoCmd.RepaintObject acForm, ("frmUpdate")
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryReqnData", , acReadOnly

lngCustErr tells a Select Case block in the error handler code exactly which step(s) generate any errors. The pause is for allowing control to come back to the form momentarily (from Jet) so that the form view will refresh reliably. You simply pass a number you think provides a safe but minimal lag.

Public Function Pause(sngSecs As Single)
Dim Start As Variant
Start = Timer
Do While Timer < Start + sngSecs
    DoEvents
Loop
End Function