Shrink or Grow MsgBox Data

[mattc66 via AccessMonster.com]

I have the following message box code. I want to set it up so it can shrink
or grow depending on the data.

Would I use an If statment. If the MyStr... are blank I don't want them in
the message.

MsgBox "The following Feilds are blank." _
& vbCr & vbCr & MyStr1 & "" _
& vbCr & vbCr & MyStr2 & "" _
& vbCr & vbCr & MyStr3 & "" _
& vbCr & vbCr & MyStr4 & "", vbOK, "Missing Date"

 

[Allen Browne]

Dim MyStr As String

If Len(MyStr1) > 0 Then
MyStr = MyStr1
End If

If Len(MyStr2) > 0 Then
MyStr = MyStr & MyStr2
End If

etc

 

[mattc66 via AccessMonster.com]

That didn't work. MyStr2 showed data, yet the MyStr string showed MyStr="".

Dim MyStr As String

If Len(MyStr1) > 0 Then
MyStr = MyStr1
End If

If Len(MyStr2) > 0 Then
MyStr = MyStr & MyStr2
End If

etc

I have the following message box code. I want to set it up so it can shrink
or grow depending on the data.

[quoted text clipped - 7 lines]

& vbCr & vbCr & MyStr3 & "" _
& vbCr & vbCr & MyStr4 & "", vbOK, "Missing Date"

 

[JohnC]

Consider the before update event to check for missing data. When missing
data is found, add the information to a message string. If the message
string is longer than the original 46 characters, then display the
information in a message box. Something like:


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

Dim strMessage As String
Dim strReqMsg As String
Dim strTitle As String

strTitle = "Missing Data"

strReqMsg = "The Following Required Fields Were Left Blank." '
46 characters

If IsNull(cboEMP_ID_NO) Then strReqMsg = strReqMsg & vbCrLf & "Employee
ID"
If IsNull(cboCOURSE_TYPE) Then strReqMsg = strReqMsg & vbCrLf & "Course
Type"
If IsNull(cboSITE_PRIORITY_ID) Then strReqMsg = strReqMsg & vbCrLf &
"Priority Code"
If IsNull(cboORG_CODE) Then strReqMsg = strReqMsg & vbCrLf & "Org Code"
If Len(strReqMsg) > 46 Then
MsgBox strReqMsg, , strTitle
Cancel = True
End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate

End Sub

 

[Allen Browne]

Matt, the idea is to use Len() to test the length of each string.

If the length is greater than zero, concatenate the string onto MyStr, along
with the trailing vbCrLf.

The resultant string will then contain only those strings that had something
in them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That didn't work. MyStr2 showed data, yet the MyStr string showed
MyStr="".

Dim MyStr As String

If Len(MyStr1) > 0 Then
MyStr = MyStr1
End If

If Len(MyStr2) > 0 Then
MyStr = MyStr & MyStr2
End If

etc

I have the following message box code. I want to set it up so it can
shrink
or grow depending on the data.

[quoted text clipped - 7 lines]

& vbCr & vbCr & MyStr3 & "" _
& vbCr & vbCr & MyStr4 & "", vbOK, "Missing Date"