Entering data in Table from Subform

[Bob Quintal]

I have a form with sub-form. I've decided I want to add mulitple
records associated wth a record on the subform. When I tried to
nest and sub-form in a sub-form I get a notice about not being
able to use "Continuous" in the "middle" form. Grr.

So I've created a pop-up that takes the new data fine however I
can't get the linking field from the sub-form into the table for
the sub-sub-form, that field remains empty.

Make sure that you have a textbox on the popup that's bound to the
field in the popup's table. Its .visible property can be false.

In the popup's OnOpen event, add code to test for new record, so you
don't change an existing value, then see if the openargs contains a
value, and set the control to that value.

In the code that opens the form, set the openargs parameter to the
value of the linking field as a string, even if it's numeric.

DoCmd.OpenForm stDocName,,, stWhereClause,,,"'" & me.linkfield & "'"

Form_Open
if me.NewRecord then
iflen(me.openargs) > 0 then
me.linkfield = val(me.openargs)
end if
end if

 

[Guest]

I have a form with sub-form. I've decided I want to add mulitple records
associated wth a record on the subform. When I tried to nest and sub-form in
a sub-form I get a notice about not being able to use "Continuous" in the
"middle" form. Grr.

So I've created a pop-up that takes the new data fine however I can't get
the linking field from the sub-form into the table for the sub-sub-form, that
field remains empty.

 

[John W. Vinson]

I have a form with sub-form. I've decided I want to add mulitple records
associated wth a record on the subform. When I tried to nest and sub-form in
a sub-form I get a notice about not being able to use "Continuous" in the
"middle" form. Grr.

One alternative is to use correlated subforms. It's a bit tricky but doable.

Let's say you have tables name Parent, Child and Grandchild, with primary keys
ParentID, ChildID, GranchildID and corresponding foreign keys. Have your
continuous subform subChild based on Child; create a second subform
subGrandchild based on Grandchild.

Put a textbox named txtRelay (which should probably have its Visible property
set to No once you have it working) on the main form with a control source

=subChild.Form!ChildID

Set the Master Link Field of subGrandchild to

txtRelay

Access won't offer this on the dropdown list of alternatives, but you can just
type it in. Make the Child Link Field ChildID (the foreign key in Grandchild).

You may need to Requery the grandchild form in the AfterUpdate event of the
Child subform, I don't remember for sure.

John W. Vinson [MVP]

 

[Guest]

John~

Since this grand-child-form isn't actually embeded into the child-form I
don't have the option for master/link.

In order to view all the records on the child-form I added an open form
button that would lauch the grandchild form.

Any other ideas would be appreciated.

~Lori

 

[Guest]

Bob~

I don't know much about coding. I assumed the stWhereClause was supposed to
be a reference the linking field, but it case it wasn't I tried it both ways.


Also I keep getting an "expected end of statement" on the
"iflen(me.openargs) > 0 then" line.

~Lori

 

[Guest]

Bob~

There was a typo, fixed that but it's still not working. Let me see if I
have this right.

DoCmd.OpenForm stDocName,,, stWhereClause,,,"'" & me.linkfield & "'"

Goes into the sub-form which launches the pop-up (which is done via an
open-form button).

and the Form_Open you provided goes into the pop-ups OnOpen.

You said something about setting the openargs parameter to a field, but I
don't... don't know where this is done.

~Lori

You mentioned something 'bout settings

 

[John W. Vinson]

Since this grand-child-form isn't actually embeded into the child-form I
don't have the option for master/link.

Reread my suggestion.

Make the great-grandchild form a subform *OF THE MAIN FORM* - not of the child
subform.

Opening a separate popup form is possible but a major pain.

In order to view all the records on the child-form I added an open form
button that would lauch the grandchild form.

Then you'll need to have code in the button's click event to filter the form
based on the selected ID in the "child" form, *and* to pass that ID in the
OpenArgs property, *and* code in the popup form's Open event to set the
Default Value property of the child table foreign key field to the OpenArgs
value (after checking to be sure there is such a value).

John W. Vinson [MVP]

 

[Bob Quintal]

Bob~

I don't know much about coding. I assumed the stWhereClause was
supposed to be a reference the linking field, but it case it
wasn't I tried it both ways.


Also I keep getting an "expected end of statement" on the
"iflen(me.openargs) > 0 then" line.

~Lori

myspacebar is flaky. That should be If len....not iflen.

 

[Bob Quintal]

Bob~

There was a typo, fixed that but it's still not working. Let me
see if I have this right.

DoCmd.OpenForm stDocName,,, stWhereClause,,,"'" & me.linkfield &
"'"

Goes into the sub-form which launches the pop-up (which is done
via an open-form button).

If you use the Wizard to do your button, it will give you most of
that line, you just need to tack on to the end of it.

and the Form_Open you provided goes into the pop-ups OnOpen.

You said something about setting the openargs parameter to a
field, but I don't... don't know where this is done.

In the example I posted, it shows in hte popup form's
On Open Event
Form_Open
if me.NewRecord then
iflen(me.openargs) > 0 then
me.linkfield = val(me.openargs)
end if
end if
end sub

the "me.linkfield" needs to be changed to whatever is the real name
of your linking field.

That will put the value passed in openargs to the new field in that
form.

If instead of adding a new record, you want to see an existing
record, you would change that line of code to
me.filterby = "linkfield = " & openargs
then you need to add a line right below saying
me.filteron = true.

~Lori

You mentioned something 'bout settings

 

[Guest]

Bob~

It's just not working. The Event_ID remains at "0".

John's post, now that I've read it more closely, appears to be working and
may provide some added benefit. I think I'll tinker with that.

Thank you for your patients.

~Lori

 

[Guest]

John~

Hm. Once I -read- the message instead of skimming it wasn't tricky at all.
Only thing is there are two different types of events triggered off the child
sub-form that I was hoping to use two different sub-sub... ah grand-child
forms.

The night is still early, it will give me something to bang my head against
and detract from the pain of my broken toe.

~Lori