Using a function to print

[JIM]

I'm trying to make a function work found on-line. My module looks like this:
Option Compare Database

Public Const SW_Hide = 0
Public Const SW_Minimize = 6
Public Const SW_Restore = 9
Public Const SW_Show = 5
Public Const SW_ShowMazimized = 3
Public Const SW_ShowMinimized = 2
Public Const SW_ShowMinnoActive = 7
Public Const SW_Showna = 8
Public Const SW_ShownoActivate = 4
Public Const SW_ShowNormal = 1

Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA"
(ByVal hWnd As Long, ByVal IpOperation As String, ByVal IpParameters As
String, ByVal IpDirectory As String, ByVal nShowCmd As Long) As Long

Public Sub ExecuteFile(sFileName As String, sAction As String) 'sAction can
be either "Open" or "Print"
Dim vReturn As Long

If ShellExecute(Access.hWndAccessApp, sAction, sFileName, vbNullString,
SW_ShowNormal) < 33 Then
MsgBox "File not found"
End If
End Sub

Then in my click event on a form the code looks like this:

Private Sub Option62_Click()
sFileName = Me.txtMapLoc
vReturn = ExecuteFile(sFileName, "Print")
End Sub

I get "Compile error: Byref argument type mismatch" And after thinking -
it's probably saying this is not a file name, which it is not. The control
txtMapLoc has in it the location of the file (ie Flans\Xcel.doc) not the
file name. How can I implement this function using the location?
Thanks,
JIM

 

[Douglas J. Steele]

Try declaring sFileName:

Private Sub Option62_Click()
Dim sFileName As String
Dim vReturn As Variant

sFileName = Me.txtMapLoc
vReturn = ExecuteFile(sFileName, "Print")

End Sub

The fact that your code raised that error leads me to believe that you
haven't told Access to require declaration of all variables. You should. To
do this, go into the VB Editor and select Tools | Options from the menu bar.
Look at the Editor tab: one of the options there should be "Require Variable
Declaration". What this means is that all future modules you create will
have Option Explicit as the first or second line of the file. You should go
back to all of your existing modules and add that line. While it may seem
like a lot of work declaring all your variables, in the long run it'll save
you huge amounts of time as you try to figure out why your code isn't doing
what it should when you've made a typo on a variable name!

 

[JIM]

Thanks so much, Doug. I checked Require Variable Declar. and added option
explicit to module. Also reiterated the variables in subroutine. Now it
says Compile error Expected Function or variable and highlights ExecuteFile.
In help it says:
The syntax of your statement indicates a variable or function call. This
error has the following cause and solution:
The name isn't that of a known variable or Function procedure.
Check the spelling of the name. Make sure that any variable or function with
that name is visible in the portion of the program from which you are
referencing it. For example, if a function is defined as Private or a
variable isn't defined as Public, it's only visible within its own module.
(or)
You are trying to inappropriately assign a value to a procedure name. For
example if MySub is a Sub procedure, the following code generates this error:
MySub = 237 ' Causes Expected Function or variable error

Although you can use assignment syntax with a Property Let procedure or with
a Function that returns an object or a Variant containing an object, you
can't use assignment syntax with a Sub, Property Get, or Property Set
procedure.

I've checked everything in help message. What am I missing?
Thanks, JIM

 

[Douglas J. Steele]

When you saved the code, you didn't happen to name the module ExecuteFile
did you? If so, rename the module. Modules cannot have the same name as subs
or functions.

 

[JIM]

The name of my module is Print File which has two parts:
Declarations and ExecuteFile-
Option Compare Database
Option Explicit

Public Const SW_Hide = 0
Public Const SW_Minimize = 6
Public Const SW_Restore = 9
Public Const SW_Show = 5
Public Const SW_ShowMazimized = 3
Public Const SW_ShowMinimized = 2
Public Const SW_ShowMinnoActive = 7
Public Const SW_Showna = 8
Public Const SW_ShownoActivate = 4
Public Const SW_ShowNormal = 1

Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA"
(ByVal hWnd As Long, ByVal IpOperation As String, ByVal IpParameters As
String, ByVal IpDirectory As String, ByVal nShowCmd As Long) As Long

