Where do I put the code main or subform?

[Tim]

I have made some research, but I haven't gotten what I am looking for. I have
a main form and a subform. In the subform, I have many text fields that I
need to do the validation, i.e., the user must enter the data; the field can
not be empty. My question is where should I put the VBA codes? In the main
form or the sub form?

I know that I should use the BeforeUpdate event and set Cancel = True, but I
don't know where I put my codes. Also, besides the BeforeUpdate event, should
I use the Exit event for the specific textbox? I have 10 text boxes to do the
validation. If I use the Exit event, then I have to write some codes for this
event in the subform.
Thank you for your help in advance. tim

 

[Allen Browne]

Tim, use the BeforeUpdate event of the *form*, not the controls. And it must
be in the subform's module.

Even simpler, open your table in design view, and set the Required property
to Yes for the fields where a value must be entered. Then Access won't allow
the record to be saved unless the required fields have a value--no code
needed.

 

[Tim]

Hi Allen,
Thank you for your help. Now, it's clear that I need to put my VBA codes
into the Subform's BeforeUpdate event. How about the Exit event in the
subform for every textbox? Maybe I don't need it, do I?
I can not use the Required property because the record source of this
subform is a query not a table.
Thank you. I appreciate your help. tim

 

[Rick Brandt]

Hi Allen,
Thank you for your help. Now, it's clear that I need to put my VBA
codes into the Subform's BeforeUpdate event. How about the Exit event
in the subform for every textbox? Maybe I don't need it, do I?
I can not use the Required property because the record source of this
subform is a query not a table.

But the data is still being written to one or more tables and you can still make
those fields required in those tables and the query will be bound by those
rules.

 

[Allen Browne]

I don't find the Exit event of the controls useful:
a) It does nothing positive: it never fires if the user never visits a
control (e.g. if the user skips a control by clicking in another one).

b) It is unnecessarily restrictive. For example, if you have a control (such
as the city) that automatically assigns a value to another control (such as
the zip code), and the user clicks in the zip code before filling out the
city, you now have them stuck there until they go and look up the zip code
for themselves somewhere.

As Rick says, even if this subform's RecordSource is a query, the query
still gets its fields from tables, so you can still set the Required
property of the field(s) in the table(s).

In general, Form_BeforeUpdate is the best place to do record-level
validation, including:
- validation that is too complex to use in a field's Validation Rule;
- optional validation (i.e. warn the user, but allow the entry anyway);
- comparisons between fields (since you need both fields filled in before
you can make the comparison.)

 

[Tim]

Thank you very much, experts. It's interesting, isn't it? Actually, I think
my forms (main and subform) are probably different. I will take a look at my
forms again and let you guys know what I think.
Thanks -- tim

 

[Tim]

OK...Here is my scenario. I have a main form and a subform. The main form is
used to display the record, say, student records (name, adress, etc.), that
pull out from a table. The subform is used to display their scores entered
by teacher(s). I would like to validate what a teacher enter, i.e., the field
must not be blank or empty before s/he can go to the next textbox. The
teacher would click on the navigator of the form at the bottom to advance to
the next student record.
My question is if it's appropriate to use Form_BeforeUpdate?
Thank you, tim

 

[Allen Browne]

Yes, Form_BeforeUpdate is the best solution for record-level validation that
is too complex to put into the table itself.

 

[Tim]

Thanks, Allen. I have written some VBA codes into my subform BeforeUpdate
event and tested it. However, when I intentionally left [txtField1] blank and
click the Access built-in navigation of the subform to advance to the next
record, nothing is happen; i.e., there is no error message and the form shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you know what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the code,
there is still no error message it supposed to pop up!

Please help!! thank you very much
--tim

 

[Allen Browne]

1. Is the subform bound to a table/query?

2. Is txtField1 bound to a field in the table?

3. Is the form's BeforeUpdate property set to:
[Event Procedure]

4. You did put this into the Before Update of the subform's module (not the
main form's module)?

5. Temporarily comment out the error handler, and add a line to the top of
the procedure to verify the code is running, and add a Stop. When it runs
you can then press F8 repeatedly to step through the lines of code and trace
what's going on.

