Open External Database Form

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.

Re: Open External Database Form

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" wrote in message
news:24pQi.74$7a2.44@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.

Re: Open External Database Form

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" wrote in message
> news:24pQi.74$7a2.44@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.
>
>

Re: Open External Database Form

Ian wrote in
news:3IvQi.11785$0z6.2664@newsfe7-win.ntli.net:

> 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

I tried to post this in Google but I think the post failed. I apologize
if this appears twice.

1. You could have the mdb application open the report automatically via
an autoexec macro or | You could follow a hyperlink to the report in the
mdb application (see help-follow hyperlink). Assuming the report needs
information you could set the value of the text box to a public function
which reads the value from wherever, perhaps a text file written by your
mde application or by command line parameters included in your call to
open the mdb file.
2. You could scrap the label wizard and design your own label report
using the default paper size in you printer. (I think the label wizard
sucks big time).
3. You could design a MS-Word label document that used your JET tables to
fill its fields.
4. You could set a reference to your mdb file from your mde and open the
report (or form that opens the report) from a standard module in the
referenced mdb.
5. You could look up the programming language for your printer and write
your own documents with printer codes and send it off to the printer.
6. If you're using Access 2003 you could put the report into an adp, open
an ADODB recordset and set the report's recordset to that.
7. If you're using Access 2003 you could put the report into an adp, and
completely program the report to show what you want dynamically.

Yes, I've done all of these except #4 at one time or another, but the
solutions are peculiar to each individual situation.

Of course, you could do what I would do: forget the MDE thing and just
deliver an MDB. Yes, I know people might mess with my code, even borrow
it and call it their own. So what? I'll write something better tomorrow,
or maybe tonight, if I can't sleep.

--
lyle fairfield

Re: Open External Database Form

lyle fairfield wrote:
> Ian wrote in
> news:3IvQi.11785$0z6.2664@newsfe7-win.ntli.net:
>
>> 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
>
> I tried to post this in Google but I think the post failed. I apologize
> if this appears twice.
>
> 1. You could have the mdb application open the report automatically via
> an autoexec macro or | You could follow a hyperlink to the report in the
> mdb application (see help-follow hyperlink). Assuming the report needs
> information you could set the value of the text box to a public function
> which reads the value from wherever, perhaps a text file written by your
> mde application or by command line parameters included in your call to
> open the mdb file.
> 2. You could scrap the label wizard and design your own label report
> using the default paper size in you printer. (I think the label wizard
> sucks big time).
> 3. You could design a MS-Word label document that used your JET tables to
> fill its fields.
> 4. You could set a reference to your mdb file from your mde and open the
> report (or form that opens the report) from a standard module in the
> referenced mdb.
> 5. You could look up the programming language for your printer and write
> your own documents with printer codes and send it off to the printer.
> 6. If you're using Access 2003 you could put the report into an adp, open
> an ADODB recordset and set the report's recordset to that.
> 7. If you're using Access 2003 you could put the report into an adp, and
> completely program the report to show what you want dynamically.
>
> Yes, I've done all of these except #4 at one time or another, but the
> solutions are peculiar to each individual situation.
>
> Of course, you could do what I would do: forget the MDE thing and just
> deliver an MDB. Yes, I know people might mess with my code, even borrow
> it and call it their own. So what? I'll write something better tomorrow,
> or maybe tonight, if I can't sleep.
>

Lyle

Thanks for all your help, I cant imagine a more comprehensive answer.

I shall have to give it some more thought, wouldn’t it be nice if MS
would just let the MDE remember the page sizes?

I think #6 and #7 are out because I know the users have to use Access 97
and Access 2000.

#1 sounds good, I have used text files to read data back into other DB’s
in the past, all sorts of problems when 2 users make the same file at
the same time etc.

#2 will be problematic if the user changes his printer and moves away
from Dymo.

#3 I considered using word templates and filling in the fields using
word bookmarks, this has worked in the past for different purposes, I
guess a new word template would have to be created if the user changed
the printer.

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.

Thanks again for all your help.

Re: Open External Database Form

