Report object in a form? Huh? VBA error

  • Thread starter Thread starter AndyCotgreave
  • Start date Start date
A

AndyCotgreave

Hi,
I've inherited a database for a company I do support work for, and it
has a problem with its database that I don't understand.

When I copy the day's copy of the database onto my laptop it works fine
on my laptop.

The problem is, I then do the following:
1. Open the VB editor (Alt+F11). At this stage, that's ALL I do. I
don't change a thing
2. Return to the database. Click on one of the buttons to show company
details (this is normal usage)
3. I now get run-time error 2467 ("The expression you entered refers to
an object that is closed or doesn't exist") on the following line:

Set rs = FinForm!FinancialsSubform_Accounts.Report.Recordset

The question, then, is WHY do I get an error on this line after doing
nothing more than opening the VB Editor?

And secondly, I don't understand how or why a subfom has its own Report
- I thought Reports was just an application-wide collection.

The above line DOES work if I don't open the VB editor...

I'm running Access 2003, v11.6566.6568 SP2 (on Windows XP SP2)

Thanks for help in advance,

Andy
 
Hey Andy,

Why are using ".Report." anyway? Unless I am incorrect in doing so
(maybe someone can school me on this), I typically set a recordset in
this fashion:

Set rs = CurrentDB.OpenRecordset("mytablename")

Tammy
 
Why are using ".Report." anyway?

I have no idea - this is code I inherited. It's stumped me completely,
because I would do it the same way as you.

What's confusing me is that I don't understand how a Form can have a
Report object inside it? I've never come across this before.

And even when I answer that question, there's the issue that it's
making my database break without me making any changes...!

Andy
 
Andy,

Much like you can have a SubForm in your Form, you can have a SubReport
in your Form. Maybe the ".Report." should be used in the case of a
SubReport. Personally, I have not found a need to use SubReport in my
forms and have not seen samples using this either.

Any chance your inherited code was originally pointing to a SubReport
in this line?

I would change the code and write it the way you are accustomed.

Tammy
 
In Access 97 and earlier, Access had its own VBA window inside the
application. In Office 2000, Microsoft removed this and losely cobbled
Access together with a copy of the VB IDE instead. This led to a serious
loss of functionality at several levels, such as:
- can no longer compile just loaded modules;
- can no longer have different users working on different modules;
- monolithic save (performance loss);
- annoying confirmation messges where you are forced to respond to every
object (such as a mock-up query to help get the SQL right) when you save
code;
- failure to maintain the date/time in MSysObjects;
- duplicated copies of the names of objects (e.g. AllForms verses
MSysObjects), opening the door to another form of corruption;
and so on.

Another side effect is that Access does not really have a clue what is going
on in the VBA window, so merely opening the code window causes the database
to treat the code as decompiled. The behavior you describe implies that
merely decompiling is causing the error. That in turn suggests that there is
a corruption in the database - a difference between the compilied version of
the code and the text version.

If that is the case, a decompile will probably fix it. Decompile a copy of
the database by entering something like this at the command prompt while
Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact the database.

If the company is actually using an earlier version of Access (2000 or
2002), and you are working on it in A2003, I suggest that you decompile
again before you give the mdb back to them again. Then compact, and do not
open the code again. This will dump the A2003 binary (which cannot be used
by earlier versions anyway), and can avoid hard to trace errors.

For other suggestions on how to avoid corruption in your database, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

Regarding the reference:
FinForm!FinancialsSubform_Accounts.Report.Recordset
it appears that "FinancialsSubform_Accounts" is the name of a subform
control, and it contains a report (the report named in its SourceObject
property). So, the actual report loaded into the subform is referenced in
that way. I'm not sure that the Recordset property of the report in the
subform is exposed through that property though: reports use a forward
scolling recorset, and I would not expect that approach to work once the
report has run.
 
Thanks Tammy.

Potentially dumb question:
How can I check if there is a sub report on the form? It'll be there as
clear as day, will it?

The reason I ask is that I want to be wary of missing some "subtle"
piece of Access that I've not come across before.

In the meantime, I think I have a fix like this:

I've change the line of code from this:
Set rs = FinForm!FinancialsSubform_Accounts.Report.Recordset

to the following:
Set rs = Form_FinancialsSubform_Accounts.Recordset

The FinForm was a form passed to the Sub as a parameter. But, that form
always had the same subform, so i now access the subform directly.

And, it seems that I can get hold of the recordset directly, without
going through the mysterious sub report.
 
Andy,

Good solution. That is another way to Set the Recordset. You can look
at all the controls in the design view of the form for the SubReport.
They usually stick out like SubForms. The obvious TextBoxes,
ComboBoxes, Option Buttons, etc are simple to pick out but ListBoxes
and SubForms/Reports tend to look similar to each other.

Typically if you right click on the control and bring up the
Properties, you will see ListBox: in the title bar of the ListBox and
so forth. However, for SubForms/Reports you will see SubForm/SubReport
in the title bar.

The difference between a SubForm and a SubReport is that you will have
other controls on a SubForm and you can enter/edit data into these
controls. Controls on a SubReport are just like regular Reports. They
"hold" data from a table/query and the data cannot be edited (kind of
like a .pdf document).

Hope this makes sense. Maybe, like Allen was pointing out, there is a
SubReport within your SubForm. It is hard to tell without actually
looking at it.

Take Care,

Tammy
 
Tammy, Allen, thanks very much for your help.

Allen, I've seen your responses in my Access search results many times
- they are always helpful and well worded. You've gone a long way here,
too, to give me huge amount of detail.

It really is appreciated, particularly the decompilation stuff - the
company i do this work for is a mix of Windows 2000/XP and Office
2000/2003, so that's going to be really useful.

Ain't it funny that we work with this huge piece of software, used by
millions, and it's riddled with so many gotchas that prevent us
developing applications. Ho Hum. This morning I was battling Word and
its Number List quirks, and this afternoon it's lousy Access. Grr.

Andy
 
No worries, Andy. Hopefully we help empower one another to get this stuff
done.

Let's not start on Word's numbering problems through: we could be here all
day. :-)
 
Back
Top