multiple values in 1 form

[NigelShaw]

Hi,

how could i get multiple values shown in 1 form only? i currently have a code to look through 6 fields and if the fields iar blank, you get a a message box. you press ok and the next message is shown for the next field if it is blank but it would be so much better to have 1 message box showing all blank fields required to be filled in.

all it does is check the fields as these are required before continuing. if anyof the fields are blank, then it tells you and you cannot continue untl they are all filled. it seems easy in my head!!

im sure this can be done but im not quite sure how.

any ideas would be greatly received.

NS

 

[neileg]

Use a string variable and add the field names to the variable as you step through them. Them show the message box with the string variable unless it is of zero length.

 

[NigelShaw]

Hi,

do you mean like name them field1, field2, field3, field4 and so on for the code to loop through them like field + 1 or down those lines?

i will have to look up how to do that as its a bit out of my realm!

NS

 

[Moniker]

strFieldChecks = Field1 & Field2 & Field3 & Field4 & Field5 & Field6

If Nz(strFieldChecks,"") = "" Then
MsgBox "All fields must be entered before continuing"
Else
--- All fields have something in them ---
End If

 

[NigelShaw]

Hi Moniker,

i was hoping the message would read something like -

The following items are missing
Field1
Field3
Field4
Field6

some of the fields might have the needed info so the flash message wanted to show the items that are required. i can create a form for the look but not sure how to get the values showing like above.

also, what does Nz do?

many thanks,


Nigel

 

[Moniker]

This is code that would go in the click event of a Command Button named cmdCheckFields:

Sub cmdCheckFields_Click()

    Dim strFieldChecks As String

    strFieldChecks = strFieldChecks & Switch(Nz(Field1,"")="","Field1" & vbCrLf, True,"")
    strFieldChecks = strFieldChecks & Switch(Nz(Field2,"")="","Field2" & vbCrLf, True,"")
    strFieldChecks = strFieldChecks & Switch(Nz(Field3,"")="","Field3" & vbCrLf, True,"")
    strFieldChecks = strFieldChecks & Switch(Nz(Field4,"")="","Field4" & vbCrLf, True,"")
    strFieldChecks = strFieldChecks & Switch(Nz(Field5,"")="","Field5" & vbCrLf, True,"")
    strFieldChecks = strFieldChecks & Switch(Nz(Field6,"")="","Field6" & vbCrLf, True,"")

    If strFieldChecks <> "" Then
        MsgBox "The following fields need to be entered first:" & vbCrLf & vbCrLf & strfieldChecks,vbExclamation,"Missing Data"
        Exit Sub
    Else
        --- Process fields here ---
    End If

End Sub

And lucky you, I'm using switches too.

I'll explain both:

Nz(<ValueToCheckForNull>,<ValueToReturnIfNull>)

Nz, short for Null Zero, check to see if a value is NULL. If it is, it will return whatever is in <ValueToReturnIsNull>. For example, if you have a number field that you want to convert to a 0 when it's not entered (is a NULL value), this would do it:

MyNumberField = Nz(MyNumberField,0)

Also, this is a much better way to check for both NULL values and zero-length strings (remember they are different) at the same time. For example, this:

If Nz(MyTextField,"") = "" Then

will catch both a zero-length string and it will convert a NULL to a zero-length string. Two birds with one stone, if you will.

Switch(Expression1, Value1, Expression2, Value2, ... ExpressionX, ValueX)

A Switch function is similar to an If/Then statement. From left to right, it checks the value of Expression. If that Expression evaluates to TRUE, then the Value immediately following that Expression is returned. If Expression is FALSE, it moves to the next Expression until one of them equals TRUE. If none of them evaluate to TRUE, then the Switch function returns a NULL. For example, this line above:

strFieldChecks = strFieldChecks & Switch(Nz(Field1,"")="","Field1" & vbCrLf, True,"")

Translates into this as an If/Then statement:

If Nz(Field1,"") = "" Then
     strFieldChecks = strFieldChecks & "Field1" & vbCrLf
Else
     strFieldChecks = strFieldChecks & "" 
End If

At the end of my Switch statement, I force it to evaluate to True, and therefore the Switch returns a zero-length string instead of a NULL. (Concatenating a NULL string does weird things.)

Both of these functions have decent entries in the Access Help file. For whatever reason, some people have a little difficulty wrapping their head around the Switch function, so if you want a more detailed example, check this post (response #5) I wrote a while back.

HTH

 

[NigelShaw]

hi Moniker,

that is some reply....

thank you very much. i'll be working on it over the weekend

Nigel