a database to start other databases

  • Thread starter A Database to Start other Databases
  • Start date
A

A Database to Start other Databases

I'd like to be able to make a database that will then start other databases.
We have about 12 databases in our office and everyone forgets which database
does what. I'd like buttons to start the database and then in each db,
they'll press a "return" button to get back to the menu program.
 
A

A Database to Start other Databases

This is more than I need. I would like a simple database that just jumps to
all the other databases (there would be buttons to get you to the other
databases). I was thinking of something like using "send keys" to close the
current database, open the next database. Then when they person leaves the
other database, it goes back to the "controlling" database (that just has the
buttons to get you to the other databases). Does this make sense? Does
anyone have a db like this? Would it work?
 
D

Debra Farnham

Actually ... all that does is exactly what you have asked for.

I do not use sendkeys and am unable to assist in that regard. If you are
insistent on using sendkeys, I'm afraid someone else will have to jump in on
this thread to help you.

If you want to attempt what I have suggested:

Create a blank database that only contains a form with a few buttons on it
that allow users to click the relevant button to open one of several
different databases.

Copy the code you found when you followed the link into a new module. Then
on the onclick event of each of your buttons, all you need is:

fOpenRemoteForm "ProperPathtoDatabase\NameofDatabase.mdb",
"NameofFormToShow"


Sorry I can'be of further help.

Debra


"A Database to Start other Databases"
 
A

A Database to Start other Databases

Thanks for the input. I've copied the code and put it in a module. Should
the name of the module be something specific?

I copied the string to call it and put it in the "on click" field of a button.

It is: = fOpenRemoteForm "v:\databases\homebase.mdb", "calls form"

When I press the macro it says: MS Office can't find the macro
fOpenRemoteForm "v:\databases\homebase.'

I'm sure I've got some of the syntax or setup incorrect. Sorry, I'm not all
that knowledgable with Access.

Any suggetions? Thanks!
 
D

Debra Farnham

The call to the code has to be made in VBA ... when you go to the onclick
event of the button, press the three dots on the box at the end of the
textbox and choose Event Procedure.

Place the fOpenRemoteForm .... code before the End Sub.

Please post back if you are still having difficulties.

Debra


"A Database to Start other Databases"
 
A

A Database to Start other Databases

We are getting really close now!!! Made the change and clicking the button
did bring up the other database. Comments:
- It seemed to be slow in bring up the database (I'll do some further
checking on this to see if it is my imagination).
- When I went to close the database I went to, I got a beep and then the
following message came up: << runtime error, error #91, object variable or
with block variable not set >>

Any ideas on the error message? THANKS!!!!
 
D

Debra Farnham

Glad to hear it's close

Can you copy and paste the code you put in the module?

Deb

"A Database to Start other Databases"
 
A

A Database to Start other Databases

Here it is:

Option Compare Database

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiSetForegroundWindow Lib "user32" _
Alias "SetForegroundWindow" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) _
As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Function fOpenRemoteForm(strMDB As String, _
strForm As String, _
Optional intView As Variant) _
As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long

On Error GoTo fOpenRemoteForm_Err

If IsMissing(intView) Then intView = acViewNormal

If Len(Dir(strMDB)) > 0 Then
Set objAccess = New Access.Application
With objAccess
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
.OpenCurrentDatabase strMDB
.DoCmd.OpenForm strForm, intView
Do While Len(.CurrentDb.Name) > 0
DoEvents
Loop
End With
End If
fOpenRemoteForm_Exit:
On Error Resume Next
objAccess.Quit
Set objAccess = Nothing
Exit Function
fOpenRemoteForm_Err:
fOpenRemoteForm = False
Select Case Err.Number
Case 7866:
'mdb is already exclusively opened
MsgBox "The database you specified " & vbCrLf & strMDB & _
vbCrLf & "is currently open in exclusive mode. " & vbCrLf _
& vbCrLf & "Please reopen in shared mode and try again", _
vbExclamation + vbOKOnly, "Could not open database."
Case 2102:
'form doesn't exist
MsgBox "The Form '" & strForm & _
"' doesn't exist in the Database " _
& vbCrLf & strMDB, _
vbExclamation + vbOKOnly, "Form not found"
Case 7952:
'user closed mdb
fOpenRemoteForm = True
Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select
Resume fOpenRemoteForm_Exit
End Function
'************ Code End *************
 
D

Debra Farnham

This is probably not the best advice, but this is how I handled it in one of
my programs and so far *crossing my fingers* .. no problems.

Near the end of the code where it currently reads:

Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select

replace it with:

Case Else:
objAccess.Quit
Set objAccess = Nothing
Exit Function
End Select

Debra


"A Database to Start other Databases"
 
A

A Database to Start other Databases

WORKED PERFECTLY!!!!! Thanks again!!!


Debra Farnham said:
This is probably not the best advice, but this is how I handled it in one of
my programs and so far *crossing my fingers* .. no problems.

