Getting desperate re recordset reference...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am totally stumped by trying to move a bookmark. Line 2 crashes no matter
how I arrange the "!" and "." or insert .Form. in various positions. The
usual error is "incorrect reference." The code is in a called Module
procedure. (The FindFirst line works elsewhere, so I am confident of that.)

Dim rst2 As DAO.Recordset
Set rst2 = Forms!Main!SubFrm!SubFrmSourceObject.Recordset
rst2.FindFirst "Field1 = ..."

Forms references have always been problematic for me, but I usually can get
it by trial and error. Not this time. Help! I'm growing old!

Thanks for your time.
 
David Habercom said:
I am totally stumped by trying to move a bookmark. Line 2 crashes no
matter how I arrange the "!" and "." or insert .Form. in various
positions. The usual error is "incorrect reference." The code is in
a called Module procedure. (The FindFirst line works elsewhere, so I
am confident of that.)

Dim rst2 As DAO.Recordset
Set rst2 = Forms!Main!SubFrm!SubFrmSourceObject.Recordset
rst2.FindFirst "Field1 = ..."

Forms references have always been problematic for me, but I usually
can get it by trial and error. Not this time. Help! I'm growing
old!
Click to expand...

Aren't we all!

This ...
Set rst2 = Forms!Main!SubFrm!SubFrmSourceObject.Recordset
Click to expand...

.... looks screwy. A reference to the properties of the form displayed
in a subform control should be made like this:

Forms!<MainFormName>!<SubformControlName>.Form.<PropertyName>

In the above, all the bracketed items are to be replaced by the name of
the actual object you want to refer to. So make replacements as
follows:

Placeholder Replace With
---------------------------------- ------------------------------
<MainFormName> name of main form
<SubformControlName> name of subform control*
<PropertyName> name of property

*Note: the "subform control" is the control, of type "subform", on the
main form -- the control that serves as the window to display the form
object that is acting as a subform.

In the example you gave, you were using the Recordset property. It may
be, though, that you want the RecordsetClone property instead. That
will depend on what you want to do with it. If all you want to do is
position the subform to the record located by FindFirst, the Recordset
property will do fine.
 
Dirk, in fact that was the exact format I last tested, if I read your post
correctly. The exact text is
Set rst2 = Forms!SwitchboardNew!EndwmtSubFrm.Form.Recordset

The error mssg is 2455: You entered an expression that has an invalid
reference to the property Form/Report.

Can you think of anything else that might cause this?

Recordset is the optimum property, as I simply want to move to a record.

Thanks.
 
Is EndwmtSubFrm the name of the subform control on form SwitchboardNew? The
control may or may not have the same name as the form being used as a
subform.
 
David Habercom said:
Dirk, in fact that was the exact format I last tested, if I read your
post correctly. The exact text is
Set rst2 = Forms!SwitchboardNew!EndwmtSubFrm.Form.Recordset

The error mssg is 2455: You entered an expression that has an invalid
reference to the property Form/Report.

Can you think of anything else that might cause this?
Click to expand...

Please verify that "EndwmtSubFrm" is really the name of the subform
control on the main form, and not just the name of the form object that
the subform control displays. If you made the subform by dragging and
dropping the form from the database window onto the design canvas of the
main form, the subform control's name will be created from the Caption
property of the form, if that has been specified. Only if the form has
no caption will the name of the form be used for the subform control.
And if you made the subform by using the Subform/Subreport tool from the
toolbox, the name will be "Subform1" or something like that.

Also, make sure we're really talking about a subform control here, and
not a list box.

You might try the fully qualified and expanded syntax for the reference:

Set rst2 =
Forms("SwitchboardNew").Controls("EndwmtSubFrm").Form.Recordset
 
Dirk & Doug,

Yes, EndwmtSubFrm is the frame on SwitchboardNew, and its source object is
SwitchboardEndwmtSubFrm.

I guess my naming habits aren't the best, huh?
 
David,
Lately, I have had success using the a simpler syntax:
Form_sfmForm.Recordset in VisualBasic.
I am using Access 2003. You see the available Form_ names in the Project
window on the top left window in Visual Basic

Therefore, try rst2=Form_EndwmtSubFrm.RecordSet
 
David Habercom said:
Dirk & Doug,

Yes, EndwmtSubFrm is the frame on SwitchboardNew, and its source
object is SwitchboardEndwmtSubFrm.
Click to expand...

I'm puzzled. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem,
compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it
to the address derived by removing NO SPAM from the reply address of
this message. If that address isn't visible to you, you can get it from
my web site, which is listed in my sig. Do *not* post my real address
in the newsgroup -- I don't want to be buried in spam and viruses.
 
Dirk, I appreciate the offer, but I think it would not be practical. The
data structures & relationships are hideously complex with multiple
cross-references. It reflects the nature of our work, which is private fund
raising for the university. On top of that, the data referring to wealthy
donors is very confidential, and I would spend more time "sanitizing" it than
the problem justifies. And more time stripping the data down to a small
sample.

I do have a fall-back on this problem, which is to run the code from inside
another subform, where it already works very nicely. But I want to run the
same code from the main form as well, so I was trying to follow (what I
imagine to be) good practice by writing the code once in a called procedure.
That is where the failure occurs. I'll just write it twice and move on with
life...

Thanks to everybody for taking the time. I hope you have a great weekend.
I'm headed to the Smokies...

Best regards.
 
David Habercom said:
Dirk, I appreciate the offer, but I think it would not be practical.
Click to expand...

How about just importing the form and subform into a new blank
database -- no tables, no queries, nothing but the forms -- and sending
me that. I'm really puzzled, and maybe that will let me see what's
going on.
Thanks to everybody for taking the time. I hope you have a great
weekend. I'm headed to the Smokies...
Click to expand...

Have a good time.
 
Dick D said:
David,
Lately, I have had success using the a simpler syntax:
Form_sfmForm.Recordset in VisualBasic.
I am using Access 2003. You see the available Form_ names in the
Project window on the top left window in Visual Basic

Therefore, try rst2=Form_EndwmtSubFrm.RecordSet
Click to expand...

I don't recommend that approach, as a rule. It works, so long as there
is only one instance of the form object currently open. But if the same
form could be open in more than one subform control, or as a main form
and subform at the same time, or in any other arrangement where there's
more than one instance of the form's class module currently in memory,
you don't know which instance that type of reference will be acting on.
 
Back
Top