Access 2003 change - no more subform.form property?

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

Hi all,
I'm in the process of testing Acc2003 with my older Acc2K files, and I have
one fairly bizarre problem:

I've got an unbound form with two subforms placed on it. The top subform is
bound to one table and the bottom subform is bound to a related (1-to-many)
child table. I want the bottom subform to update to show all child records
related to the selected record in the top subform - as you pick something
new on top, the lower subform updates to show the related child records.
This works fine in 2000, with the lower subform's Linked Child Fields set to
the foreign key in the child table and Link Master Fields set to the primary
key in the upper subform. In the OnCurrent event of the upper subform, I
requery the lower subform's RecordSource.

Access 2003 ignores the requery completely. When I debugged, as I'm looking
at the subform object, it HAS a form property, but it's not attached to
anything so I can't update the lower subform's RecordSource. Anyone else
seen this kind of thing? I'm not seeing anything in the KB or on these
forums even remotely related.

Thanks in advance.
 
When I debugged, as I'm looking
at the subform object, it HAS a form property, but it's not attached to
anything so I can't update the lower subform's RecordSource.
Click to expand...

I think that form property IS the lower subform (or a reference to it), so you
can use a statement like the following:

Me.SubForm2.Form.RecordSource = "tblFred"

(SubForm2 = the name of the bottom subform control.)

Rick
 
RS,
Thanks for the response.

That's the way you USED to be able to do it in 2000, but that's what I mean:
if you try that in 2003 it tells you that Form is an invalid property of the
subform, and examining the subform object in a watch window verifies that.
The ONLY properties you can get to are for the outer 'shell' of the subform:
you can see the LinkedChildFields and LinkedMasterFields properties but you
can't dig down into the underlying form via the FORM reference any longer.

Since nobody else has complained about this, I keep thinking I've got a
corrupted database, but it does it with two different files on four
different PCs. One of the databases is a split database with Access as both
front-end and back-end. The other database is an Access front-end and a SQL
Server 2000 back-end, if that makes any difference.

Thanks again.
 
I was going to say that I have Access 2003 and it works fine for me, but....

I realized that that my little test was created with "Default File Format" set
to Access 2000. When I realized that, I 'converted' the database to Access
2002/2003 format. No problem! It worked fine. I was ready to write back
reporting my experience but I thought I ought to try a test using a db which
was originally created in Access 2002/2003 format. So I tried it, and...

It didn't work! So, the bottom line is that my experience seems to confirm
what you are saying: we USED to be able to do it, but now in Access 2003 (using
2002/2003 format) you can't. When I compile the VBA source code I get the
error message "Compile error: Method or data member not found".

If I get a chance, I'll look into it a little more. But at this point, it
doesn't appear that it is just you and your databases. (We must BOTH be crazy!)

Rick
 
CORRECTION: It DID work when I coded it correctly. I was too quick with my
20002/2003 test program.

This didn't work: Me.SubForm2.RecordSource =

This did work: Me.SubForm2.Form.RecordSource =

So, it did always work for me in Access 2003 when I coded it correctly.

Rick
 
So, it did always work for me in Access 2003 when I coded it correctly.
Click to expand...

<WHEW>!!!

That's a relief. I use that code a *lot* and would be aghast if it
were in fact lost.
 
Rick,
I agree with your response concerning the proper referencing, but I don't
get good results no matter WHAT referencing I use. Here's my OnCurrent
event for the upper subform (the parent form is frmProjects (unbound), the
top subform is fsubProjects and the bottom subform is fsubGroups):

;==============================
Private Sub Form_Current()
Dim sSQL As String

sSQL = "SELECT * FROM tblGroup "
sSQL = sSQL & " WHERE tblGroup.PrjID = " & Me.ID
sSQL = sSQL & " ORDER BY [Description];"
' Forms!frmProjects!fsubGroups.Form.RecordSource = sSQL
' Forms.frmProjects.fsubGroups.Form.RecordSource = sSQL
' Forms("frmProjects").fsubGroups.Form.RecordSource = sSQL
' Forms.Item("frmProjects").fsubGroups.Form.RecordSource = sSQL
' Me.Parent.fsubGroups.Form.RecordSource = sSQL
' Me.Parent!fsubGroups.Form.RecordSource = sSQL
Forms!frmProjects!fsubGroups.Requery

End Sub

Each of the different (commented) referencing methods I use generates the
same error: "2455 - You entered an expression that has an invalid reference
to the property Form/Report." All of them will compile fine, but generate
the run-time error when changing records in the top subform.

I appreciate your taking the time to look at this.

Thanks!
 
When I tried it I was using a different approach, but I tried yours and was
able to get it to work. Here's my code for the OnCurrent event:

Private Sub Form_Current()
Dim Fred As String
Fred = "SELECT ProductID, ReviewerID, Date, " _
& "OverallRating, Completed FROM ProductReview " _
& "WHERE ProductID=" & ProductID & ";"

Me.Parent.SubForm2.Form.RecordSource = Fred

End Sub

Note: "SubForm2" is the name of the SubForm control that contains the bottom
form.

Also, I didn't need any Requery.

I'm wondering if maybe you have the SubForm control and the Form that it
contains mixed up. What is "fsubGroups"? Is that the name of the SubForm
control of the name of the form that's embedded in the control?

Rick
 
Rick,
Ok, at first I was starting to bang my head on the walls, but after reading
your reply I tried a couple other things: I originally had the subform
control as well as the contained form named identically ("fsubGroups"
referred to BOTH the subform control as well as its source object). I
changed those to be two different values, but that made no difference. I
STILL can't make the reference you're making in the code snippet below
without the 2455 error.

