Using a function to print

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JIM - 20 May 2008 17:14 GMT

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 F:Plans\Xcel.doc) not the
file name. How can I implement this function using the location?
Thanks,
JIM

Reply to this Message

Douglas J. Steele - 20 May 2008 18:34 GMT

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!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I'm trying to make a function work found on-line. My module looks like
> this:
[quoted text clipped - 41 lines]
> Thanks,
> JIM

Reply to this Message

JIM - 21 May 2008 15:55 GMT

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

> Try declaring sFileName:
>
[quoted text clipped - 63 lines]
> > Thanks,
> > JIM

Reply to this Message

Douglas J. Steele - 21 May 2008 16:05 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks so much, Doug. I checked Require Variable Declar. and added option
> explicit to module. Also reiterated the variables in subroutine. Now it
[quoted text clipped - 101 lines]
>> > Thanks,
>> > JIM

Reply to this Message

JIM - 21 May 2008 17:25 GMT

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

> 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
[quoted text clipped - 105 lines]
> >> > Thanks,
> >> > JIM

Reply to this Message

Douglas J. Steele - 21 May 2008 17:44 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The name of my module is Print File which has two parts:
> Declarations and ExecuteFile-
[quoted text clipped - 37 lines]
> It doesn't like ExecuteFile.
> Thanks, JIM

Reply to this Message

JIM - 21 May 2008 17:50 GMT

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

Reply to this Message

Douglas J. Steele - 21 May 2008 18:31 GMT

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

You could try:

ExecuteFile(CStr(sFileName), "Print")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi again, I closed my database and reopened and recompiled and am now back
> at
[quoted text clipped - 126 lines]
>> >> > Thanks,
>> >> > JIM

Reply to this Message

JIM - 22 May 2008 18:15 GMT

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

> And sFileName is declared as a String both in Option62_Click and in
> ExecuteFile?
[quoted text clipped - 133 lines]
> >> >> > Thanks,
> >> >> > JIM

Reply to this Message

Douglas J. Steele - 22 May 2008 18:22 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Doug for all your help. My subroutine now looks like this:
>
[quoted text clipped - 168 lines]
>> >> >> > Thanks,
>> >> >> > JIM

Reply to this Message

JIM - 27 May 2008 16:37 GMT

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

> Either create a new database and import everything from your current
> database into it, or decompile your current database (see
[quoted text clipped - 172 lines]
> >> >> >> > Thanks,
> >> >> >> > JIM

Reply to this Message

Douglas J. Steele - 27 May 2008 19:27 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Doug for all your help so far. We are making progress. I tried
> both
[quoted text clipped - 200 lines]
>> >> >> >> > Thanks,
>> >> >> >> > JIM

Reply to this Message

JIM - 28 May 2008 17:24 GMT

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
[quoted text clipped - 208 lines]
> >> >> >> >> > Thanks,
> >> >> >> >> > JIM

Reply to this Message

Douglas J. Steele - 28 May 2008 21:54 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Doug, the link you referred to suggests using SYSREL90 so I downloaded
> and
[quoted text clipped - 240 lines]
>> >> >> >> >> > Thanks,
>> >> >> >> >> > JIM

Reply to this Message