moving to subform

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

How can I tell, via VBA, when the user has moved from the fields on a form
to a subform on that same form?

Need to do some data entry checking at that point. As it is, if they leave
a required field blank and move to (click in) the subform, I get an Access
error msg (in this case, it's a combo box that requires a selection).

Thx.
 
Hi Bill,

You have a couple of easy no-VBA required methods that you can use. These
have the added benefit of being enforced at the table level, so all queries
and forms inherit this business rule. Not so with custom VBA code in a
specific form.

1.) Set the field that serves as the control source for the combo box in
question as a required field or

2.) Set a validation rule on the field: Is Not Null
Make sure to include the appropriate validation text, such as:
You must select a salesperson. (for the EmployeeID example given below)

As an example, open the Northwind sample database. Set the EmployeeID field
in the Orders table as Required = Yes. Try to add a new order, using the
Orders form, but not selecting a salesperson.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Tom:

Hm. Setting the validation rule to Is Not Null (and validation text) didn't
work.

When I went to the subform without entering any data in the subject field, I
got the Access error. Now, this combo box sets up a relationship, so is
that a wrinkle?

Here is the error msg:

You cannot add or change a record because a related record is required in
table "tablename."

Bill
 
Well, the datatype was Number, and the default val was set to zero, so I
removed the default value.

Now, it does not give me an error when I move to the subform field, but
neither does the "is not null" validation rule fire.

:-)
 
Hi Bill,

Hmmm....it works for me. Are you sure that you didn't accidently enter the
validation rule in the validation text property? And, did you enter a
validation text?

Try it on the Northwind sample, as I indicated in my first post. Does it
work for you then? I tested it before posting, and it worked fine for me.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
I checked again, and I had "is not null" in the validation rule, and "must
supply value" in the valildation text field.

Will look some more. Maybe something else is getting in the way.
 
Back
Top