Test for a value on a subform

[al416 via AccessMonster.com]

I have a form with a subform. The user is suppose to complete a response for
all of the Result fields in the subform. Each subform will hav a variable
number of Result records. I want to test for the existence of a result and,
if null, prompt the user to go back and complete the form. the code below is
close, but not quite there.

Any help?

Thanks

'Test for values in all Result field on open subform
'For N = 1 to MaxQuestionNumber
'CurrentResult = ("[Result]", "tblAuditQuestionResults", "[QuestionNumber] =
N")
' If IsNull CurrentResult Then
' MsgBox "All audit questions require a result/response"
' Exit Sub
' End If

 

[Jeanette Cunningham]

Easiest way to do this is to put your code on Before Update event of the
subform.
If IsNull = true then, you just go Cancel = True

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txt1) Or IsNull(Me.txt2) Or IsNull(Me.txt3) Then
Cancel = True
MsgBox "All audit questions require a result/response"
End If
End Sub

Replace txt1 with your own object names and add as many IsNull(Me.txtN)'s as
you need.

Users will not be able to go to the main form if they have missed filling in
some of the results in the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[al416 via AccessMonster.com]

Thanks, I get the idea, but I need to test the same field for a variable
number of records on the open subform. Sometimes There will be 10 seperate
Result fields and sometimes there will be 15, or 8, or whatever.

I was hoping to do this test (along with a whole bunch of other code) after
the user had completed the entire subform and form (one big test to be sure
everything is complete). If there is a better way to do it I'm all for it.
Your approach tests the subform for completeness before it updates, which is
fine, but I still am not clear how to increment the test through the entire
form.

Easiest way to do this is to put your code on Before Update event of the
subform.
If IsNull = true then, you just go Cancel = True

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txt1) Or IsNull(Me.txt2) Or IsNull(Me.txt3) Then
Cancel = True
MsgBox "All audit questions require a result/response"
End If
End Sub

Replace txt1 with your own object names and add as many IsNull(Me.txtN)'s as
you need.

Users will not be able to go to the main form if they have missed filling in
some of the results in the subform.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a form with a subform. The user is suppose to complete a response
for

[quoted text clipped - 18 lines]

' Exit Sub
' End If

 

[Jeanette Cunningham]

If you are trying to force the user to fill in all the required fields
before the record is saved, then the before update event is the way to go.
Once the user tabs or mouses out of the subform, the subform data is saved
to the table. So if you don't catch the missing fields on the before update
event of the subform, you have no way of forcing the user to complete the
required fields.

A similar thing happens with the main form - once the user tabs or mouses
into the subform, the main form data is saved to the table.

Maybe you could try with an unbound form, and do the test for required
fields on the close button - this would be more work, but you could set it
up so that you did not save any data to the tables unless all required
fields were completed.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks, I get the idea, but I need to test the same field for a variable
number of records on the open subform. Sometimes There will be 10 seperate
Result fields and sometimes there will be 15, or 8, or whatever.

I was hoping to do this test (along with a whole bunch of other code)
after
the user had completed the entire subform and form (one big test to be
sure
everything is complete). If there is a better way to do it I'm all for it.
Your approach tests the subform for completeness before it updates, which
is
fine, but I still am not clear how to increment the test through the
entire
form.

Easiest way to do this is to put your code on Before Update event of the
subform.
If IsNull = true then, you just go Cancel = True

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txt1) Or IsNull(Me.txt2) Or IsNull(Me.txt3) Then
Cancel = True
MsgBox "All audit questions require a result/response"
End If
End Sub

Replace txt1 with your own object names and add as many IsNull(Me.txtN)'s
as
you need.

Users will not be able to go to the main form if they have missed filling
in
some of the results in the subform.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a form with a subform. The user is suppose to complete a response
for

[quoted text clipped - 18 lines]

' Exit Sub
' End If

 

[al416 via AccessMonster.com]

Thanks,
I have incorporated your suggestion and it works fine.

Thanks again for the help.

If you are trying to force the user to fill in all the required fields
before the record is saved, then the before update event is the way to go.
Once the user tabs or mouses out of the subform, the subform data is saved
to the table. So if you don't catch the missing fields on the before update
event of the subform, you have no way of forcing the user to complete the
required fields.

A similar thing happens with the main form - once the user tabs or mouses
into the subform, the main form data is saved to the table.

Maybe you could try with an unbound form, and do the test for required
fields on the close button - this would be more work, but you could set it
up so that you did not save any data to the tables unless all required
fields were completed.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks, I get the idea, but I need to test the same field for a variable
number of records on the open subform. Sometimes There will be 10 seperate

[quoted text clipped - 37 lines]