Re: Opening report in another db

[lyle fairfield]

"dpcman01" <bo*@dpcman.com.auwrote in
news:48***********************@news.optusnet.com.a u:

Sorry, I posted the previous message in haste without sufficient
checking. I rechecked Dev's code and found that I'd omitted the second
apiShowWindow call.
So now the report opens fine, but with the built in menubar and
toolbar instead of my custom Menubar.
The custom one is there when I open the ReportLibrary.mdb directly,
but not when opened from from this code.
So I added the line:
.Reports("rCheque").MenuBar = "DPCPreview" after opening the
report.
Now I find that it still opens without the custom menubar, but if I
right click anywhere on the screen, the custom menu appears and the
built in ones vanish.
Has anyone else experienced this problem?

Much of the code at the MVP site gave excellent results last century.

If I were opening a report in another db I would
(using Northwind 2007.accdb as an example)
1. Write a procedure in that db as:

Public Function OpenEmployeesPhoneBookReport()
DoCmd.OpenReport "Employee Phone Book", acViewPreview
End Function

2. Set a reference to that db from the current/home db (the db in which I
will be working);

3. Write a function in the current/home db (the db in which I will be
working) as:

Public Function OpenNorthwind2007EmployeesPhoneBookReport()
[Northwind 2007].OpenEmployeesPhoneBookReport
End Function

I can think of three advantages of this method:
1. AutoExec Macros and Startup Forms in the referenced db are not run;
2. The referenced db does not have to be closed;
3. There may be less chance of confusing the two dbs as there is when we
create a new instance of the Access application.

--
lyle fairfield

[Larry Linson]

Ah, good to see you, Lyle, and to see you in good form, as well -- keeping
up with the times. BTW, how's your Y3K Consulting offering going? Mine is,
to be honest, a "slow sell".

Larry

"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn**********************@216.221.81.119...

"dpcman01" <bo*@dpcman.com.auwrote in
news:48***********************@news.optusnet.com.a u:

>Sorry, I posted the previous message in haste without sufficient
checking. I rechecked Dev's code and found that I'd omitted the second
apiShowWindow call.
So now the report opens fine, but with the built in menubar and
toolbar instead of my custom Menubar.
The custom one is there when I open the ReportLibrary.mdb directly,
but not when opened from from this code.
So I added the line:
.Reports("rCheque").MenuBar = "DPCPreview" after opening the
report.
Now I find that it still opens without the custom menubar, but if I
right click anywhere on the screen, the custom menu appears and the
built in ones vanish.
Has anyone else experienced this problem?

Much of the code at the MVP site gave excellent results last century.

If I were opening a report in another db I would
(using Northwind 2007.accdb as an example)
1. Write a procedure in that db as:

Public Function OpenEmployeesPhoneBookReport()
DoCmd.OpenReport "Employee Phone Book", acViewPreview
End Function

2. Set a reference to that db from the current/home db (the db in which I
will be working);

3. Write a function in the current/home db (the db in which I will be
working) as:

Public Function OpenNorthwind2007EmployeesPhoneBookReport()
[Northwind 2007].OpenEmployeesPhoneBookReport
End Function

I can think of three advantages of this method:
1. AutoExec Macros and Startup Forms in the referenced db are not run;
2. The referenced db does not have to be closed;
3. There may be less chance of confusing the two dbs as there is when we
create a new instance of the Access application.

--
lyle fairfield

[dpcman01]

Thanks Lyle,
I'd already been down that track, but couldn't get it to work from
distributed Access 2002 mde's.
The problem was finding the path to the reference when installed on
different user systems, since the reference can't be changed in an mde at
runtime.
Some users have stand-alone PC's and others are using Win2003 Server with
TS.
Do you have any ideas to overcome this reference addressing issue?
--
Bob Darlington
Brisbane
"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn**********************@216.221.81.119...

"dpcman01" <bo*@dpcman.com.auwrote in
news:48***********************@news.optusnet.com.a u:

>Sorry, I posted the previous message in haste without sufficient
checking. I rechecked Dev's code and found that I'd omitted the second
apiShowWindow call.
So now the report opens fine, but with the built in menubar and
toolbar instead of my custom Menubar.
The custom one is there when I open the ReportLibrary.mdb directly,
but not when opened from from this code.
So I added the line:
.Reports("rCheque").MenuBar = "DPCPreview" after opening the
report.
Now I find that it still opens without the custom menubar, but if I
right click anywhere on the screen, the custom menu appears and the
built in ones vanish.
Has anyone else experienced this problem?

Much of the code at the MVP site gave excellent results last century.

If I were opening a report in another db I would
(using Northwind 2007.accdb as an example)
1. Write a procedure in that db as:

Public Function OpenEmployeesPhoneBookReport()
DoCmd.OpenReport "Employee Phone Book", acViewPreview
End Function

2. Set a reference to that db from the current/home db (the db in which I
will be working);

3. Write a function in the current/home db (the db in which I will be
working) as:

Public Function OpenNorthwind2007EmployeesPhoneBookReport()
[Northwind 2007].OpenEmployeesPhoneBookReport
End Function

I can think of three advantages of this method:
1. AutoExec Macros and Startup Forms in the referenced db are not run;
2. The referenced db does not have to be closed;
3. There may be less chance of confusing the two dbs as there is when we
create a new instance of the Access application.

--
lyle fairfield

[lyle fairfield]

"dpcman01" <bo*@dpcman.com.auwrote in
news:48**********************@news.optusnet.com.au :

Do you have any ideas to overcome this reference addressing issue?

No, not under the circumstances you describe.

But how to show the report?

My code (below) works but not flawlessly.