Near the end of the code where it currently reads:

Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select

replace it with:

Case Else:
objAccess.Quit
Set objAccess = Nothing
Exit Function
End Select

Debra


"A Database to Start other Databases"
Click to expand...
 
D

Debra Farnham

You're welcome

Thanks for taking the leap from Sendkeys

You should be proud of yourself :)

Debra

"A Database to Start other Databases"
WORKED PERFECTLY!!!!! Thanks again!!!


Debra Farnham said:
This is probably not the best advice, but this is how I handled it in one of
my programs and so far *crossing my fingers* .. no problems.

Near the end of the code where it currently reads:

Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select

replace it with:

Case Else:
objAccess.Quit
Set objAccess = Nothing
Exit Function
End Select

Debra


"A Database to Start other Databases"
Here it is:

Option Compare Database

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiSetForegroundWindow Lib "user32" _
Alias "SetForegroundWindow" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) _
As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Function fOpenRemoteForm(strMDB As String, _
strForm As String, _
Optional intView As Variant) _
As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long

On Error GoTo fOpenRemoteForm_Err

If IsMissing(intView) Then intView = acViewNormal

If Len(Dir(strMDB)) > 0 Then
Set objAccess = New Access.Application
With objAccess
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
.OpenCurrentDatabase strMDB
.DoCmd.OpenForm strForm, intView
Do While Len(.CurrentDb.Name) > 0
DoEvents
Loop
End With
End If
fOpenRemoteForm_Exit:
On Error Resume Next
objAccess.Quit
Set objAccess = Nothing
Exit Function
fOpenRemoteForm_Err:
fOpenRemoteForm = False
Select Case Err.Number
Case 7866:
'mdb is already exclusively opened
MsgBox "The database you specified " & vbCrLf & strMDB & _
vbCrLf & "is currently open in exclusive mode. " &
Click to expand...
vbCrLf
_
& vbCrLf & "Please reopen in shared mode and try again", _
vbExclamation + vbOKOnly, "Could not open database."
Case 2102:
'form doesn't exist
MsgBox "The Form '" & strForm & _
"' doesn't exist in the Database " _
& vbCrLf & strMDB, _
vbExclamation + vbOKOnly, "Form not found"
Case 7952:
'user closed mdb
fOpenRemoteForm = True
Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select
Resume fOpenRemoteForm_Exit
End Function
'************ Code End *************


:

Glad to hear it's close

Can you copy and paste the code you put in the module?

Deb

"A Database to Start other Databases"
We are getting really close now!!! Made the change and clicking the
button
did bring up the other database. Comments:
- It seemed to be slow in bring up the database (I'll do some further
checking on this to see if it is my imagination).
- When I went to close the database I went to, I got a beep and
Click to expand...
then
the
following message came up: << runtime error, error #91, object variable or
with block variable not set >>

Any ideas on the error message? THANKS!!!!

:

The call to the code has to be made in VBA ... when you go to the
onclick
event of the button, press the three dots on the box at the end
Click to expand...
of
the
textbox and choose Event Procedure.

Place the fOpenRemoteForm .... code before the End Sub.

Please post back if you are still having difficulties.

Debra


"A Database to Start other Databases"
message
Thanks for the input. I've copied the code and put it in a module.
Should
the name of the module be something specific?

I copied the string to call it and put it in the "on click"
Click to expand...
field
of a
button.

It is: = fOpenRemoteForm "v:\databases\homebase.mdb", "calls form"

When I press the macro it says: MS Office can't find the macro
fOpenRemoteForm "v:\databases\homebase.'

I'm sure I've got some of the syntax or setup incorrect.
Click to expand...
Sorry,
I'm
not
all
that knowledgable with Access.

Any suggetions? Thanks!


:

Actually ... all that does is exactly what you have asked for.

I do not use sendkeys and am unable to assist in that
Click to expand...
regard.
If
you
are
insistent on using sendkeys, I'm afraid someone else will
Click to expand...
have
to
jump
in on
this thread to help you.

If you want to attempt what I have suggested:

Create a blank database that only contains a form with a few buttons
on
it
that allow users to click the relevant button to open one of several
different databases.

Copy the code you found when you followed the link into a new
module.
Then
Click to expand...
Click to expand...
Click to expand...
 
C

Chrys

A Database to Start other Databases said:
I'd like to be able to make a database that will then start other databases.
We have about 12 databases in our office and everyone forgets which database
does what. I'd like buttons to start the database and then in each db,
they'll press a "return" button to get back to the menu program.
Click to expand...
 
U

UpRider

Chrys, the below subroutine works for me. You can modify it for your
needs....
strFileToRun is the complete path and filespec of the .mdb to run..

HTH, UpRider

Sub subRunTarget()
Dim hTask As Variant
Dim AppPath As String
Dim strFileToRun As String
strFileToRun = Forms!frmMain!txtFile
AppPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
hTask = Shell(AppPath & " """ & strFileToRun & """", 1)
End Sub
 
Top