On Oct 15, 7:02 am, Ian wrote:
> lyle fairfield wrote:
> > Ian wrote in
> >news:3IvQi.11785$0z6.2664@newsfe7-win.ntli.net:
>
> >> 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
>
> > I tried to post this in Google but I think the post failed. I apologize
> > if this appears twice.
>
> > 1. You could have the mdb application open the report automatically via
> > an autoexec macro or | You could follow a hyperlink to the report in the
> > mdb application (see help-follow hyperlink). Assuming the report needs
> > information you could set the value of the text box to a public function
> > which reads the value from wherever, perhaps a text file written by your
> > mde application or by command line parameters included in your call to
> > open the mdb file.
> > 2. You could scrap the label wizard and design your own label report
> > using the default paper size in you printer. (I think the label wizard
> > sucks big time).
> > 3. You could design a MS-Word label document that used your JET tables to
> > fill its fields.
> > 4. You could set a reference to your mdb file from your mde and open the
> > report (or form that opens the report) from a standard module in the
> > referenced mdb.
> > 5. You could look up the programming language for your printer and write
> > your own documents with printer codes and send it off to the printer.
> > 6. If you're using Access 2003 you could put the report into an adp, open
> > an ADODB recordset and set the report's recordset to that.
> > 7. If you're using Access 2003 you could put the report into an adp, and
> > completely program the report to show what you want dynamically.
>
> > Yes, I've done all of these except #4 at one time or another, but the
> > solutions are peculiar to each individual situation.
>
> > Of course, you could do what I would do: forget the MDE thing and just
> > deliver an MDB. Yes, I know people might mess with my code, even borrow
> > it and call it their own. So what? I'll write something better tomorrow,
> > or maybe tonight, if I can't sleep.
>
> Lyle
>
> Thanks for all your help, I cant imagine a more comprehensive answer.
>
> I shall have to give it some more thought, wouldn't it be nice if MS
> would just let the MDE remember the page sizes?
>
> I think #6 and #7 are out because I know the users have to use Access 97
> and Access 2000.
>
> #1 sounds good, I have used text files to read data back into other DB's
> in the past, all sorts of problems when 2 users make the same file at
> the same time etc.
>
> #2 will be problematic if the user changes his printer and moves away
> from Dymo.
>
> #3 I considered using word templates and filling in the fields using
> word bookmarks, this has worked in the past for different purposes, I
> guess a new word template would have to be created if the user changed
> the printer.
>
> 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.
>
> Thanks again for all your help.

I have done #4 but not by opening the report with a command in a
standard module in the referenced mdb, which I believe is the usual
practice. I have tried to expose the report, that is to make it behave
just like a report in the currentdb.

To do so in the referenced mdb I have made sure the report had a code
module, even if the code did nothing, saved the report as text with
Application.SaveAsText (before referencing the mdb).
Then I have changed the line
Attribute VB_Exposed = False
to
Attribute VB_Exposed = True
in the text file.
Then I have reloaded an image of the report with
Application.LoadFromText but using a different name for the report so
as not to munget the original.

In Access 97 this used to make the report (the new one with the
modified name) just like a report in the current db, and fully usable.
Does it still do that in later versions? I'm not sure. Perhaps it's
worth a shot.

(The Attribute Lines seem to vary from VB version to VB version; I am
using Access 2002 right now.)

With an exposed object, I have found that it's worthwhile to check
where its data is coming from: the currentdb or the codedb.

Re: Open External Database Form

Wow! Interesting thread. Thanks.

Larry Linson
Microsoft Access MVP

Re: Open External Database Form

> 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

Re: Open External Database Form