However, I then tried building a new form from scratch and re-adding the
subforms. Just for grins, I changed the LinkMasterFields format from
"fsubProjects!ID" to "fsubProjects.Form.ID" and the blasted thing worked.
So, on the one hand, I'm happy I got it to work - thanks very much for your
help. But on the other, more curious hand, I gotta wonder WHY I can't make
the same RecordSource reference you're doing, and why is the bang format no
longer any good but the dot format is fine? I've got a mix of bang and dot
references all over the place, but this is the only one that's choking.

Thanks again!

RSGinCA said:
When I tried it I was using a different approach, but I tried yours and was
able to get it to work. Here's my code for the OnCurrent event:

Private Sub Form_Current()
Dim Fred As String
Fred = "SELECT ProductID, ReviewerID, Date, " _
& "OverallRating, Completed FROM ProductReview " _
& "WHERE ProductID=" & ProductID & ";"

Me.Parent.SubForm2.Form.RecordSource = Fred

End Sub

Note: "SubForm2" is the name of the SubForm control that contains the bottom
form.

Also, I didn't need any Requery.

I'm wondering if maybe you have the SubForm control and the Form that it
contains mixed up. What is "fsubGroups"? Is that the name of the SubForm
control of the name of the form that's embedded in the control?

Rick

Subject: Re: Access 2003 change - no more subform.form property?
From: "John Smith" (e-mail address removed)
Date: 3/7/2004 8:02 PM Pacific Standard Time
Message-id: <[email protected]>

Rick,
I agree with your response concerning the proper referencing, but I don't
get good results no matter WHAT referencing I use. Here's my OnCurrent
event for the upper subform (the parent form is frmProjects (unbound), the
top subform is fsubProjects and the bottom subform is fsubGroups):

;==============================
Private Sub Form_Current()
Dim sSQL As String

sSQL = "SELECT * FROM tblGroup "
sSQL = sSQL & " WHERE tblGroup.PrjID = " & Me.ID
sSQL = sSQL & " ORDER BY [Description];"
' Forms!frmProjects!fsubGroups.Form.RecordSource = sSQL
' Forms.frmProjects.fsubGroups.Form.RecordSource = sSQL
' Forms("frmProjects").fsubGroups.Form.RecordSource = sSQL
' Forms.Item("frmProjects").fsubGroups.Form.RecordSource = sSQL
' Me.Parent.fsubGroups.Form.RecordSource = sSQL
' Me.Parent!fsubGroups.Form.RecordSource = sSQL
Forms!frmProjects!fsubGroups.Requery

End Sub

Each of the different (commented) referencing methods I use generates the
same error: "2455 - You entered an expression that has an invalid reference
to the property Form/Report." All of them will compile fine, but generate
the run-time error when changing records in the top subform.

I appreciate your taking the time to look at this.

Thanks!
Click to expand...
Click to expand...
 
Ohhhhhhhhhhhhhh...... now I get it! (I think...) (...after a bit of detective
work.)

I looked up that 2455 error on the MS Knowledge Base and found an article
entitled "Dirty Property Not Recognized on Unbound Form". (If you're
interested: http://support.microsoft.com/default.aspx?scid=kb;en-us;208908)
That's not exactly your problem, but it did let me put 2 and 2 together. (2 + 2
= 5, right?)

First of all, I was wondering about your 2 Link properties on the subform. The
way you had your example coded in the sample you posted, you don't need those,
since the SQL SELECT statement had the Me.ID already combined into it. That
alone will select the records you want in the bottom form without any Link
field stuff.

Also, the Help Text on the LinkMasterFields property says, "If these properties
are set, Microsoft Access automatically updates the related record in the
subform when you change to a new record in a main form."

In your case, you said you have an unbound form, and your LinkMasterFields
property is making reference to something in the top subform, NOT to something
in the main form. The help text that I quoted makes specific reference to the
main form... and apparently it ain't kidding! Actually, I've used that
LinkMaster field in an unbound mainforn, but I was referencing a control bound
to the form... and that worked. I suspect that whatever the value of that
LinkMasterFields property is, it has to be bound, and that value that you had
there was NOT bound because of the bang. And when you changed it from
"fsubProjects!ID" to "fsubProjects.Form.ID" it became bound. BTW, I don't
really understand "!" too much (I'm a C programmer and fairly new to VBA), so
I'm just flying by the seat of my pants....

It looks to me like you have been beating your head against the wrong wall.
The problem wasn't with your statements in the Form_Current subroutine in the
top form, but with the value in the LinkMasterFields property of the botton
SubForm control. However, I wouldn't bet any money on it until I tried it....

If it turns out that the bang is the problem, I'd be interested in knowing if
it was actually in there when the form worked with Access 2000.


Rick
 
Rick,
Originally, in Acc2K, I was using the Child and Parent links to do all this
updating for me (and yes, the bang reference worked fine in 2000), but
that's what I couldn't get to work in 2003. So then I went to "Plan B" and
tried to manually update the RecordSource of the second subform when the
first subform changed. Sorry, I was probably jumbling them all together
when I was posting earlier.

I'm not sure when they got rid of the bang referencing and changed it all to
dot referencing, but both 2000 and 2003 seem to accept the dot referencing
just fine, so I may be able to run both versions simultaneously. That's a
nice change from previous Access updates, where we had to upgrade everything
at once or else have special versions of the apps for each version of
Access.
 
Back
Top