?
Solved

Call a form from an external MDB

Posted on 2008-01-31
12
Medium Priority
?
622 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:BETTY
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 20794847
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
 

Expert Comment

by:Live4Surf
ID: 20795487
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
 
LVL 28

Expert Comment

by:TextReport
ID: 20795506
You can also use Environ("ComputerName")
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Expert Comment

by:Live4Surf
ID: 20795538
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
 
LVL 85
ID: 20795579
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
 
LVL 13

Accepted Solution

by:
John Mc Hale earned 800 total points
ID: 20801990
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
 
LVL 75
ID: 20802631
"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
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 20802737
....Ouch :<
0
 
LVL 75
ID: 20802745
concurring with LSM .... API's calls are a beautiful thang ... one of the most powerful features of Access / VBA.

mx
0
 
LVL 1

Author Comment

by:BETTY
ID: 20805117
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
 
LVL 1

Author Comment

by:BETTY
ID: 20813288
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
 
LVL 28

Expert Comment

by:TextReport
ID: 20813315
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month5 days, 19 hours left to enroll

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question