lyle wrote:
> On Oct 15, 7:02 am, Ian wrote:
>> lyle fairfield wrote:
>>> Ian wrote in
>>> news:3IvQi.11785$0z6.2664@newsfe7-win.ntli.net:
>>>> 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
>>> I tried to post this in Google but I think the post failed. I apologize
>>> if this appears twice.
>>> 1. You could have the mdb application open the report automatically via
>>> an autoexec macro or | You could follow a hyperlink to the report in the
>>> mdb application (see help-follow hyperlink). Assuming the report needs
>>> information you could set the value of the text box to a public function
>>> which reads the value from wherever, perhaps a text file written by your
>>> mde application or by command line parameters included in your call to
>>> open the mdb file.
>>> 2. You could scrap the label wizard and design your own label report
>>> using the default paper size in you printer. (I think the label wizard
>>> sucks big time).
>>> 3. You could design a MS-Word label document that used your JET tables to
>>> fill its fields.
>>> 4. You could set a reference to your mdb file from your mde and open the
>>> report (or form that opens the report) from a standard module in the
>>> referenced mdb.
>>> 5. You could look up the programming language for your printer and write
>>> your own documents with printer codes and send it off to the printer.
>>> 6. If you're using Access 2003 you could put the report into an adp, open
>>> an ADODB recordset and set the report's recordset to that.
>>> 7. If you're using Access 2003 you could put the report into an adp, and
>>> completely program the report to show what you want dynamically.
>>> Yes, I've done all of these except #4 at one time or another, but the
>>> solutions are peculiar to each individual situation.
>>> Of course, you could do what I would do: forget the MDE thing and just
>>> deliver an MDB. Yes, I know people might mess with my code, even borrow
>>> it and call it their own. So what? I'll write something better tomorrow,
>>> or maybe tonight, if I can't sleep.
>> Lyle
>>
>> Thanks for all your help, I cant imagine a more comprehensive answer.
>>
>> I shall have to give it some more thought, wouldn't it be nice if MS
>> would just let the MDE remember the page sizes?
>>
>> I think #6 and #7 are out because I know the users have to use Access 97
>> and Access 2000.
>>
>> #1 sounds good, I have used text files to read data back into other DB's
>> in the past, all sorts of problems when 2 users make the same file at
>> the same time etc.
>>
>> #2 will be problematic if the user changes his printer and moves away
>> from Dymo.
>>
>> #3 I considered using word templates and filling in the fields using
>> word bookmarks, this has worked in the past for different purposes, I
>> guess a new word template would have to be created if the user changed
>> the printer.
>>
>> 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.
>>
>> Thanks again for all your help.
>
> I have done #4 but not by opening the report with a command in a
> standard module in the referenced mdb, which I believe is the usual
> practice. I have tried to expose the report, that is to make it behave
> just like a report in the currentdb.
>
> To do so in the referenced mdb I have made sure the report had a code
> module, even if the code did nothing, saved the report as text with
> Application.SaveAsText (before referencing the mdb).
> Then I have changed the line
> Attribute VB_Exposed = False
> to
> Attribute VB_Exposed = True
> in the text file.
> Then I have reloaded an image of the report with
> Application.LoadFromText but using a different name for the report so
> as not to munget the original.
>
> In Access 97 this used to make the report (the new one with the
> modified name) just like a report in the current db, and fully usable.
> Does it still do that in later versions? I'm not sure. Perhaps it's
> worth a shot.
>
> (The Attribute Lines seem to vary from VB version to VB version; I am
> using Access 2002 right now.)
>
> With an exposed object, I have found that it's worthwhile to check
> where its data is coming from: the currentdb or the codedb.
>

Lyle

I have used the Application.SaveAsText, my text file does not contain
the words Attribute, Exposed or False using Access 97?

Re: Open External Database Form

