How to insert StudentID from main form into subform?

[Crispy]

I have two forms...one called frmStudentRecords and one called
frmParentInformation. The frmStudentRecords form has a command button that
launches the frmParentInformation form.

When you click to create a new record in frmStudentRecords it inserts an
autonumber into the StudentID field. This is great. However, when you then
click on the command button to launch the frmParentInformation form to fill
that out as well... the two forms are not connected (the StudentID field for
frmParentInformation is left blank). I need the new autogenerated StudentID
number from the frmStudentRecords form to insert itself into the
frmParentInformation form so the two forms connect and relationships will
work.

I tried sticking:

If IsNull(Me!StudentID) Then
Me!StudentID = Forms!frmStudentRecords!StudentID
End If

in the BeforeUpdate field of the frmParentInformation form. It didn't work..
just would stick a "0" in the student ID field.

Any ideas?

Thanks!

 

[Jack]

Try this
On your click event after you open the other document
example: DoCmd.OpenForm "frmParentInformation"
Try this
Forms![frmParentInformation]!StudentID = StudentID.Text
Cheers

-----Original Message-----
I have two forms...one called frmStudentRecords and one called
frmParentInformation. The frmStudentRecords form has a command button that
launches the frmParentInformation form.

When you click to create a new record in

frmStudentRecords it inserts an

autonumber into the StudentID field. This is great. However, when you then
click on the command button to launch the

frmParentInformation form to fill

 

[Crispy]

Thanks for the help.

Unfortunately it didnt' work. If I understood you right this is what I had:

Private Sub ParentInformation_Click()
On Error GoTo Err_ParentInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmParentInformation"

stLinkCriteria = "[StudentID]=" & Me![StudentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frmParentInformation]!StudentID = StudentID.Text

Exit_ParentInformation_Click:
Exit Sub

Err_ParentInformation_Click:
MsgBox Err.Description
Resume Exit_ParentInformation_Click

With that code, I got a error that said:

"You can't reference a property or method for a control unless the control
has the focus"

Any other ideas?

Try this
On your click event after you open the other document
example: DoCmd.OpenForm "frmParentInformation"
Try this
Forms![frmParentInformation]!StudentID = StudentID.Text
Cheers

-----Original Message-----
I have two forms...one called frmStudentRecords and one called
frmParentInformation. The frmStudentRecords form has a command button that
launches the frmParentInformation form.

When you click to create a new record in

frmStudentRecords it inserts an

autonumber into the StudentID field. This is great. However, when you then
click on the command button to launch the

frmParentInformation form to fill

that out as well... the two forms are not connected (the StudentID field for
frmParentInformation is left blank). I need the new autogenerated StudentID
number from the frmStudentRecords form to insert itself into the
frmParentInformation form so the two forms connect and relationships will
work.

I tried sticking:

If IsNull(Me!StudentID) Then
Me!StudentID = Forms!frmStudentRecords!StudentID
End If

in the BeforeUpdate field of the frmParentInformation form. It didn't work..
just would stick a "0" in the student ID field.

Any ideas?

Thanks!



.

 

[Albert D. Kallal]

stDocName = "frmParentInformation"

stLinkCriteria = "[StudentID]=" & Me![StudentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frmParentInformation]!StudentID = StudentID.Text