I think I would import the report into the user db and find some way to
link to the tables, views or whatever on which it is based.

As a last resort I'd create a db with only that report and an autoexec
macro which opened the report (and a link to the data) in it. In the
reports closing code I'd put:
Quit.
So ... open the db and the report shows.
Close the report and the db closes.

and open the report db with whatever command is appropriate in your
working db.

----------------------------------
.... this is quite gnarly in my opinion.

Private Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd&, ByVal ShowCommand&)
Declare Function SetForegroundWindow& Lib "user32" _
(ByVal hwnd&)
Const Maximized& = 3
Dim AccessApplication As Access.Application

Public Function OpenExtraneousReport( _
ByVal Database$, _
ByVal Report$)
If AccessApplication Is Nothing Then _
Set AccessApplication = GetObject(Database)
With AccessApplication
ShowWindow .hWndAccessApp, Maximized
SetForegroundWindow .hWndAccessApp
.DoCmd.OpenReport Report, acViewPreview
End With
End Function

Sub test()
OpenExtraneousReport "Northwind 2007.accdb", "Employee Phone Book"
End Sub

--
lyle fairfield

[Sky]

"dpcman01" <bo*@dpcman.com.auwrote in message
news:48**********************@news.optusnet.com.au ...

Thanks Lyle,
I'd already been down that track, but couldn't get it to work from
distributed Access 2002 mde's.
The problem was finding the path to the reference when installed on
different user systems, since the reference can't be changed in an mde at
runtime.
Some users have stand-alone PC's and others are using Win2003 Server with
TS.
Do you have any ideas to overcome this reference addressing issue?
--
Bob Darlington
Brisbane

If you install the referenced library database .mde file in the same folder
as the front-end application .mde, then the front-end will find and
reference the library database automatically, even if it was compiled in a
different path on the developer's computer. I have used this feature in both
Access 2000 and 2003. I would expect it works the same way in Access 2002.

Another option is to create the RefLibPaths registry entry, but that has
proved more troublesome for me, especially if you install more than one
application on the same customer computer.

- Steve

[dpcman01]

Thanks Steve and Lyle.
I'll let you know the outcome.

--
Bob Darlington
Brisbane
"dpcman01" <bo*@dpcman.com.auwrote in message
news:48**********************@news.optusnet.com.au ...

Thanks Lyle,
I'd already been down that track, but couldn't get it to work from
distributed Access 2002 mde's.
The problem was finding the path to the reference when installed on
different user systems, since the reference can't be changed in an mde at
runtime.
Some users have stand-alone PC's and others are using Win2003 Server with
TS.
Do you have any ideas to overcome this reference addressing issue?
--
Bob Darlington
Brisbane
"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn**********************@216.221.81.119...

>"dpcman01" <bo*@dpcman.com.auwrote in
news:48***********************@news.optusnet.com. au:

>>Sorry, I posted the previous message in haste without sufficient
checking. I rechecked Dev's code and found that I'd omitted the second
apiShowWindow call.
So now the report opens fine, but with the built in menubar and
toolbar instead of my custom Menubar.
The custom one is there when I open the ReportLibrary.mdb directly,
but not when opened from from this code.
So I added the line:
.Reports("rCheque").MenuBar = "DPCPreview" after opening the
report.
Now I find that it still opens without the custom menubar, but if I
right click anywhere on the screen, the custom menu appears and the
built in ones vanish.
Has anyone else experienced this problem?

Much of the code at the MVP site gave excellent results last century.

If I were opening a report in another db I would
(using Northwind 2007.accdb as an example)
1. Write a procedure in that db as:

Public Function OpenEmployeesPhoneBookReport()
DoCmd.OpenReport "Employee Phone Book", acViewPreview
End Function

2. Set a reference to that db from the current/home db (the db in which I
will be working);

3. Write a function in the current/home db (the db in which I will be
working) as:

Public Function OpenNorthwind2007EmployeesPhoneBookReport()
[Northwind 2007].OpenEmployeesPhoneBookReport
End Function

I can think of three advantages of this method:
1. AutoExec Macros and Startup Forms in the referenced db are not run;
2. The referenced db does not have to be closed;
3. There may be less chance of confusing the two dbs as there is when we
create a new instance of the Access application.

--
lyle fairfield

[dpcman01]

--
Bob Darlington
Brisbane
"Sky" <s.young @ stanley associates . comwrote in message
news:fT***************@nwrddc01.gnilink.net...

"dpcman01" <bo*@dpcman.com.auwrote in message
news:48**********************@news.optusnet.com.au ...

>Thanks Lyle,
I'd already been down that track, but couldn't get it to work from
distributed Access 2002 mde's.
The problem was finding the path to the reference when installed on
different user systems, since the reference can't be changed in an mde at
runtime.
Some users have stand-alone PC's and others are using Win2003 Server with
TS.
Do you have any ideas to overcome this reference addressing issue?
--
Bob Darlington
Brisbane

If you install the referenced library database .mde file in the same
folder as the front-end application .mde, then the front-end will find and
reference the library database automatically, even if it was compiled in a
different path on the developer's computer. I have used this feature in
both Access 2000 and 2003. I would expect it works the same way in Access
2002.

Another option is to create the RefLibPaths registry entry, but that has
proved more troublesome for me, especially if you install more than one
application on the same customer computer.

- Steve

Steve,
I followed your advice and loaded the referenced mde in the same folder as
the front end mde, but get an error message indicating that there is a
missing reference (ie "The expression you entered has a function that ...
can't find" .
If I load the front end as an mdb, there's no problem, but my distributed
app needs to be an mde.
I might need to look at your second suggestion with RefLibPaths, but here
again, couldn't I expect to have the same problem from an mde front end?