MS Access Forms Cycle through each field on close to check for nulls
MS Access Forms Cycle through each field on close to check for nulls
Why does this not work?
It is the ctl.value that appears to be the problem
'On close of form
Dim VarErr As Integer
Dim Ctl As Control
VarErr = 0
For Each Ctl In Me.Controls
If Ctl.Value = Null Then 'Not liked ????
VarErr = 1
End If
Next Ctl
'Evaluate error stuff
Exit Sub
Why does this not work?
It is the ctl.value that appears to be the problem
'On close of form
Dim VarErr As Integer
Dim Ctl As Control
VarErr = 0
For Each Ctl In Me.Controls
If Ctl.Value = Null Then 'Not liked ????
VarErr = 1
End If
Next Ctl
'Evaluate error stuff
Exit Sub
Just text boxes?
if ctl.ControlType = 109 then
'check for null
endif
if ctl.ControlType = 109 then
'check for null
endif
Labels, command buttons, etc have no Value property.
Jerry's got a good point. There are some controls that do not have a .Value property. Ctl.Value will fail on those. You need to account for that in your code.
ASKER
Sorry no, still errors on line 'If NZ(Ctl.Value,"") = "" Then'
Ctl.value is not a listed option for Ctl on the programing prompt
Fields are a mixture of text, currency and memo
Ctl.value is not a listed option for Ctl on the programing prompt
Fields are a mixture of text, currency and memo
They are all text boxes on the form.
ASKER
Good point, where can I find the values for ControlType to distinquish labels and buttons from data controls... think we are getting there.
Access has built - in constants as well as the numerical values:
acTextBox
acComboBox
etc...
There is code at this URL which contains a good listing of them:
http://www.mvps.org/access/forms/frm0004.htm
Regarding this statement:
>If Ctl.Value = Null Then
Your problem here is two-fold. You need to use code like Jerry's to specify the control types on your form. However, you cannot directly compare a value to null. That will produce another error. You need to use functions such as These:
If NZ(YourValue,"") = ""
or
If IsNull(YourValue)
acTextBox
acComboBox
etc...
There is code at this URL which contains a good listing of them:
http://www.mvps.org/access/forms/frm0004.htm
Regarding this statement:
>If Ctl.Value = Null Then
Your problem here is two-fold. You need to use code like Jerry's to specify the control types on your form. However, you cannot directly compare a value to null. That will produce another error. You need to use functions such as These:
If NZ(YourValue,"") = ""
or
If IsNull(YourValue)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just the best, elegant solution
For Each Ctl In Me.Controls
If NZ(Ctl.Value,"") = "" Then 'Not liked ????
VarErr = 1
End If
Next Ctl
That will check for empty strings or nulls.