Let's try again, how about command line switches to pass a variabl

[Guest]

Okay, I want to split off reports and queries from my main database, because
trying to wade through this mountain of queries is cumbersome from a support
perspective....

I want to move reports and queries that are all functionally related to
another database with linked tables, but I want to call the reports from my
original database, by clicking on a form button, but when I run that report,
I want it filtered to match the record I'm looking at in my form....

I got no response to my first query about how to make that call....

My next post was answered, but where we created an OpenReport function and
tried to pass the variable to that function, but for some reason it creates
the reference, but never recognizes the function on that database that was
created.

I was thinking if I used a simple command line to open that database, I'd
like to pass the variable along that way.

If I have an AutoExec macro, or an AutoExec macro that runs code, can I pass
the variable I want using the /cmd switch on the command line?

If so, can someone point me to some examples of how that would be set up? I
can't find any real life examples of using the /cmd switch anywhere.

So, if I want to pass [CustomerNumb], which is equal to "15" using that
switch....

Application.FollowHyperlink "C:\MyFiles\db2.mdb /cmd 15"

Application.FollowHyperlink "C:\MyFiles\db2.mdb /cmd CustomerNumb=15"

Application.FollowHyperlink "C:\MyFiles\db2.mdb /cmd ""CustomerNumb=15"""

Application.FollowHyperlink "C:\MyFiles\db2.mdb /cmd ""[CustomerNumb]=15"""

Or does something else work for that?

Or is there a much, much simpler way to pass a variable between databases
that my lack of experience has me missing here?

 

[Guest]

I got it to work....

In DB1 (the main DB)...

----------------
Sub cmdprevrptDistrictProfileRedo_Click()

Dim CmdLineText As String

