Code to Open New Form Based on Input Form Answer

[Alan]

I have an Access 2002 database with the following tables/fields:

tblStudents
---------------
StuID (key)
Name
Address
Children? (yes/no field)
ClassNo

tblChildren
--------------
StuID
ChildNo (key is StuID + ChildNo)
Name
DOB

I have a form named frmAddStudents where the user can input new
Student info. If the user answers yes in the Children? field, I want
to insert code at the proper event for that field that will open my
frmAddChildren form, have the StuID control on that form already set
to the value of StuID that was entered on the frmAddStudents form, and
move the focus to the ChildNo control for input. If the user answers
no in the Children? field, I want the focus to jump to the ClassNo
control on the frmAddStudents. Can someone provide me with the
appropriate code that will accomplish this?

At the end of the frmAddChildren, I will have two command buttons. The
first will be to Add Another Child. For the click event of this cmd
button, I need code that will save the record just entered to the
tblChildren table and then start a new record on the frmAddChildren
with the focus set at the ChildNo control and the StuId control
already set to equal the value from the previous record.Can someone
provide me with the appropriate code that will accomplish this?

Thanks for your assistance.

Alan

 

[Albert D. Kallal]

I have a form named frmAddStudents where the user can input new
Student info. If the user answers yes in the Children? field, I want
to insert code at the proper event for that field that will open my
frmAddChildren form

What do you want to do if children are already entered? You don't want to
open the children form in add mode becuase how then will you view exiting
chilrden?

You should really consider a sub-form here, as then you can do the above
whole process without ANY code. You're spending way too much writing all
kinds of the user interface stuff when it's all actually built into MS
access naturally. You can do all of this without writing one line code if
you do this right.

The problem I see right now is your desing does not have provisiions for
editing existing children (how do you plan to do that?).

Anway, lets ignore the big holes in your designs give you some ideas how you
can accomplish what you are asking for.

Ok, in the after update event of the Children field, then simply go:

if me!HasChildren = true then
me.Refresh ' force a disk write
docmd.OpenForm "frmAddChildren",,,,acFormAdd
forms!frmAddChildren.ChildNo.SetFocus
else
me.Classno.SetFocus
end if

The above handles your 1st part.

, have the StuID control on that form already set
to the value of StuID

YOu need to add the following code in form frmAddChildren's "before insert"
event:

me.StuID = forms!frmAddChildren!StuID

The above will automatic set the value of stuID for you WHEN YOU add the
reocrd (type of some first characters into the record). We use the on-insert
event to set the StudID becuase if user mades a mistake, then they can close
the form and a reocrd will NOT be added. So, we ONLY set the StudID value
WHEN the user starts typeing (before insert event).

At the end of the frmAddChildren, I will have two command buttons. The
first will be to Add Another Child.

Just have the user use the built in command buttions to move (navagate) to a
new reocrrd, or simply close the form to save the reocrd. You don't want to
spend too much time writing user interface when it is built in since then
your users are going to expect every single form you have to work the same
way. (by the way, the form should be set as model). and if you set the
form's cycle property correctly when you get the last field on the form it
should cycle rum back to the top, and this can save you the focus issue we
talked about.

For the click event of this cmd
button, I need code that will save the record just entered to the
tblChildren table and then start a new record on the frmAddChildren

as mention the user can just close the form in the same as automatic, or the
person can navigate to a new record, and again the same as automatic

However, if you do want a button there for convenience sake that means
saving go to a new record, the following code simply does the trick (it
accomplishes this by simply moving to a new record).

You can place a button with the following code:

DoCmd.GoToRecord , , acNewRec
me.ChildNo.SetFocus

I think that having this form pop up during data entery is probably not best
desing. What are you going to do after user enters a couple children then
later on realize the this was the wrong record. that would be going to do
have the user going to the has to Linfield and enter no to, and yet your
database now has children attached to this person? I really think you're
opening up a big can of Worms here.

I think you should use a sub form here. with a sub form you can have a list
of children in there and the usual see that there is children when they
simply look at the form, and that way you don't even have to set up the yes
no field.

if you design is such that you really don't want to use a sub form for the
children, then I would actually place a button on the form and opens up the
children formed and displays all the testing children. that way they can
navigate and view the existing children, or they could use the above
buttoned to move to new child is they want to add some more children.

The code to open he frmChildren with ONLY the children that belongs to the
current person is:

docmd.OpenForm "frmAddChildren",,,"StudID = " & me!StudID

Notice in the above we don't open the children form in add mode, but we
simply open in normal mode and filtered to the exist list of children. This
would allow one to navigate through the children, and if the user so decides
navigate to a new record then on before-insert code will set the StudID.
And, your button that "adds new" children in the frmChildren would also work
quite nice in this case.

 

[Alan]

Thanks for your help, Albert. That was just what I needed.

Alan