Solved Limit number of rows in a message box (1 Viewer)

[oxicottin]

Is it possible to limit the number of rows in a message box? I have a loop that displays data in a message box and the max rows it can accumulate from data is 74 rows which is way to long, the Yes/No button is off the screen it's so long... Is there a way to limit the number of rows of data so my message box fits on the screen? Can I have some and at the end maybe something like "............"?

Here is the loop that collects the data from a query:

   Else
        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            'strMissnData = .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine
           
            Do Until .EOF
               
                 strMissnData = strMissnData & .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine

                .MoveNext
            Loop
        End With

        If vbYes = MsgBox("There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & "    " & strMissnData & vbCrLf _
        & "* You have to follow up on the missing data before:" & vbCrLf & vbCrLf _
        & "    1) Emailing a Label Count" & vbCrLf _
        & "    2) Saving Label Count as a .pdf", vbYesNo + vbInformation, "Missing Information") Then

Here is the full code im using:

Function FindNullRecordsOnUnload(frm As Form) As Boolean
   
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intX As Integer
    Dim strMissnData As String
    Dim strMissnProd As String
   
    Set db = CurrentDb()
    Set rs = fDAOGenericRst("qry_FindNullRecordsOnUnload", dbOpenSnapshot)
    'Set rs = db.OpenRecordset("qry_FindNullRecordsOnUnload", dbOpenSnapshot)
'--------------------------------------------------------------------------------------------------
'Open the switchboard its a new record and there isnt data missing
    If frm.NewRecord Then
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If
'--------------------------------------------------------------------------------------------------
'If you entered data in main form (frm_InventoryOverview) but didnt create records in subform then cancel and go back to form.
        Dim MissinProd As DAO.Recordset
        Set MissinProd = frm.Form.sfrm_InventoryDetails.Form.RecordsetClone()
       
    ElseIf frm.Form.sfrm_InventoryDetails.Form.Recordset.RecordCount = 0 Then
        MsgBox "You havent created records in your subform click the -Create Records- button!", vbInformation + vbOKOnly, "Required Data!"
       
        FindNullRecordsOnUnload = True
'--------------------------------------------------------------------------------------------------
'If there isnt any fields left blank then close the form and open switchboard
    ElseIf rs.RecordCount < 1 Then
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If
'--------------------------------------------------------------------------------------------------
'Some data is missing, stop and display whats missing so it can be filled out, _
        Run through the query (qry_FindNullRecords) and find products/lengths/quanity that data wasnt entered
    Else
        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            'strMissnData = .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine
           
            Do Until .EOF
               
                 strMissnData = strMissnData & .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine

                .MoveNext
            Loop
        End With

        If vbYes = MsgBox("There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & "    " & strMissnData & vbCrLf _
        & "* You have to follow up on the missing data before:" & vbCrLf & vbCrLf _
        & "    1) Emailing a Label Count" & vbCrLf _
        & "    2) Saving Label Count as a .pdf", vbYesNo + vbInformation, "Missing Information") Then
       
'--------------------------------------------------------------------------------------------------
'Stop closing of form you selected YES, so finish filling out blank fields you missed
        FindNullRecordsOnUnload = True  'End Function
'--------------------------------------------------------------------------------------------------
    Else
'--------------------------------------------------------------------------------------------------
'You selected NO so close the form and open the switchboard
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If

'--------------------------------------------------------------------------------------------------
    End If
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[Minty]

Rather than looping to just EOF, just set a maximum number of iterations, and do either EOF OR Max Recs in the loop control?

 

[ebs17]

I have a loop that displays data in a message box

It is better to display query data in a form. You can also display the dialog for the user in the form.

 

[Josef P.]

Try:
Replace

With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            'strMissnData = .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine
         
            Do Until .EOF
             
                 strMissnData = strMissnData & .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine

                .MoveNext
            Loop
        End With

        If vbYes = MsgBox("There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & "    " & strMissnData & vbCrLf _
        & "* You have to follow up on the missing data before:" & vbCrLf & vbCrLf _
        & "    1) Emailing a Label Count" & vbCrLf _
        & "    2) Saving Label Count as a .pdf", vbYesNo + vbInformation, "Missing Information") Then

with

        MissingDataMessage = GenerateMissingDataMessage(rs, 5)

        If vbYes = MsgBox(MissingDataMessage, vbYesNo + vbInformation, "Missing Information") Then

+

Private Function GenerateMissingDataMessage(ByVal rs As DAO.Recordset, ByVal MaxMissingDataLines As Long) As String

    Dim intX As Integer
    Dim strMissnData As String
    Dim LineCounter As Long
   
    Dim strMissnDataMessage As String
   
    With rs
        .MoveLast
        .MoveFirst
        intX = .RecordCount
       
        Do Until .EOF
            LineCounter = LineCounter + 1
            If LineCounter > MaxMissingDataLines Then
               strMissnData = strMissnData & "... (+" & CStr(intX - LineCounter + 1) & " more)" & vbNewLine
               Exit Do
            End If
            strMissnData = strMissnData & .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine
            .MoveNext
        Loop
     
    End With
   
    strMissnDataMessage = "There are " & intX & " records that are missing information for the following Products/Lengths."
    strMissnDataMessage = strMissnDataMessage & vbCrLf & vbCrLf & strMissnData & vbCrLf
    strMissnDataMessage = strMissnDataMessage & "* You have to follow up on the missing data before:" & vbCrLf & vbCrLf _
        & "    1) Emailing a Label Count" & vbCrLf _
        & "    2) Saving Label Count as a .pdf"

    GenerateMissingDataMessage = strMissnDataMessage

End Function

(This code is so far only written, but never run - so untested).

 

[oxicottin]

I tried Select Top (@Rows) in my query and that works but my functions message box counts number of records and if I set the # of records to say 30 and I'm actually missing 74 records then my message box would say I'm missing 30 records and that's not correct.

 

[oxicottin]

@Josef P. that worked perfectly.... Thanks!

 

[ebs17]

One question: Why do you show data if it is of no interest to the user? If the data were important, you would have to show all the data and not just a reduced part.

 

[The_Doc_Man]

Eberhard asks a valid question, which I will generalize a bit: Don't ever bother to show things in excruciating detail if the user has no need to see the excruciating detail. Make those details available as an option for the masochists among your users, but minimize the details that don't contribute to the user's immediate need to make progress on whatever task was being attempted.