6. The Exit Sub part need to be outside the IF block.

7. You can use Me to refer to the current form.

8. After these changes, does the code compile (Compile on Debug menu)?

You will now have something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'On Error GoTo PROC_ERR
Stop
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
End If
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I have written some VBA codes into my subform BeforeUpdate
event and tested it. However, when I intentionally left [txtField1] blank
and
click the Access built-in navigation of the subform to advance to the next
record, nothing is happen; i.e., there is no error message and the form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the code,
there is still no error message it supposed to pop up!

Please help!! thank you very much
--tim

Yes, Form_BeforeUpdate is the best solution for record-level validation
that
is too complex to put into the table itself.

 

[Tim]

Thanks, Allen. To answer your questions...

1. Is the subform bound to a table/query? -Yes.
2. Is txtField1 bound to a field in the table? -Yes.
3. Is the form's BeforeUpdate property set to [Event Procedure] -Yes.
4. You did put this into the Before Update of the subform's module (not the
main form's module)? -Yes.
5. Temporarily comment out the error handler, and add a line to the top of
the procedure to verify the code is running, and add a Stop. When it runs
you can then press F8 repeatedly to step through the lines of code and trace
what's going on. -Good practice!
6. The Exit Sub part need to be outside the IF block. -All right!
7. You can use Me to refer to the current form.

-I do put IsNull(Me.txtField1), but in the following line below it, I have
to refer the
main form also. If not, there is an error message saying that the program
can't find the form "frmSubform" referred to in a macro expression or VB
code.

8. After these changes, does the code compile (Compile on Debug menu)?

-Yes, but I would like to add a loop so that the message keeps repeat until
the user will have to enter something in the field. I have tried to do the
following code,
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"

Do Until Not IsNull(Me.txtField1) And Me.txtField1 <> ""
Forms![frmMain]![frmSubform]![txtField1].SetFocus
Loop
End If
------
However, I get the infinitive loop. Can you please help me to fix the bug?

Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I have written some VBA codes into my subform BeforeUpdate
event and tested it. However, when I intentionally left [txtField1] blank
and
click the Access built-in navigation of the subform to advance to the next
record, nothing is happen; i.e., there is no error message and the form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the code,
there is still no error message it supposed to pop up!

Please help!! thank you very much
--tim

 

[Allen Browne]

No loop is needed.

Cancelling Form_BeforeUpdate means the user is stuck editing the form. Once
they have entered the data, it will try to save, which triggers
Form_BeforeUpdate again, which runs your test. If the data is still not
right, they are still stuck editing the form, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. To answer your questions...

1. Is the subform bound to a table/query? -Yes.
2. Is txtField1 bound to a field in the table? -Yes.
3. Is the form's BeforeUpdate property set to [Event Procedure] -Yes.
4. You did put this into the Before Update of the subform's module (not
the
main form's module)? -Yes.
5. Temporarily comment out the error handler, and add a line to the top
of
the procedure to verify the code is running, and add a Stop. When it runs
you can then press F8 repeatedly to step through the lines of code and
trace
what's going on. -Good practice!
6. The Exit Sub part need to be outside the IF block. -All right!
7. You can use Me to refer to the current form.

-I do put IsNull(Me.txtField1), but in the following line below it, I have
to refer the
main form also. If not, there is an error message saying that the program
can't find the form "frmSubform" referred to in a macro expression or VB
code.

8. After these changes, does the code compile (Compile on Debug menu)?

-Yes, but I would like to add a loop so that the message keeps repeat
until
the user will have to enter something in the field. I have tried to do the
following code,
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"

Do Until Not IsNull(Me.txtField1) And Me.txtField1 <> ""
Forms![frmMain]![frmSubform]![txtField1].SetFocus
Loop
End If
------
However, I get the infinitive loop. Can you please help me to fix the bug?

Thanks, Allen. I have written some VBA codes into my subform
BeforeUpdate
event and tested it. However, when I intentionally left [txtField1]
blank
and
click the Access built-in navigation of the subform to advance to the
next
record, nothing is happen; i.e., there is no error message and the form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the code,
there is still no error message it supposed to pop up!

 

[Tim]