CmdLineText = "T:\Starter\Starts.mdb /cmd" & " """ &
[Forms]![frmMAINPROGRAMS]![DistrictID] & """"

Shell "C:\Program Files\Microsoft Office\Office\MSAccess.exe " &
CmdLineText

End Sub
---------------------------------
That opens a shell with the following command, if in my example the customer
number is 15:

C:\Program Files\Microsoft Office\Office\MSAccess.exe T:\Reps\db2.mdb /cmd
"15"

On my DB2, I have an AutoExec that runs the Function "CheckCommandLine"

----------------------------
Function CheckCommandLine()
' Check value returned by Command function.
Dim CmdNumber As Long

CmdNumber = Val(Command)

OpenReportCard (CmdNumber)
End Function

---------------------------------
Function OpenReportCard(DistrictID As Long)

DoCmd.OpenReport "rpt2006Profile", acViewPreview, , "[DistrictID]= " &
DistrictID


End Function

 

[Tim Ferguson]

Or is there a much, much simpler way to pass a variable between
databases that my lack of experience has me missing here?

It seems to me that you are trying to pass things between GUIs, not
between databases. Reports are not part of the data model. Queries are
part of the data model and can be seen via a DAO Database object, but
they are not much use without a GUI.

If you really want to open a report in a different mdb, then I cannot see
anyway other than

(a) OLE automation using GetObject() etc etc. Horrid

(b) using the /cmd start up switch. I've never used it, nor can I
imagine any reason for wanting to. There is a VBA function for
retrieving it. This, however, seems to work for me:

Command line (all one line, obviously)

"%officedir%\msaccess.exe" "%datdir%\Uncle.mdb" /x StartHere /cmd "Hello
world"

Macro name StartHere, has one line RunCode EchoCommand,

Module has one function vis:-

Option Explicit

Public Function EchoCommand() ' void as called from a macro.RunCode

MsgBox "You passed this command: " & Command()

End Function


Now, that took me about six minutes, including looking up the help files.
Had you actually tried doing it before asking?

I have to say, at the same time, that the whole question seems to be a
non-event.

All this complication stems from a decision to move all the reports to a
second mdb because it's "cumbersome from a support perspective". I think
you have proved that your alternative is a whole lot more cumbersome. Why
can't you just document the stuff you have, adopt a decent naming
convention and live with it?

Having taken the decision to move the things, you then insist, for no
obvious reason, on running them from the original mdb. What on earth is
wrong with opening the second mdb and just clicking on the things?

May I finally suggest a really radical alternative? You might actually do
some analysis of what you are trying to do, get some idea of the work
flow and separate the stuff, not on the basis of, 'reports here, queries
there' etc; but on the basis of "sales here, admin there, despatches in
the other one". If you have single processes that are so monolithic and
complex that they can't be atomised, then (a) you may have a business
process problem or (b) the people will just have to put up with a complex
interface. Or both... But at least you would know, and won't go trying
to make up for this false catabolism.

Just a thought...


Tim F

 

[Guest]

Thanks for the attitude Tim... to address your points

It seems to me that you are trying to pass things between GUIs, not
between databases. Reports are not part of the data model. Queries are
part of the data model and can be seen via a DAO Database object, but
they are not much use without a GUI.

If you really want to open a report in a different mdb, then I cannot see
anyway other than

(a) OLE automation using GetObject() etc etc. Horrid

(b) using the /cmd start up switch. I've never used it, nor can I
imagine any reason for wanting to. There is a VBA function for
retrieving it. This, however, seems to work for me:

Command line (all one line, obviously)

"%officedir%\msaccess.exe" "%datdir%\Uncle.mdb" /x StartHere /cmd "Hello
world"

Macro name StartHere, has one line RunCode EchoCommand,

Module has one function vis:-

Option Explicit

Public Function EchoCommand() ' void as called from a macro.RunCode

MsgBox "You passed this command: " & Command()

End Function


Now, that took me about six minutes, including looking up the help files.
Had you actually tried doing it before asking?

Yes, I had tried several days and several different methods with help from
others here, without success, or I wouldn't have posted that this was my
third go-around. But hey, feel free to be a jerk about it. If you bothered
looking at the whole thread, you'll see that I wanted to use the /cmd switch,
but there's nothing anywhere in help that actually tells you how that
information is accessed once it's passed.

I have to say, at the same time, that the whole question seems to be a
non-event.

All this complication stems from a decision to move all the reports to a
second mdb because it's "cumbersome from a support perspective". I think
you have proved that your alternative is a whole lot more cumbersome. Why
can't you just document the stuff you have, adopt a decent naming
convention and live with it?

Actually, we haven't proved anything of the sort. By isolating this bit,
functionally, I've made the maintenance aspect of updating these reports a
ten minute affair, mostly of importing outside, one time data, from the
several day nightmare that it was before. The only missing piece was passing
the variable, which I managed to solve on my own, through bits and pieces
from a number of sites similar to this one, and without the sneering
feedback from a less than helpful person.

(A) This is not a database of my creation.
(B) It will need to be maintained by low-level end-users.
(C) There are literally hundreds of queries. No matter what your naming
convention is, it's much simpler to split off the functions that naturally
belong together.

Having taken the decision to move the things, you then insist, for no
obvious reason, on running them from the original mdb. What on earth is
wrong with opening the second mdb and just clicking on the things?

For me? Not a damn thing. For the end user who knows absolutly nothing
about Access, and who would very quickly get lost trying to remember what
subdirectories to look for on the network? Everything is wrong with that.

I realize that it's probably a foreign concept to someone so devoted to
Microsoft products, but the beauty and power of computers comes from ease of
use to the non-expert. By having a central interface where they can press a
button, labelled with what it actually does, to pull up what the end-user
wants, is what I'm here to help them do. Given your attitude, I can see that
the being helpful is a somewhat foreign concept.

May I finally suggest a really radical alternative? You might actually do
some analysis of what you are trying to do, get some idea of the work
flow and separate the stuff, not on the basis of, 'reports here, queries
there' etc; but on the basis of "sales here, admin there, despatches in
the other one". If you have single processes that are so monolithic and
complex that they can't be atomised, then (a) you may have a business
process problem or (b) the people will just have to put up with a complex
interface. Or both... But at least you would know, and won't go trying
to make up for this false catabolism.

Just a thought...


Tim F

That's exactly what I'm doing. I'm splitting off these functions because
they don't interact with most of the core functions of the main database.
The main database, BTW, doesn't contain the core data, either. When this was
designed and built, years before I ever saw it, this main application/GUI was
built with the tables completely separate, without any additional forms,
reports or queries.

It's clear the original designer intended that functions to be structured in
a way that was distributed from the central data repository, and as long as
you can make the parts talk to each other, minimally, it makes sense.

I'm talking about passing a single variable between databases, not rocket
science. I've used and written a lot of databases in other applications and
other languages that do this quite easily. Unfortunately, this group I'm
working with now has follwed the herd and chose Access.

So I try to make due. There are limitations to what I will be allowed to
do, as far as restructuring the database, and there are limitations also
based upon who will be both using and maintaining the database in the future
- the IS department isn't interested in maintaining this for them, and the
end-users can only be expected to be trained up to a certain degree.

What I'm doing is neither random, nor haphazard, but is in fact based upon
analysis of how the database is now, how it got here, what can be done with
it now, and how it will have to be maintained in the future. The main focus
being accuracy, flexibility and ease of use for the end-user.

Thanks for nothing, and in the future, if you don't have any help to offer,
spare us all the twerp act.