Need assistance to *streamline* custom message box

[skydivetom]

Hello experts:



I'd like to get some assistance with streamlining a process for generating a custom message box.

Background:
- Attached DB contains 3 "syslog" tables (starting with prefix "tbl_Syslog_").
- W/o going into detail, these tables are auto-generated based on log files {"ac", "ac_audit", "ps_audit"}. For purposes of this thread, it's not important to discuss the import routine.
- However, at the end of the import routine, I would like to throw a custom message box that shows how many records were loaded for which log file... again, {"ac", "ac_audit", "ps_audit"}.

Existing Process:
- For sake of argument, let's say all 3 log tables were generated/loaded in the current routine.
- At the end of the routine, the custom message (click on command button "Import Syslogs") is thrown.
- Here, all 3 log names + their associated record counts (via Dlookup) are displayed.
- So far so good!

Review of the VBA:
- Although the VBA works, it is NOT efficient/dynamic/smooth... whatever you'd like to call it.
- That is, if I were to end up with, e.g., 2 additional log files, I would have to do the following:

a. For each new log file, declare the variables: CntSysLog#, LongSyslog#, ShortSyslog#
b. I'd also have to add 2 lines where I "Define long table names" and set them to the new tables names.
c. I'd have to add 2 lines where I "Extract short table reference from long table name" (e.g., extract "ac" from "tbl_Syslog_ac").
d. Next, I'd have to add 2 lines where I "Obtain record counts from each syslog table".
e. Finally, I'd have to add 2 lines to the custom message box (w/ the respective variable names).

Here's what I envision:
- If I were to end up with, e.g., 2 new log files (tables), I'd simply enter 2 new records (ASC order) into table [tbl_LogFiles].
- Then, the modified VBA might do away w/ the many hard-code variables and use a ForLoop instead.
- So, in this case, whatever variables would be needed will, e.g., look up the *short* [LOG_REFERENCE] + use [TABLENAME] as an input to the DCOUNT function.

My goal:
- Ultimately, I want the same exact message box as I currently have but with a much more efficient/dynamic method which would reduce adding new lines of code when new log files are added.
- If necessary, I'd be okay to manually add lines to the actual message box VBA as long as I can easily reference variable counters (or something like it).

Any thoughts on how to streamline the existing function/message box generation?

Thank you,
EEH

[darkwind]

Well, one thing to be aware of is the size limits of the MsgBox method....

If you're going to programmatically fill a box like this, it may not work out if you have a large number of log types. The character limit is 1024, so if you have more characters than that it won't work and will truncate the text early. I have a method I commonly use I adapted from somewhere I don't recall:

Public Const vbStayTop = 4096

Public  Function iPopUpBox(ByVal sMessage As String, Optional ByVal  iSecondsToWait As Integer = 0, Optional ByVal sTitle As String =  "ImportData", Optional ByVal iType As VBA.VbMsgBoxStyle = 0) As Integer
    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Set wsh = VBA.CreateObject("wScript.Shell")
    iPopUpBox = wsh.PopUp(sMessage, iSecondsToWait, sTitle, iType + vbStayTop)
End Function

That will get around the character limit, and it has the iSecondsToWait property, which lets you auto-dismiss the pop up after a period of time if you want to. The equivalent to your existing MsgBox code would be:

    Dim sMsg As String
    sMsg = "Successfully imported n records from .log files:" & vbCrLf & vbCrLf
    sMsg = sMsg & "Syslog '" & ShortSyslog1 & "':" & Chr$(9) & Chr$(9) & CntSysLog1 & vbCrLf
    sMsg = sMsg & "Syslog '" & ShortSyslog2 & "':" & Chr$(9) & Chr$(9) & CntSysLog2 & vbCrLf
    sMsg = sMsg & "Syslog '" & ShortSyslog3 & "':" & Chr$(9) & Chr$(9) & CntSysLog3
    Debug.Print iPopUpBox(sMsg, , "Status Message", vbInformation + vbOKOnly)

If you wanted that same message to disappear after 15 seconds:

    iPopUpBox sMsg, 15, "Status Message", vbInformation + vbOKOnly

I've written up something that should meet requirements. See attached.

[skydivetom]

darkwind:

Thanks so much for sending this alternative method for the custom message.

- In my original (sample) solution, the message is thrown (lines 35-38) using the simple DCount function (lines 30-32).
- In your version, the message box is not thrown. Based on my very dumbed down example DB, what action would call your message box and outputs record counts {1, 2, 3)?

Thank you,
Tom

[darkwind]

The message box is called on line 120. The record counts are populated into an array on line 60, and into the message string on line 110.

It is using the wscript.shell.popup method, which has been reported as unreliable by some users in the past. I've never had any trouble with it, but perhaps you're running into that problem.

You could certainly replace line 120 with the below, but it won't work if you need more than 1024 characters (it'll be truncated):

MsgBox sMsg, vbOKOnly + vbInformation, "Status Message"

If the wscript popup method isn't working for you and you need over 1024 characters, your alternative is to create a userform that looks like a pop up box. I've made another copy of the database using that method instead of the popup method.

[Welshgasman]

Worked for me @darkwind

[skydivetom]

Darkwind:

Wow!!! Your solution super, super impressive!!!!

Cheers,
Tom