Well, I have problems here. When I intentionally leave [txtField1] blank and
click on the Next button of the built-in Access navigaton (on the subform),
it does pop up my error message and the SetFocus set the cursor back to this
field. However, I, intentionally, click on the next button of the Access
navigation of the subform again, this time, it is advanced to the next
record. This is not right!
Also, when I leave the [txtField1] blank and click on the next button of the
Main form, the error message pops up and I click the OK button of the
message, it advances to the next record. Why does it behave like this?
Thank you for your help
tim

No loop is needed.

Cancelling Form_BeforeUpdate means the user is stuck editing the form. Once
they have entered the data, it will try to save, which triggers
Form_BeforeUpdate again, which runs your test. If the data is still not
right, they are still stuck editing the form, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. To answer your questions...

1. Is the subform bound to a table/query? -Yes.
2. Is txtField1 bound to a field in the table? -Yes.
3. Is the form's BeforeUpdate property set to [Event Procedure] -Yes.
4. You did put this into the Before Update of the subform's module (not
the
main form's module)? -Yes.
5. Temporarily comment out the error handler, and add a line to the top
of
the procedure to verify the code is running, and add a Stop. When it runs
you can then press F8 repeatedly to step through the lines of code and
trace
what's going on. -Good practice!
6. The Exit Sub part need to be outside the IF block. -All right!
7. You can use Me to refer to the current form.

-I do put IsNull(Me.txtField1), but in the following line below it, I have
to refer the
main form also. If not, there is an error message saying that the program
can't find the form "frmSubform" referred to in a macro expression or VB
code.

8. After these changes, does the code compile (Compile on Debug menu)?

-Yes, but I would like to add a loop so that the message keeps repeat
until
the user will have to enter something in the field. I have tried to do the
following code,
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"

Do Until Not IsNull(Me.txtField1) And Me.txtField1 <> ""
Forms![frmMain]![frmSubform]![txtField1].SetFocus
Loop
End If
------
However, I get the infinitive loop. Can you please help me to fix the bug?

Thanks, Allen. I have written some VBA codes into my subform
BeforeUpdate
event and tested it. However, when I intentionally left [txtField1]
blank
and
click the Access built-in navigation of the subform to advance to the
next
record, nothing is happen; i.e., there is no error message and the form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the code,
there is still no error message it supposed to pop up!

 

[Allen Browne]

Tim, something else must be happening here.

Perhaps txtField1 is unbound after all.
Perhaps an Undo is firing.
Perhaps something is assigning another value (perhaps something that's not
visible, such as a zero-length string) to the field.
Perhaps it's something I didn't think of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, I have problems here. When I intentionally leave [txtField1] blank
and
click on the Next button of the built-in Access navigaton (on the
subform),
it does pop up my error message and the SetFocus set the cursor back to
this
field. However, I, intentionally, click on the next button of the Access
navigation of the subform again, this time, it is advanced to the next
record. This is not right!
Also, when I leave the [txtField1] blank and click on the next button of
the
Main form, the error message pops up and I click the OK button of the
message, it advances to the next record. Why does it behave like this?
Thank you for your help
tim

No loop is needed.

Cancelling Form_BeforeUpdate means the user is stuck editing the form.
Once
they have entered the data, it will try to save, which triggers
Form_BeforeUpdate again, which runs your test. If the data is still not
right, they are still stuck editing the form, and so on.

