Open External Database Form

[Ian]

I am trying to:
1. Open a form on the external database
2. Enter a value in a text box on that form

I have 1 above working OK using module form “The Access Web”, the module
looks like this:
************ Code Start *************
' 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 *************
Now I want to enter a value in a text box on the form I just opened, on
that form I have a text box called GetExternalID, How can I enter a
value into that external database/ Form/ Text box?

I would expect to use something like:
ExternalDB!FormsName![GetExternalID]=”TestValue” but I cant get this to
work.

I tried adding within the With statement:
..DoCmd.GoToControl "GetExternalID"
This moves the focus to the Text Box but I still cant enter a value into
that text box.

Any help would be appreciated.

[Larry Linson]

What are you needing to accomplish by doing this? Perhaps there is an
easier way... manipulating a form and controls in another database is a
rather complex approach that may not be needful.

Larry Linson
Microsoft Access MVP

"Ian" <ia********@ntlworld.comwrote in message
news:24*************@newsfe3-gui.ntli.net...

>I am trying to:
1. Open a form on the external database
2. Enter a value in a text box on that form

I have 1 above working OK using module form “The Access Web”, the module
looks like this:
************ Code Start *************
' 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 *************
Now I want to enter a value in a text box on the form I just opened, on
that form I have a text box called GetExternalID, How can I enter a value
into that external database/ Form/ Text box?

I would expect to use something like:
ExternalDB!FormsName![GetExternalID]=”TestValue” but I cant get this to
work.

I tried adding within the With statement:
.DoCmd.GoToControl "GetExternalID"
This moves the focus to the Text Box but I still cant enter a value into
that text box.

Any help would be appreciated.

[Ian]

Larry Linson wrote:

What are you needing to accomplish by doing this? Perhaps there is an
easier way... manipulating a form and controls in another database is a
rather complex approach that may not be needful.

Larry Linson
Microsoft Access MVP

Thanks Larry

Why am I doing this, well here goes. I am still trying to solve my
problem, see item on 11-10-07, subject: Default use Specific Printer and
Label Size.

Basically I cant my Dymo 400 label printer to remember my label size
on a report, each time I close the report it forgets the size. If I keep
the database as an MDB it remembers the size, when I convert to an MDE
it forgets it. After much research it seems there is no realistic way to
get Access 97 and 2000 to remember page sizes using an MDE.

My attempted (and perhaps daft) solution is this to store this one
report in a separate database that can stay as an MDB. I have managed to
get it to open the external DB and set the focus to a text box, now all
I have to do is place the linking ID into the text box and, as far as I
can see it should work.

Perhaps my solution is a bit crazy but I cant think of another way of
doing it.

Regards

>
"Ian" <ia********@ntlworld.comwrote in message
news:24*************@newsfe3-gui.ntli.net...

>I am trying to:
1. Open a form on the external database
2. Enter a value in a text box on that form

I have 1 above working OK using module form “The Access Web”, the module
looks like this:
************ Code Start *************
' 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 *************
Now I want to enter a value in a text box on the form I just opened, on
that form I have a text box called GetExternalID, How can I enter a value
into that external database/ Form/ Text box?

I would expect to use something like:
ExternalDB!FormsName![GetExternalID]=”TestValue” but I cant get this to
work.

I tried adding within the With statement:
.DoCmd.GoToControl "GetExternalID"
This moves the focus to the Text Box but I still cant enter a value into
that text box.

Any help would be appreciated.

[Larry Linson]

Wow! Interesting thread. Thanks.

Larry Linson
Microsoft Access MVP

[Sky]

4. You could set a reference to your mdb file from your mde

Unfortunately, you cannot set a reference from an mde to an mdb, as far as I
know. You can set a reference from an mdb to an mde.

So you might want to consider the other interesting options.

- Steve

[Marshall Barton]

Ian wrote:

>I think I shall have a go at #4 which ironically is the one you say you
have never used. As the forms/report would be stored in the backend, I
suppose I could just make a temporary table from the front end to store
the ID that I need for the report, then read it in from the temp table,
it might work. I wanted it to use a separate DB for the report, each PC
would have this DB in the same folder as the front end, this would allow
each PC to use a different make of printer without a problem, if I put
it on the back end there can only be one make of printer.

Off the wall thought.

Instead of using a reference, you can shell to your report
mdb using /X <macro/cmd <recordid>

Where the macro uses RunCode to execute the function that
opens the report. Your function can use the Command
function to retrieve the record id value:

DoCmd.OpenReport "the report", , , "keyfield=" & Command()

--
Marsh