You can't use the stLinkCritea UNTIL the record has been created. In the
above case..if the child record (frmParentInformaton) has NOT yet been
created..the form will open to a blank (with NO record present...and then
thus you can't do the studentID assignment). (and, also ignore the advice
about using StudentID.text...that is incorrect. You need to use
StudentID.Value....or just simply use StudentID (if you .text..then the
control MUST have focus..and that surly not going to be requirement here).

It is not clear if you ALWAYS want to open to a new record...or open to
existing records..and ALLOW the user to add more records?

Regardless...the record MUST exist if you use stLikCritea.

Do you need to load up the form with existing records..and allow adding of
new records? Or will you ALWAYS force the user to add a new reocrd when
launchng the form? (and, of couse if you do this..then how to edit existing
reocrds?).

You could "check" if reocrds exist BEFORE you launch the form. If no
records exist, then you have to leave out the stLinkCritera.

In addtion, if you need to allow the user to add multiple reocrds..then a
slight differnt stragety needs to be taken here.

I am going to assume that you allow many records...so, use the following:


if me.Dirty = True then
me.Dirty = True
end if

' the above is needed to force a disk write...as the id may not yet be
created..and you really need to save the data to disk before you launch the
child form anyway. (it is good practive to get into..since it mans the
current reocrd gets saved...and then you lanch another form).

' Now...lets check if any child reocrds exist:

if nz(dlookup("studentid","parentInfoTable","studentid = " &
me!studentid),0) > 0 then

' records exist...use stLink
stLinkCriteria = "[StudentID] = " & Me![StudentID]
docmd.OpenForm stDocName,,,stLinkCriteria,acFormEdit,,me!StudentID
else
' NO records exist...DO NOT use stLink...BUT Open form in add mode...

stLinkCriteria = "[StudentID] = " & Me![StudentID]
docmd.OpenForm stDocName,,,,acFormAdd,,me!StudentID

endIf

Note how we passd the studentID in the Open Args....

That means in the forms before update event...we need to set the StudentId
like:

if isnull(me.OpenArgs) = false then
me!StudentID = me.OpenArgs
endif

You can certainly change the above code a bit..it really depends on how you
are going to allow, or want the user to add new records to that form you
open...

 

[Crispy]

Hi,

Still having a problem. Getting back the error "In order to change data
through this form, the focus must be in a bound field that can be modified."
when you click on the "Parent Information..." command button that is on the
frmStudentRecords to launch the fromParentInformation form. Any ideas?

I do want to thank you for your help. To answer your questions ... I wanted
the user to have the ability to browse through the student records and add a
record if they want. So the frmStudentRecords form is opened up to be able
to browse through, etc. and then if they want to add a new record they can
click to add a new student record from that opened form. After filling out
the blank frmStudentRecords that opens with information they then click on
the frmParentInformation command button that is on the form in order to
launch the frmParentInformation sub-form and continue to enter information
(if it's blank cause it's a new record) about the student's parents. If the
user wasn't adding a new record, simply browsing through student records and
clicked on the "Parent Information" command button, I simply want the
student's parent information to appear.

In essence the Parent Information command button is used both for existing
records (to see the parent information) or clicked on when in the process of
adding a new record to add parent information.

I hope that answers what you were asking?

Thanks again for any help. I'm completely stuck.

Abby

stDocName = "frmParentInformation"

stLinkCriteria = "[StudentID]=" & Me![StudentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frmParentInformation]!StudentID = StudentID.Text

You can't use the stLinkCritea UNTIL the record has been created. In the
above case..if the child record (frmParentInformaton) has NOT yet been
created..the form will open to a blank (with NO record present...and then
thus you can't do the studentID assignment). (and, also ignore the advice
about using StudentID.text...that is incorrect. You need to use
StudentID.Value....or just simply use StudentID (if you .text..then the
control MUST have focus..and that surly not going to be requirement here).

It is not clear if you ALWAYS want to open to a new record...or open to
existing records..and ALLOW the user to add more records?

Regardless...the record MUST exist if you use stLikCritea.

Do you need to load up the form with existing records..and allow adding of
new records? Or will you ALWAYS force the user to add a new reocrd when
launchng the form? (and, of couse if you do this..then how to edit existing
reocrds?).

You could "check" if reocrds exist BEFORE you launch the form. If no
records exist, then you have to leave out the stLinkCritera.

In addtion, if you need to allow the user to add multiple reocrds..then a
slight differnt stragety needs to be taken here.

I am going to assume that you allow many records...so, use the following:


if me.Dirty = True then
me.Dirty = True
end if

' the above is needed to force a disk write...as the id may not yet be
created..and you really need to save the data to disk before you launch the
child form anyway. (it is good practive to get into..since it mans the
current reocrd gets saved...and then you lanch another form).

' Now...lets check if any child reocrds exist:

if nz(dlookup("studentid","parentInfoTable","studentid = " &
me!studentid),0) > 0 then

' records exist...use stLink
stLinkCriteria = "[StudentID] = " & Me![StudentID]
docmd.OpenForm stDocName,,,stLinkCriteria,acFormEdit,,me!StudentID
else
' NO records exist...DO NOT use stLink...BUT Open form in add mode...

stLinkCriteria = "[StudentID] = " & Me![StudentID]
docmd.OpenForm stDocName,,,,acFormAdd,,me!StudentID

endIf

Note how we passd the studentID in the Open Args....

That means in the forms before update event...we need to set the StudentId
like:

if isnull(me.OpenArgs) = false then
me!StudentID = me.OpenArgs
endif

You can certainly change the above code a bit..it really depends on how you
are going to allow, or want the user to add new records to that form you
open...

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[Albert D. Kallal]

Hi,

Still having a problem. Getting back the error "In order to change data
through this form, the focus must be in a bound field that can be modified."
when you click on the "Parent Information..." command button that is on the
frmStudentRecords to launch the fromParentInformation form. Any ideas?

The above means that the record for the frmParentInfo has not yet been
added. And, to modify any fields on that form..then you MUST have some field
that you can edit. (the reasons for this is when you start typing..the
on-insert event will fire). In other words..you have a open form...you need
to be able to edit some data on that form. After you edit some data on that
form then your code should be able to modify fields. You then should be able
to press the button to launch the frmParentInfo. So, if you are trying to
modify some values in code on the form frmParent , then yes..you do need to
ensure the cursor is on a field that can be modified (so, that error message
means exactly what it says).

So, after you load frmParrentInfo...do a setfocus on a control that can be
modified BEFORE you change any other values in code. Normally, when a form
loads...your cursor is in the first available control that can be
modified....if your cursor is not..then change your code to do a
setfocus...or simply ensure that the cursor is in a field can be modified.
Another work around is to add the record FIRST in code..and then open to
that record..and this also eliminates this problem. And, another workaround
is to move your code that sets some field values to the on-insert event for
the form. So, check where you have some code that modifies fields/control
values on that form. The on-insert event MUST have fired before you start
trying to modify fields/controls with your code. (you can see this when you
open up a regular plain form, and go to add a new record...NOTHING appears
until you start to type (ie: the record is not added until you start to
type..nor is the autonumber field generated either. Right AFTER you type the
first keystroke into the form..then the record is added..and the on-insert
event fires).

In essence the Parent Information command button is used both for existing
records (to see the parent information) or clicked on when in the process of
adding a new record to add parent information.

Ok.and my answer kind of assumes that....

Note that if your initial students browsing form allows any kind of
editing..then again right BEFORE you launch the frmStuduent details..then
you as always need to write the record to disk right before you open the
frmStudents detail form.

 

[Crispy]

So would I put something like:

Private Sub Form_Open(Cancel As Integer)
Forms!frmParentInformation!FatherName.SetFocus
End Sub

in frmParentInformation form?

Thank you for the explanation... I believe I understand what you were
saying--or at least part of it. )

Thanks for the help,

Abby

 

[Albert D. Kallal]

So would I put something like:

Private Sub Form_Open(Cancel As Integer)
Forms!frmParentInformation!FatherName.SetFocus
End Sub

in frmParentInformation form?

Yes..you are on the right track!

I am kind of split here on the best solution..since another approach would
be to add the record in code..and then Open the form to that new record.
(however, lets continue on your current path..).

 

[Crispy]

You will dread hearing this:

I got the same error.

"In order to change data through this form, the focus must be in a bound
field that can be modified."

I have no idea why I would get the error... the FatherName field should be
fine and has the focus after you click away the error box.

Absolutely maddening.

Abby