Thanks, Allen. To answer your questions...
1. Is the subform bound to a table/query?
-Yes.
2. Is txtField1 bound to a field in the table?
-Yes.
3. Is the form's BeforeUpdate property set to [Event Procedure]
-Yes.
4. You did put this into the Before Update of the subform's module
(not
the
main form's module)?
-Yes.
5. Temporarily comment out the error handler, and add a line to the
top
of
the procedure to verify the code is running, and add a Stop. When it
runs
you can then press F8 repeatedly to step through the lines of code and
trace
what's going on.
-Good practice!
6. The Exit Sub part need to be outside the IF block.
-All right!
7. You can use Me to refer to the current form.
-I do put IsNull(Me.txtField1), but in the following line below it, I
have
to refer the
main form also. If not, there is an error message saying that the
program
can't find the form "frmSubform" referred to in a macro expression or
VB
code.
8. After these changes, does the code compile (Compile on Debug menu)?
-Yes, but I would like to add a loop so that the message keeps repeat
until
the user will have to enter something in the field. I have tried to do
the
following code,
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"

Do Until Not IsNull(Me.txtField1) And Me.txtField1 <> ""
Forms![frmMain]![frmSubform]![txtField1].SetFocus
Loop
End If
------
However, I get the infinitive loop. Can you please help me to fix the
bug?

Thanks, Allen. I have written some VBA codes into my subform
BeforeUpdate
event and tested it. However, when I intentionally left [txtField1]
blank
and
click the Access built-in navigation of the subform to advance to
the
next
record, nothing is happen; i.e., there is no error message and the
form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you
know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the
code,
there is still no error message it supposed to pop up!

 

[Tim]

Thanks, Allen. I will check it again and let you know if I find something.
tim

Tim, something else must be happening here.

Perhaps txtField1 is unbound after all.
Perhaps an Undo is firing.
Perhaps something is assigning another value (perhaps something that's not
visible, such as a zero-length string) to the field.
Perhaps it's something I didn't think of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, I have problems here. When I intentionally leave [txtField1] blank
and
click on the Next button of the built-in Access navigaton (on the
subform),
it does pop up my error message and the SetFocus set the cursor back to
this
field. However, I, intentionally, click on the next button of the Access
navigation of the subform again, this time, it is advanced to the next
record. This is not right!
Also, when I leave the [txtField1] blank and click on the next button of
the
Main form, the error message pops up and I click the OK button of the
message, it advances to the next record. Why does it behave like this?
Thank you for your help
tim

No loop is needed.

Cancelling Form_BeforeUpdate means the user is stuck editing the form.
Once
they have entered the data, it will try to save, which triggers
Form_BeforeUpdate again, which runs your test. If the data is still not
right, they are still stuck editing the form, and so on.

Thanks, Allen. To answer your questions...
1. Is the subform bound to a table/query?
-Yes.
2. Is txtField1 bound to a field in the table?
-Yes.
3. Is the form's BeforeUpdate property set to [Event Procedure]
-Yes.
4. You did put this into the Before Update of the subform's module
(not
the
main form's module)?
-Yes.
5. Temporarily comment out the error handler, and add a line to the
top
of
the procedure to verify the code is running, and add a Stop. When it
runs
you can then press F8 repeatedly to step through the lines of code and
trace
what's going on.
-Good practice!
6. The Exit Sub part need to be outside the IF block.
-All right!
7. You can use Me to refer to the current form.
-I do put IsNull(Me.txtField1), but in the following line below it, I
have
to refer the
main form also. If not, there is an error message saying that the
program
can't find the form "frmSubform" referred to in a macro expression or
VB
code.
8. After these changes, does the code compile (Compile on Debug menu)?
-Yes, but I would like to add a loop so that the message keeps repeat
until
the user will have to enter something in the field. I have tried to do
the
following code,
If IsNull(Me.txtField1) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"

Do Until Not IsNull(Me.txtField1) And Me.txtField1 <> ""
Forms![frmMain]![frmSubform]![txtField1].SetFocus
Loop
End If
------
However, I get the infinitive loop. Can you please help me to fix the
bug?

Thanks, Allen. I have written some VBA codes into my subform
BeforeUpdate
event and tested it. However, when I intentionally left [txtField1]
blank
and
click the Access built-in navigation of the subform to advance to
the
next
record, nothing is happen; i.e., there is no error message and the
form
shows
the next record. Same thing happened when I clicked on the built-in
navigation to advance to the next record of the main form. Do you
know
what
is wrong with the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo PROC_ERR
If IsNull([Forms]![frmSubform]![txtField1]) Then
MsgBox "You must enter Field1", vbOKOnly, "Missing Field!"
Forms![frmSubform]![txtField1].SetFocus
Exit Sub
End If
PROC_ERR:
MsgBox Err.Description
Resume Next
End Sub

***********
When I tried to refer the main form and then the subform into the
code,
there is still no error message it supposed to pop up!