Disallow user to change record & how to use SetFocus for Subform c

[Guest]

I have a Main form with Individual information (First Name, Last Name,
Contact Info, etc) and a subform with Specialty Information (Surgeon,
Pharmacist, Dentist, etc). I need to set this form up so that the specialty
field must be populated in the subform IF there is a phone number given for
the Individual. As it currently is, users are able to enter the Individuals
info without being forced to enter specialty information (any time a phone
number is provided within the Individual's information).

I have tried using the before update event for the form to place an if
statement checking to see if the phone number field from the Main form is
populated and if the specialty field from the subform is = 0 (it's a combo
box with a specialty ID who's default value is 0). If the above scenario is
true, I use the cancel command coupled with a MsgBox message to keep it from
updating which also keeps the user from being able go to a different record.
That part seems to work fine, the problem is that with that code, I also
cannot click into the subform to populate the Specialty control that I need
to because when I click in the subform it tries to update the Main form.

My questions:
Is there a better way to keep a user from going on to the next record when
they have entered a phone number in the main form and not entered specialty
information in the subform?
Is there a way to use the code I described above and just set the focus of
the form to the Specialty subform control so that after the message pops up
telling them they need to select a specialty, the focus is also on that
control? Currently when I try and set the focus to that control it tells me
that it cannot move the focus to that control.

 

[John W. Vinson]

I have a Main form with Individual information (First Name, Last Name,
Contact Info, etc) and a subform with Specialty Information (Surgeon,
Pharmacist, Dentist, etc). I need to set this form up so that the specialty
field must be populated in the subform IF there is a phone number given for
the Individual. As it currently is, users are able to enter the Individuals
info without being forced to enter specialty information (any time a phone
number is provided within the Individual's information).

The problem here is how Subforms work. A Subform is normally used to enter
data into the "many" side of a one to many relationship; the main form is the
"one" side. For referential integrity to be enforced, Access must save the
main form record *BEFORE* anything can be entered into the subform; so the
subform mechanism saves the main form's record the moment you set focus to the
subform.

It's a "chicken or egg" problem - you say you want to save the Individual
information only *after* the specialty information is filled out; but to
prevent "orphan" records - specialists assigned to nobody in particular! - you
must save the Individual information *before*.

About the only way to really enforce this is to use unbound Forms, or
temporary tables; copy the data using VBA code to the "real" table after the
information has been entered into both tables. It's doable but can be a real
hassle.

Might just user training be a solution...?

John W. Vinson [MVP]

 

[Guest]

What you are saying makes sense and was kind of what I was thinking based on
the problems I've run into. The truth is, I don't really care if the
information on the Main form is saved, I just don't want the user to go on to
another record if a phone number has been entered before entering something
into the Specialty control on the subform. So, is there a way to allow the
main form to be saved, but not allow it to change records if a phone number
has been provided and the subform field is empty?

 

[Guest]

Is there a way to run code when the New Record, Next Record, or Previous
Record buttons are clicked? I know a common event to use for these buttons
is the Current event, but in this case, I think it would probably be too late
to run code in the Current event.