Call a form from an external MDB

We work in a multiuser & multiPC basis. One user works always in a given machine and other user with other functions work in another PC. With this in mind, I have in a common-to-all network drive one MDB with only the data tables, and several MDBs with forms, modules, etc accessing these tables. All is working and fine.

Now I've been required to make one MDB in each PC, different for each one (so fo each user), containing only a form with CommandButtons to call the required network MDBs. Something like a "personalized menú". This has led me to 2 problems:

1. How can I call a MDB with a name containing spaces? I can call for example "john.mdb" but I want to call "john john.mdb".

2. The main problem: how can I call a determined form inside the remote MDB?. For example, if I have an MDB "container.mdb" with the forms "Form1" and "Form2", now I'm calling only container.mdb, but I want to press the button and open Form1 inside container.mdb.

By now I'm using this simple code:
    Dim stAppName As String
    stAppName = "G:\Path\Database name.mdb"
    Call Shell("C:\Archivos de programa\Microsoft Office\Office\MSACCESS.EXE " & stAppName, vbMaximizedFocus)

You can answer both in English or Spanish. Thank you very much.
LVL 1
BETTYAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
To call the MDB with names the filename needs to be wrapped in "

Call Shell("C:\Archivos de programa\Microsoft Office\Office\MSACCESS.EXE " & Chr(34) & stAppName & Chr(34), vbMaximizedFocus)

Not sure how yoy are determining the user or PC

If you have set your Startup Form then you can change this to an AutoExec macro to run code that then opens the appropriate form.

Cheers, Andrew
0
Live4SurfCommented:
That would probably be best, by having a start up form that has code in it something like this:

Private Declare Function apiGetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Integer

'Returns the computername
Function GetMachineName() As String
On Error GoTo Errorhnd
Dim lngLen As Long
Dim lngX As Integer
Dim strCompName As String
lngLen = 12
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
'If lngX < 0 Then
GetMachineName = Left$(strCompName, lngLen)
'Else
'GetMachineName = ""
'End If
Exit Function
Errorhnd:
    GetMachineName = ""
End Function



And from there you can easily say

Select Case GetMachineName
     Case PCName1
          Button1.visible = true
          Button2.visible = true
     Case PCName2
          Button3.visible = true
          Button4.visible = true
     Case PCName3
          Button5.visible = true
          Button6.visible = true
     Case Else
          Docmd.Quit
End Select

Regards
0
TextReportCommented:
You can also use Environ("ComputerName")
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Live4SurfCommented:
Much easier! Thanks TextReport.. That even helped me out, saves stupid API calls that im using.

Select Case Environ("ComputerName")
     Case "PCName1"
          Button1.visible = true
          Button2.visible = true
     Case "PCName2"
          Button3.visible = true
          Button4.visible = true
     Case "PCName3"
          Button5.visible = true
          Button6.visible = true
     Case Else
          Docmd.Quit
End Select
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Most devs prefer to use those "stupid" API calls ...

You can always automate Access:

Dim mAcc as Access.Application

Set mAcc = New Access.Application
'/open the database
mAcc.OpenCurrentDatabase "full path to your db"
'/launch the form
mAcc.DoCmd.OpenForm "Form1"
'/now make it visible
mAcc.Visible = True

0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
You cannot directly open a Form in another MDB from within the current DB, but you can do the following:

In the customized menu button for the local MDB, use the Shell command as already explained by several experts, but you can use an additional parameter to pass to Microsoft Access, which will execute a Macro in the Container MDB. So you can do as per code snippet:

Note:
1. The code illustrates how to open MDB files containing spaces in the name; i.e. surround the name with double set of quotation marks.
2. The /x switch passed to Microsoft Access via the Shell command tells Access to execute the macro macOpenForm1. This means that you will have to create a simple macro in the container MDB, called
macOpenForm1 (or rename to whatever you like), which contains the macro actions OpenForm, filling the parameters of the name of form to open and any other parameters you see fit. Also, if the 'external' database is expected to close after the form has been opened, then you might also need to add a Quit macro action after this.
3. If you setup uses workgroup security, then you can easily modify your Shell command with the additional swicthes, which might end up like (see snippet 2).
Private Sub cmdCommand1_Click
 
Dim strPath As String
strPath = "C:\Archivos de programa\Microsoft Office\Office\MSACCESS.EXE ""G:\Path\Database name.mdb"" /x ""macOpenForm1"""
Shell strPath, vbNormalFocus
 
End Sub
 
 
 
------------ Snippet 2 -------------
Private Sub cmdCommand1_Click
 
Dim strPath As String
strPath = "C:\Archivos de programa\Microsoft Office\Office\MSACCESS.EXE ""G:\Path\Database name.mdb"" /x ""macOpenForm1"" /wrkgrp ""G:\Path\Database workgroup filename.mdw"" /user ""user1"" /pwd ""password1"""
Shell strPath, vbNormalFocus
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"You cannot directly open a Form in another MDB from within the current DB,"

Actually, you can ... by setting a Reference to that MDB.  Same thing you do in a library MDA (Add-In or otherwise).

mx
0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
....Ouch :<
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
concurring with LSM .... API's calls are a beautiful thang ... one of the most powerful features of Access / VBA.

mx
0
BETTYAuthor Commented:
Wow, how much activity, dudes!!

There's no problem is determining which user is in each computer: only a person is allowed to login in that PC. So it's not necessary to evaluate it, only to put the "menu MDB" in local in each one, only this the desired buttons. Easy enough to have one menu MDB for PC, as there are only 5. It's "work-of-newbie", but as I have no control over the entire network administration, I don't want to be changing PC names, etc every time the admins decide to do it... and believe me, it's like they were making tests once a week!!

Let me wait to test your proposals until tuesday, as I have Office XP 2002 at home, and I need it to work in the Office97 we still have at work. Thanks.

0
BETTYAuthor Commented:
LSMConsulting:
Your solution seems to work, too (not tested, only "by looking to"), and would be another approx to the question.

I've given the points to fredthered because his solution is directly insertable into my already typed code and it works perfectly. Furthermore, the solution to my two questions is solved in one single line.

Thanks to all for your contribution.
0
TextReportCommented:
Don't forget the /CMD on your commandline, its a good way to pass infor to the 2nd MDB rather than having to run different macro's
Cheers, Andrew
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.