On Oct 15, 5:39 pm, Ian wrote:
> lyle wrote:
> > On Oct 15, 7:02 am, Ian wrote:
> >> lyle fairfield wrote:
> >>> Ian wrote in
> >>>news:3IvQi.11785$0z6.2664@newsfe7-win.ntli.net:
> >>>> 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
> >>> I tried to post this in Google but I think the post failed. I apologize
> >>> if this appears twice.
> >>> 1. You could have the mdb application open the report automatically via
> >>> an autoexec macro or | You could follow a hyperlink to the report in the
> >>> mdb application (see help-follow hyperlink). Assuming the report needs
> >>> information you could set the value of the text box to a public function
> >>> which reads the value from wherever, perhaps a text file written by your
> >>> mde application or by command line parameters included in your call to
> >>> open the mdb file.
> >>> 2. You could scrap the label wizard and design your own label report
> >>> using the default paper size in you printer. (I think the label wizard
> >>> sucks big time).
> >>> 3. You could design a MS-Word label document that used your JET tables to
> >>> fill its fields.
> >>> 4. You could set a reference to your mdb file from your mde and open the
> >>> report (or form that opens the report) from a standard module in the
> >>> referenced mdb.
> >>> 5. You could look up the programming language for your printer and write
> >>> your own documents with printer codes and send it off to the printer.
> >>> 6. If you're using Access 2003 you could put the report into an adp, open
> >>> an ADODB recordset and set the report's recordset to that.
> >>> 7. If you're using Access 2003 you could put the report into an adp, and
> >>> completely program the report to show what you want dynamically.
> >>> Yes, I've done all of these except #4 at one time or another, but the
> >>> solutions are peculiar to each individual situation.
> >>> Of course, you could do what I would do: forget the MDE thing and just
> >>> deliver an MDB. Yes, I know people might mess with my code, even borrow
> >>> it and call it their own. So what? I'll write something better tomorrow,
> >>> or maybe tonight, if I can't sleep.
> >> Lyle
>
> >> Thanks for all your help, I cant imagine a more comprehensive answer.
>
> >> I shall have to give it some more thought, wouldn't it be nice if MS
> >> would just let the MDE remember the page sizes?
>
> >> I think #6 and #7 are out because I know the users have to use Access 97
> >> and Access 2000.
>
> >> #1 sounds good, I have used text files to read data back into other DB's
> >> in the past, all sorts of problems when 2 users make the same file at
> >> the same time etc.
>
> >> #2 will be problematic if the user changes his printer and moves away
> >> from Dymo.
>
> >> #3 I considered using word templates and filling in the fields using
> >> word bookmarks, this has worked in the past for different purposes, I
> >> guess a new word template would have to be created if the user changed
> >> the printer.
>
> >> 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.
>
> >> Thanks again for all your help.
>
> > I have done #4 but not by opening the report with a command in a
> > standard module in the referenced mdb, which I believe is the usual
> > practice. I have tried to expose the report, that is to make it behave
> > just like a report in the currentdb.
>
> > To do so in the referenced mdb I have made sure the report had a code
> > module, even if the code did nothing, saved the report as text with
> > Application.SaveAsText (before referencing the mdb).
> > Then I have changed the line
> > Attribute VB_Exposed = False
> > to
> > Attribute VB_Exposed = True
> > in the text file.
> > Then I have reloaded an image of the report with
> > Application.LoadFromText but using a different name for the report so
> > as not to munget the original.
>
> > In Access 97 this used to make the report (the new one with the
> > modified name) just like a report in the current db, and fully usable.
> > Does it still do that in later versions? I'm not sure. Perhaps it's
> > worth a shot.
>
> > (The Attribute Lines seem to vary from VB version to VB version; I am
> > using Access 2002 right now.)
>
> > With an exposed object, I have found that it's worthwhile to check
> > where its data is coming from: the currentdb or the codedb.
>
> Lyle
>
> I have used the Application.SaveAsText, my text file does not contain
> the words Attribute, Exposed or False using Access 97?

I no longer have a working copy of Access 97.
This old code is dated April 1999 in my archives, so I'm guessing it
is for Access 97. I suppose it won't hurt to try? Please, note that
"CodeBehindForm" is not a typo.
In any case, I feel we are getting way stretched into the exotic here,
probably into areas which have no wothwhile use, other than to amuse
us over morning coffee.

Const strExposed As String = "Attribute VB_Exposed = True" & vbCrLf
Const strStartModule = "CodeBehindForm"

Sub ExposeRpt()
Call ExposeReport("rptTransactions")
End Sub
Sub ExposeReport(strReportName)
Dim strFromPath As String, StrToPath As String, intFileNumber As
Integer, _
lngLen As Long, lngLeft As Long, _
strBuffer As String, strLeft As String, strRight As String

strFromPath = Environ("Temp") & "\" & strReportName &
"_Original.Txt"
StrToPath = Environ("Temp") & "\" & strReportName & "_Revised.Txt"

On Error Resume Next
Echo False
DoCmd.OpenReport strReportName, acViewDesign
Reports(strReportName).HasModule = True
DoCmd.Save acReport, strReportName
DoCmd.Close acReport, strReportName
Echo True
On Error GoTo 0

Application.SaveAsText acReport, strReportName, strFromPath

lngLen = FileLen(strFromPath)
intFileNumber = FreeFile
Open strFromPath For Input As #intFileNumber
strBuffer = Input(lngLen, #intFileNumber)
lngLeft = InStr(strBuffer, strStartModule) + Len(strStartModule)
strLeft = Left(strBuffer, lngLeft) & vbCrLf
strRight = Mid(strBuffer, lngLeft + 2)
strBuffer = strLeft & strExposed & strRight
Close #intFileNumber

intFileNumber = FreeFile
Open StrToPath For Output As #intFileNumber
Print #intFileNumber, strBuffer
Close #intFileNumber

Application.LoadFromText acReport, strReportName, StrToPath

End Sub

Re: Open External Database Form

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 /cmd

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