Question Variable MsbBox List (1 Viewer)

[MOTOWN44]

Hello

I have a form and would like a msgbox to appear when a user submits information using a cmd button that lists which of the fields have been left blank.

Eg

Field 1, Field 2, Field 3, Field 4 are all blank on the form the msgbox would display as a list

Field 1
Field 2
Field 3
Field 4

Where as if only fields 1, 2 and 4 were blank the list would be

Field 1
Field 2
Field 4

The code I am using at the moment is

Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim strMsg4 As String

If (IsNull(Field 1)) Then
strMsg1 = " Field 1"
Else
strMsg1 = ""
End If

If (IsNull(Field 2)) Then
strMsg2 = " Field 2"
Else
strMsg2 = ""
End If

If (IsNull(Field 3)) Then
strMsg3 = " Field 3"
Else
strMsg3 = ""
End If

If (IsNull(Field 4)) Then
strMsg4 = " Field 4"
Else
strMsg4 = ""
End If

MsgBox strMsg1 & vbCrLf & strMsg2 & vbCrLf & strMsg3 & vbCrLf & strMsg4, vbCritical, "error"

This works but if for example Field 2 is filled in ie Not Null the list on the msgbox appears with a gap

Field 1
Field 2

Field 4

This is obviously a problem because my form has 14 fields on it and if field 13 were blank but the rest are not have a huge long msgbox with 1 result and 12 line breaks

So my main point is – is there a way of making a list in vba that displayed the null fields without line breaks?

Im assuming this is going to be something to do with variables but I've never really used variables in vba so I kindly request some professional help

Thank you

 

[pbaldy]

Rather than 4 variables I'd use 1. Something like:

If (IsNull(Field 1)) Then
  strMsg1 = "Field 1" & vbCrLf
End If

If (IsNull(Field 2)) Then
  strMsg1 = strMsg1  & "Field 2" & vbCrLf
End If

...
MsgBox strMsg1

 

[boblarson]

And additionally, I would check the field like this (to make sure that empty strings AND Nulls are accounted for):

If Len([Field 2] & vbNullString) > 0 Then

but that depends on whether the field can hold an empty string or not. Some datatypes won't and some will and it also depends on whether the field in the table has been set to allow empty strings or not. But using it on a datatype which doesn't allow empty strings won't hurt and you can use the same check for each field just to have consistent code.

 

[MOTOWN44]

Thanks guys ill give it a whirl and let you know