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
You can answer both in English or Spanish. Thank you very much.
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
You can answer both in English or Spanish. Thank you very much.
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(strComp
'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
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(strComp
'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
You can also use Environ("ComputerName")
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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
Actually, you can ... by setting a Reference to that MDB. Same thing you do in a library MDA (Add-In or otherwise).
mx
....Ouch :<
concurring with LSM .... API's calls are a beautiful thang ... one of the most powerful features of Access / VBA.
mx
mx
ASKER
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.
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.
ASKER
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.
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.
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
Cheers, Andrew
Call Shell("C:\Archivos de programa\Microsoft Office\Office\MSACCESS.EXE
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