Using a loop? to validate IsNull for a list of fields 2

[tgikristi]

I have about 9 fields on a form that I want to check to make sure they aren't null when a user hits a 'save record' button, and suspend the save action until all the fields have data...

I have individual "If IsNull(Field) = True Then
send error to user and redirect error to field" for each field in the Save statement, but I cannot get them to work.

Is there an easier/correct way of doing this? I don't know anything about loops but I was wondering if I could use a loop/for...each, etc? Or is there a way to define a list of fields as one unit and then have a statement check each member of the unit?

Thanks so much for any help!!

 

[LittleSmudge]

For what you are needing to do I think you are on the right track but
Do NOT use the field name - use the Control Name.

They might be the same ( if you used a Form Wizard to create the form they probobly are ) but they don't have to be and it will mess things up if you try to refer to the field when it is not the same as the control name.

The sort of thing I'd expect would be

Private Sub cmdSave()

If IsNull(ControlName1) Then
MsgBox "You have left ControlName1 blank so you cannot Save Yet"
ControlName1.SetFocus
Exit Sub
ElseIf IsNull(ControlName2) Then
MsgBox " ControlName2 is Still blank. Correct your error before saving"
ControlName2.SetFocus
Exit Sub
ElseIf IsNull(ControlName3) Then
etc .. ..
etc ..

End Sub



You don't need =True as IsNull() returns a boolean result.


'ope-that-'elps.

G LS

 

[tgikristi]

I tried this, but it just saves the record, even if the fields are blank...any thoughts?

Private Sub Save_Click()
On Error GoTo Err_Save_Click
Dim Fld1, Fld2 As Variant
Fld1 = "Debited By"
Fld2 = "Date of Debit"
If IsNull(Fld1) Then
MsgBox "You cannot save because " & Fld1 & " cannot be blank.", vbOKOnly
Fld1.SetFocus
Exit Sub

ElseIf IsNull(Fld2) = True Then
MsgBox "You cannot save because " & Fld2 & " cannot be blank.", vbOKOnly
Fld2.SetFocus
Exit Sub

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Exit_Save_Click:
Exit Sub
End If

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click
End Sub

 

[tgikristi]

I tried this, but it just saves the record, even if the fields are blank...any thoughts? Thanks!!

Private Sub Save_Click()
On Error GoTo Err_Save_Click
Dim Fld1, Fld2 As Variant
Fld1 = "Debited By"
Fld2 = "Date of Debit"
If IsNull(Fld1) Then
MsgBox "You cannot save because " & Fld1 & " cannot be blank.", vbOKOnly
Fld1.SetFocus
Exit Sub

ElseIf IsNull(Fld2) = True Then
MsgBox "You cannot save because " & Fld2 & " cannot be blank.", vbOKOnly
Fld2.SetFocus
Exit Sub

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Exit_Save_Click:
Exit Sub
End If

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click
End Sub

 

[LittleSmudge]

Both of the IF IsNull() checks will ALWAYS Fail so it will always save the record

Use
If IsNull([Debited By]) Then

and

If IsNull([Date Of Debit]) Then


But before doing that, check out the FAQ

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=80657351&CFTOKEN=47404721

on avoiding B!**dy space characters within object names.



'ope-that-'elps.

G LS

 

[danvlas]

tgikristi: Just for you to understand the mistake:

Dim Fld1, Fld2 As Variant
Fld1 = "Debited By"
Fld2 = "Date of Debit"

will assign strings to the variables.
So If IsNull(Fld1) will return False, because Fld1 has, at that moment, the value "Debited by"
The same applies to Fld2

The correct syntax would have been:
Dim Fld1, Fld2 As Control
Set Fld1 = Me("Debited By&quot
Set Fld2 = Me("Date of Debit&quot

You could do as LittleSmudge says, or replace

If IsNull(Fld1)
with
If IsNull(Me(Fld1))

and
If IsNull(Fld2)
with
If IsNull(Me(Fld2))

Syntax for referring to a control on the form:
Forms("FormName&quot("ControlName&quot
Forms![FormName]![ControlName]

If the control resides on the form whose code you're building:
Me("ControlName&quot
Me![ControlName]

Dan

 

[GHolden]

You could do one function to use on any of your forms.
On the form set the tag of any text box you want to be checked to 1 (this allows you to choose which must be completed).
Have the following public function, which basically loops through all the controls of the passed form checks to see if they are text boxes if they are checks to see if the tag is 1 then checks to see if they are null. If all these are true the text box name is added to the string returned.

Public Function mstrIsBlank(ByVal vfrmForm As Form) As String
Dim intIndx As Integer

For intIndx = 0 To vfrmForm.Controls.Count - 1
If (TypeOf vfrmForm.Controls(intIndx) Is TextBox) Then
If vfrmForm.Controls(intIndx).Tag = 1 And IsNull(vfrmForm.Controls(intIndx).Value) Then
mstrIsBlank = mstrIsBlank & vbCrLf & vfrmForm.Controls(intIndx).Name
End If
End If
Next

End Function

You can then call the function something like

Dim strFieldName As String

strFieldName = mstrIsBlank(Me)

If strFieldName <> vbNullString Then
MsgBox &quot;The following fields must be completed..&quot; & mstrIsBlank(Me)
End If

Hope this is some assistance...

 

[GHolden]

Typo in the call bit, should be

If strFieldName <> vbNullString Then
MsgBox &quot;The following fields must be completed..&quot; & strFieldName
End If

 

[LittleSmudge]

Good one GHolden,

I've not come across the Tag property being used in that way before - bit I can see a wealth of potential uses that this solution could be put to.

Have a star from me for ( what to me is ) a novel solution to the problem.


G LS

 

[GHolden]

Thanks,

I use the same type of theory for locking, unlocking, enabling etc, different controls using strings in the tag field.

eg.

tag could be for one control

Visible Lock Check

for another..

Lock

And so on

then using instr on the tag value one function can check, lock, hide etc any combination of controls on your form.

 

[tgikristi]

Holy !

Wow--Thank you all so much for your help--I am just using Access for the 1st time and this is a great forum for getting a ton of expert and novel advice!

Thanks!!
tgikristi

 

[PassingBy]

I have tried this peice of code and am getting a runtime error 13 type mismatch on the line. It is only checking 2 text boxes on the form.

If vfrmForm.Controls(intIndx).Tag = 1 And IsNull(vfrmForm.Controls(intIndx).Value) Then

Any clues as to what is going wrong?

I have so far only 'Tagged' text boxes does it work for Combo's too?

 

[tgikristi]

I got that error too when I used too many 'Me' before the (Fld1)...what ended up working (even for comboboxes) was:

Dim Fld1 As Control
Set Fld1 = Me(&quot;Control&quot

If IsNull(Fld1) Then
MsgBox &quot;...&quot;
Fld1.SetFocus
Exit Sub

ElseIf...

tgikristi

 

[GHolden]

If you want to test combo boxes you need

If (TypeOf vfrmForm.Controls(intIndx) Is ComboBox) Then
If vfrmForm.Controls(intIndx).Tag = 1 And vfrmForm.Controls(intIndx).ListIndex = -1 Then
mstrIsBlank = mstrIsBlank & vbCrLf & vfrmForm.Controls(intIndx).Name
End If
End If

regarding the error, I originally did this in VB6 however I've noticed that in access you need to set all the tags otherwise it bombs out. So you would need to put 0 in the ones you don't want to check.

Hope this solves your probs...

 

[PassingBy]

GHolden

This seems to have resolved the problem, I will try with Comb's next!

Great posting