Dealing With Incoming Null values - Variants

[CementCarver]

I have a form that my client may or may not complete when they're inputting data. Therefore, there may be causes where a null value is being passed with the assignment of ctl.value.

After some research, it's been stated that you should use a variant to deal with this incoming null value. I'm not exactly sure how to implement variants. So any input would be much appreciated..... here's a snippet of my code:

For Each ctl In Screen.ActiveForm.Controls


If ctl.Tag = "Audit" Then
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
![NewValue] = ctl.Value ---------------this the line causing the error.....
.Update
End With
End If
Next ctl

CementCarver

[RuralGuy]

Why not use the Nz() function?
![NewValue] = Nz(ctl.Value,0) - If you want a zero or...
![NewValue] =Nz(ctl.Value,"") - If you want a ZeroLengthString (ZLS)

[CementCarver]

Thanks for your suggestion RuralGuy, but when I try the second line, I get an error run time error 2424, the expression you've entered has a field, control or property that Access can't find.

CementCarver

[RuralGuy]

You only used one of those lines right?

[CementCarver]

Yes, just the second one.

CementCarver

[RuralGuy]

Single step the code and see which element it does not like.