473,385 Members | 1,396 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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.
Oct 14 '07 #1
5 9440
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.

Oct 14 '07 #2
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.

Oct 14 '07 #3
Wow! Interesting thread. Thanks.

Larry Linson
Microsoft Access MVP
Oct 15 '07 #4
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
Oct 15 '07 #5
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
Oct 16 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Roger Withnell | last post by:
I open a new window from the current window to display maps. Several maps of different sizes can be displayed. The function is given the size of the map and adjusts the window size accordingly....
5
by: Mike Cooper | last post by:
I have downloaded this from Allen Brownes access web page. Created a txt file from it then loaded it as a macro in my db. I cannot get it to run nor can I figure where to insert the name of the db...
115
by: TheAd | last post by:
At this moment I use MsAccess and i can build about every databound application i want. Who knows about a serious open source alternative? Because Windows will be a client platform for some time, i...
6
by: ats | last post by:
I have a table that contains the database names of external databases. Each one of these external databases contain a table (which has the same structure) that I would like to query together as one...
1
by: exg001 | last post by:
Hey all! Here's what I want to do: I have database A.mdb and database B.mdb. They are both using SQL linked tables, and I am utilizing a username/password login reference the SQL tables. ...
12
by: Steve | last post by:
I have a database that raises an error when you try to open it and it doesn't open. Trying to open the database with the OpenDatabase method raises the same error. Trying to import any objects...
3
by: siyaverma | last post by:
i am trying to upload csv file from user's computer to main server the code i am using is if(((isset($_GET)) && ($_GET=="yes")) ) { $typefield = $_GET; echo...
1
by: mfaisalwarraich | last post by:
Hi Everybody, I have an external database called Patients.mdb where i made a query called qryAdmissionDetails. now i want to run this query on a report of another database called...
14
by: SimeonD | last post by:
Hi I have an access database called Sales.Mdb In vb.net 2005, I'd like to open it. Which I can do. What I can't figure out is how to figure out if Sales.Mdb is open already. If so, I want to open...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.