Public Sub ExecuteFile(sFileName As String, sAction As String) 'sAction can
be either "Open" or "Print"
Dim vReturn As Long

If ShellExecute(Access.hWndAccessApp, sAction, sFileName, vbNullString,
SW_ShowNormal) < 33 Then
MsgBox "File not found"
End If
End Sub

Then in my form I call the function on a click event:

Private Sub Option62_Click()
sFileName = Me.txtMapLoc
vReturn = ExecuteFile(sFileName, "Print")
End Sub

It doesn't like ExecuteFile.
Thanks, JIM

 

[Douglas J. Steele]

You've declared ExecuteFile as a sub, but you're trying to use it as a
function.

Try:

Private Sub Option62_Click()
sFileName = Me.txtMapLoc
Call ExecuteFile(sFileName, "Print")
End Sub

 

[JIM]

Hi again, I closed my database and reopened and recompiled and am now back at
first problem - it says there is a compile error: Byref argument type
mismatch. It doesn't like sFileName.
Thanks, JIM

 

[Douglas J. Steele]

And sFileName is declared as a String both in Option62_Click and in
ExecuteFile?

You could try:

ExecuteFile(CStr(sFileName), "Print")

 

[JIM]

Thanks Doug for all your help. My subroutine now looks like this:

Private Sub Option62_Click()
sFileName = Me.txtMapLoc
Call ExecuteFile(CStr(sFileName), "Print")
End Sub

It compiles without problems. When I click on command button a MS message
comes up:
MSAccess for Windows has encountered a problem and needs to close. We are
sorry...... And then Access closes.
I'm using A2000. Do I need to update or is there another problem?
Thanks, JIM
I will leave town until 5/27

 

[Douglas J. Steele]

Either create a new database and import everything from your current
database into it, or decompile your current database (see
http://www.mvps.org/access/bugs/bugs0008.htm for details on this option)

 

[JIM]

Thanks Doug for all your help so far. We are making progress. I tried both
your suggestions - ran the msaccess.exe /decompile with no difference access
reacted the same way and closed down. Then I tried creating a temp database
and exported all objects into new database and recompiled and still reacted
same way, same message. Any suggestion would be appreciated, JIM

 

[Douglas J. Steele]

See whether you can find anything in the Corrupt Microsoft Access MDBs FAQ
that Tony Toews has at http://www.granite.ab.ca/access/corruptmdbs.htm

Make sure you're working with a copy of the file, just in case it makes it
worse!

Good luck.

 

[JIM]

Hi Doug, the link you referred to suggests using SYSREL90 so I downloaded and
tried to run but can't get the syntax right. I'm using as a command:
C:\sysrel90.exe /srcdb <C:\ BTData.mdb> /destdb <[C:\ TempBT.mdb]>
Basically this transfers the relationships from one databae to another. Do
you know if this is correct or should I repost?
Thanks, JIM

 

[Douglas J. Steele]

I'd suggest starting a new thread, as I've never used sysrel90

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug, the link you referred to suggests using SYSREL90 so I downloaded
and
tried to run but can't get the syntax right. I'm using as a command:
C:\sysrel90.exe /srcdb <C:\ BTData.mdb> /destdb <[C:\ TempBT.mdb]>
Basically this transfers the relationships from one databae to another.
Do
you know if this is correct or should I repost?
Thanks, JIM

See whether you can find anything in the Corrupt Microsoft Access MDBs
FAQ
that Tony Toews has at http://www.granite.ab.ca/access/corruptmdbs.htm

Make sure you're working with a copy of the file, just in case it makes
it
worse!

Good luck.