Access Automation - Passing a variable

[sellert]

* My machine is Windows 2000 SP4
* Some users have XP SP?
* Both using Office 2003

I have 5 different databases for Facilities 1 thru 5 which are accessible by users from our Intranet website.

The owner is determined to reduce the number of clicks by having links that directly open a specific facility. She doesn't want to click a link then have to select a facility from a form.

The web page has a separate link for each facility database.

I would like to have each facility database open the same main database and pass to it the facility name. This will alleviate having to maintain 5 different sets of queries and reports for each.

So far I have been successful in autmating the main database, the problem I am having is passing or setting the variable in the main database with the facility name that is opening it. Can anyone explain the syntax for doing this?

Here is the code that I have for one of my Facility databases:

The variable is declared in the Main database inside a global module, here is the code.

Main database Code

Option Compare Database

Public MyVariable$


Facility database Code

Option Compare Database

Public Const EWPath = "C:\Documents and Settings\sjellert\Desktop\test-two.mdb"

Public Sub OpenEWmdb()
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase EWPath
appAccess.Set MyVariable$ = "Fac1" 'This is the problem Line
End Sub


Also tried:
Facility Database Code
Option Compare Database

Public Const EWPath = "C:\Documents and Settings\sjellert\Desktop\test-two.mdb"

Public Sub OpenEWmdb()
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase EWPath
appAccess.Set Module1.MyVariable$ = "Fac1" 'This is the problem Line
End Sub


Any help that you can provide is GREATLY appreciated.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[Remou]

I do not quite get what you wish to do, because it seems to me that the main database would hold all forms, reports etc, and selecting the facility would change the linked tables, however, it seems that you may be opening a different database.

Some notes:

Sub OpenDB()

Dim ac As New Access.Application

ac.OpenCurrentDatabase ("C:\docs\ltd.mdb")

'Name of procedure that sets MyVar
ac.Run "SetVar", CurrentDb.Name

ac.Visible = True
ac.UserControl = True

End Sub

In the called DB

Option Explicit
Public MyVar

Sub SetVar(SetTo)

MyVar=SetTo
End Sub

 

[sellert]

I think your "Notes" section will get it for me. i didn't think about running a function that will set my variable for me.

My goal is to have all my reports and queries in the main database.

I have not been able to locate a resource that could explain how I could identify the facility from the Web page link that Access could pick up on. So I have a separate database for each facility that has a matching link on the web page.

Now the link for facility one can start the facility one database, that database will open the main database and cause it to set the correct facility name in the variable. I can then use this variable to set the criteria in the queries and the displayed form name so that it pulls and reports the correct data for the facility the user clicked on in the web page.

Hope this clarified what I was trying to do. I think what you sent will help greatly, I'll let you know.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[sellert]

Remou,
It worked!!! The only thing I had to change was the line that says:

ac.Run "SetVar", CurrentDb.Name

I had to change it to:

 ac.Run "SetVar"

Of course substituted my functions name.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.