Complicated (?) form-subform problem

[Guest]

....well, complicated to me at least!! (I have x-posted this to another non-MS
forum, so I apologize if you've already seen it).

I have a form (Form1) that contains a subform (Subform1). The subform is in
datasheet view, and I want to add a new field to it that holds notes - a memo
field. This won't work well in datasheet view...I want to be able to make the
box bigger so I can see a lot of its contents at once. So, I want to add
another box onto the main form that shows the notes associated with the
currently selected record in Subform1.

So basically, what I think I want is Form1, with Subform1 in datasheet view,
and another subform (Subform2) showing the notes associated with the current
record in Subform1. [Note: at first I did this using a popup form for the
Notes field, which worked fine, but we decided it would be preferable to have
it as a box on Form1.] Not sure if it's clear from the above explanation, but
Subform1 and Subform2 both have the same table as their datasource.

I'm pretty sure I need to use the OnCurrent event of Subform1 to make the
contents of Subform2 change as the selected record is changed. The problem
I'm having is that I can't get the syntax right for referring to Subform2. My
code on OnCurrent for Subform1 was:

Private Sub Form_Current()
Me![Subform2].Form!mmoNotes.Requery
End Sub

I also tried .Refresh but neither works. I was getting a syntax error when I
try to open Form1.

I read another couple of articles and tried this:

Private Sub Form_Current()
Forms!Form1![Subform2].Form!mmoNotes.Requery
End Sub

And now I'm getting a different error - "Run-time error '-2146500594
(8000f000e)': Method 'Form' of object '_Subform' failed.

Any suggestions or advice on how best to do this would be much appreciated.
I haven't done much work with subforms other than basic form-subform pairs,
so this is a bit new and confusing for me.

Thanks!!
Jen

 

[RuralGuy]

...well, complicated to me at least!! (I have x-posted this to another
non-MS forum, so I apologize if you've already seen it).

I have a form (Form1) that contains a subform (Subform1). The subform is
in datasheet view, and I want to add a new field to it that holds notes
- a memo field. This won't work well in datasheet view...I want to be
able to make the box bigger so I can see a lot of its contents at once.
So, I want to add another box onto the main form that shows the notes
associated with the currently selected record in Subform1.

So basically, what I think I want is Form1, with Subform1 in datasheet
view, and another subform (Subform2) showing the notes associated with
the current record in Subform1. [Note: at first I did this using a popup
form for the Notes field, which worked fine, but we decided it would be
preferable to have it as a box on Form1.] Not sure if it's clear from
the above explanation, but Subform1 and Subform2 both have the same
table as their datasource.

I'm pretty sure I need to use the OnCurrent event of Subform1 to make
the contents of Subform2 change as the selected record is changed. The
problem I'm having is that I can't get the syntax right for referring to
Subform2. My code on OnCurrent for Subform1 was:

Private Sub Form_Current()
Me![Subform2].Form!mmoNotes.Requery
End Sub

I also tried .Refresh but neither works. I was getting a syntax error
when I try to open Form1.

I read another couple of articles and tried this:

Private Sub Form_Current()
Forms!Form1![Subform2].Form!mmoNotes.Requery
End Sub

And now I'm getting a different error - "Run-time error '-2146500594
(8000f000e)': Method 'Form' of object '_Subform' failed.

Any suggestions or advice on how best to do this would be much
appreciated. I haven't done much work with subforms other than basic
form-subform pairs, so this is a bit new and confusing for me.

Thanks!!
Jen

Hi Jen,

Are you using the Master/Child link fields on SubForm1? If so, put the
same child linking field on SubForm2 but make it invisible and/or put it
behind the TextBox you will display the memo field in. Then use the same
Master/Child linking as SubForm1. No additional code anywhere! Everything
you want to happen will just happen!

hth

 

[Steve Schapel]

Jen,

I assume that the Record Source of Subform2 is something like...
SELECT Notes FROM YourTable WHERE KeyField =
[Forms]![Form1]![Subform1]![Keyfield]

Try it like this...
Me.Parent!Subform2.Requery

An alternative approach might be to put a hidden control on Form1 to
reference the KeyField from Subform1, and then use this hidden control
as the Link Master Fields property of Subform2.

Mind you, this means that in effect you have 2 subforms both bound to
the same table. I am not sure what the consequences of this could be,
in terms of adding/editing/saving, but I would avoid this arrangement
myself. I would normally use a Zoombox in this type of scenrio. But if
it is really necessary to have the memo field's data shown at all times,
I would probably be tempted to put the memo field in a separate table,
related 1:1 to the Subform1 table.

--
Steve Schapel, Microsoft Access MVP

...well, complicated to me at least!! (I have x-posted this to another non-MS
forum, so I apologize if you've already seen it).

I have a form (Form1) that contains a subform (Subform1). The subform is in
datasheet view, and I want to add a new field to it that holds notes - a memo
field. This won't work well in datasheet view...I want to be able to make the
box bigger so I can see a lot of its contents at once. So, I want to add
another box onto the main form that shows the notes associated with the
currently selected record in Subform1.

So basically, what I think I want is Form1, with Subform1 in datasheet view,
and another subform (Subform2) showing the notes associated with the current
record in Subform1. [Note: at first I did this using a popup form for the
Notes field, which worked fine, but we decided it would be preferable to have
it as a box on Form1.] Not sure if it's clear from the above explanation, but
Subform1 and Subform2 both have the same table as their datasource.

I'm pretty sure I need to use the OnCurrent event of Subform1 to make the
contents of Subform2 change as the selected record is changed. The problem
I'm having is that I can't get the syntax right for referring to Subform2. My
code on OnCurrent for Subform1 was:

Private Sub Form_Current()
Me![Subform2].Form!mmoNotes.Requery
End Sub

I also tried .Refresh but neither works. I was getting a syntax error when I
try to open Form1.

I read another couple of articles and tried this:

Private Sub Form_Current()
Forms!Form1![Subform2].Form!mmoNotes.Requery
End Sub

And now I'm getting a different error - "Run-time error '-2146500594
(8000f000e)': Method 'Form' of object '_Subform' failed.

Any suggestions or advice on how best to do this would be much appreciated.
I haven't done much work with subforms other than basic form-subform pairs,
so this is a bit new and confusing for me.

Thanks!!
Jen

 

[Guest]

Hi RuralGuy,

Thanks so much - I did what you suggested, and that has gotten rid of all
the error messages and I can now see the Notes (memo) field for the first
record in Subform1. But when I click on a different record in Subform1,
Subform2 still doesn't refresh. Any other suggestions?

Jen

 

[Steve Schapel]

Jen,

With all due respect to RuralGuy, I think he misunderstood your question.

 

[Guest]

Hi Steve,

Thanks for your response. The more I try to do with this, the more it seems
to screw up! So I think I'm going to take your advice and go back to using a
pop-up form for the notes info. It just seems less complicated (not to
mention less error-prone) in the long run.

Thanks again,
Jen

Jen,

I assume that the Record Source of Subform2 is something like...
SELECT Notes FROM YourTable WHERE KeyField =
[Forms]![Form1]![Subform1]![Keyfield]

Try it like this...
Me.Parent!Subform2.Requery

An alternative approach might be to put a hidden control on Form1 to
reference the KeyField from Subform1, and then use this hidden control
as the Link Master Fields property of Subform2.

Mind you, this means that in effect you have 2 subforms both bound to
the same table. I am not sure what the consequences of this could be,
in terms of adding/editing/saving, but I would avoid this arrangement
myself. I would normally use a Zoombox in this type of scenrio. But if
it is really necessary to have the memo field's data shown at all times,
I would probably be tempted to put the memo field in a separate table,
related 1:1 to the Subform1 table.

--
Steve Schapel, Microsoft Access MVP

...well, complicated to me at least!! (I have x-posted this to another non-MS
forum, so I apologize if you've already seen it).

I have a form (Form1) that contains a subform (Subform1). The subform is in
datasheet view, and I want to add a new field to it that holds notes - a memo
field. This won't work well in datasheet view...I want to be able to make the
box bigger so I can see a lot of its contents at once. So, I want to add
another box onto the main form that shows the notes associated with the
currently selected record in Subform1.

So basically, what I think I want is Form1, with Subform1 in datasheet view,
and another subform (Subform2) showing the notes associated with the current
record in Subform1. [Note: at first I did this using a popup form for the
Notes field, which worked fine, but we decided it would be preferable to have
it as a box on Form1.] Not sure if it's clear from the above explanation, but
Subform1 and Subform2 both have the same table as their datasource.

I'm pretty sure I need to use the OnCurrent event of Subform1 to make the
contents of Subform2 change as the selected record is changed. The problem
I'm having is that I can't get the syntax right for referring to Subform2. My
code on OnCurrent for Subform1 was:

Private Sub Form_Current()
Me![Subform2].Form!mmoNotes.Requery
End Sub

I also tried .Refresh but neither works. I was getting a syntax error when I
try to open Form1.

I read another couple of articles and tried this:

Private Sub Form_Current()
Forms!Form1![Subform2].Form!mmoNotes.Requery
End Sub

And now I'm getting a different error - "Run-time error '-2146500594
(8000f000e)': Method 'Form' of object '_Subform' failed.

Any suggestions or advice on how best to do this would be much appreciated.
I haven't done much work with subforms other than basic form-subform pairs,
so this is a bit new and confusing for me.

Thanks!!
Jen

 

[RuralGuy]

Hi Steve,

Thanks for your response. The more I try to do with this, the more it
seems to screw up! So I think I'm going to take your advice and go back
to using a pop-up form for the notes info. It just seems less
complicated (not to mention less error-prone) in the long run.

Thanks again,
Jen

Hi Jen and Steve,

Actually Steve you gave me too much credit in your other post. I didn't
misunderstand the question, I just didn't think far enough ahead. It is up
to you Jen as to how to proceed but if you want to try the Master/Child
thing I am pretty sure it will work if done correctly.

I am not familiar with your table structure but let's say SubForm1 Link
Master field is "AccountNumber" and SubForm1 displays all of the invoices
for the Account. We need to make the Link Master field the
"InvoiceNumber" field on SubForm1. Soooo...

Hide the InvoiceNumber control under the MemoField TextBox
Set the Link Child Field to it and then set

Link Master Field = SubForm1ControlName.Form!InvoiceNumberControlName

You will have to replace my psudo names with the actual names. I've used
this method before and it works just fine. Good luck with the rest of your
project whatever method you choose for this little snag.

hth

 

[Steve Schapel]

Jen,

If you want to re-visit this option, try it like this... Put a textbox
on Subform1 for the memo field, whatever size fits with the form design,
but obviosly will not be big enough to show all the memo data. On the
Dbl Click event of this control, put code like this...
DoCmd.RunCommand acCmdZoomBox

 

[Steve Schapel]

Yep, RuralGuy. Now you are talking about the same sort of approach that
I mentioned in my first reply. I would normally prefer a hidden control
on the main form, to reference the key field of the first subform, and
then use this control as the Link Master Fields property of the second
subform. Whereas this is "standard procedure" when the first subform
and second subform are based on related tables, I would not use this
approach where both subforms are based on the same table. Might work, I
don't really know as I've never tried it, but it seems like asking for
trouble if the data is meant to be editable.

 

[RuralGuy]

Yep, RuralGuy. Now you are talking about the same sort of approach that
I mentioned in my first reply. I would normally prefer a hidden control
on the main form, to reference the key field of the first subform, and
then use this control as the Link Master Fields property of the second
subform. Whereas this is "standard procedure" when the first subform
and second subform are based on related tables, I would not use this
approach where both subforms are based on the same table. Might work, I
don't really know as I've never tried it, but it seems like asking for
trouble if the data is meant to be editable.

Thanks Steve. Either method accomplishes the same goal. I will have to
admit my systems were for related tables and not "the same" table. I defer
to your knowledge and experience. I always enjoy your posts. Are you
listening Jen?