do loop with msg box

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

Looking for a "neater coding" method using a Do Loop
to substitute and clean up code that already works.

The user clicks a command button to hide the subform. The
underlying code verifies if the user has still entered
data into several fields, reminding the user that
eventually, these fields need to have data before further
processes can commence.

Using a series of nested IF..then..else

If isnull(field1) then
msgbox
else
if isnull(field2) then
msgbox
else
etc


Is it possible somehow have a variablename concatenated
with a changing counter and define each resulting variable
with an actual field name

Dim fldnamevariable, counter, fldname(counter)
fldname1 = field1
fldname2 = field2
fldname3 = field3
fldname4 = field4
..
..
..
fldname10 = field10

Do

if isnull(
fldname(counter)
)
then
MSGBOX
counter = counter + 1

while counter <11

end sub
 
I would for a bunch of if statements use:

If isnull(field1) then
msgbox
exit sub
end if

If isNull(field2) then
msgbox
exit sub
end if

Of couse, then the above is in a nice sub. However, the above just solves a
nasty indent problem, and your original question was can you feed a list of
field names, and loop?

Sure, you can use:

me("stringFieldName")

or

strField = "LastName"

me(strField) will refer to last name,
if isnull(me(strField)) = True then....

Hence:

Dim vFields As Variant
Dim i As Integer

vFields = Array("Field1", "Field2", "Field3")

For i = 0 To UBound(vFields, 1)

If IsNull(Me(vFields(i))) = True Then

MsgBox "you must enter a value for " & vFields(i)
Exit Sub
Next i
 
Back
Top