ACCESS FEED: RE: [MS_AccessPros] Error after moving Sub to Public Module

Senin, 22 Juli 2013

RE: [MS_AccessPros] Error after moving Sub to Public Module - help

Rod-

Ah, there are two ways to call a sub:

Call MySub(argument1, argument2)

and

MySub argument1, argument2

Either add the Call statement or remove the parens.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of rodbevill
Sent: Sunday, July 21, 2013 11:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error after moving Sub to Public Module - help

Hi John,
Thank for the quick reply. I have changed all calls to include two
parameters, the first is a string and the second is an integer. Here is a
segment of code:
from calling routine:
===================================
Dim errMsg As String
Dim errRsp As Integer
Dim errCnt As Integer
Dim fName As String

On Error GoTo ErrTrap
errCnt = 0
CurrentDb.TableDefs.Delete "New_License"
ProcessFile:
' Change fieldnames in the Excel file
fName = Me.Txt_AZDOR_File.Value
Process_XL_Macro1(fName, Rename_Fieldnames)
' Link the Excel file
=======================================
Public subroutine located in my Standard Module
=======================================

Public Sub Process_XL_Macro1(WkBkName As String, ProcType As Integer)
Dim xlApp As Object

' Start Excel
Set xlApp = CreateObject("Excel.Application")
' Open the add-in file .
xlApp.Workbooks.Open (xlApp.librarypath & "\MyUtility.XLAM")
=========================================
When I add the following line to the calling routine, I get the "Expecting
=" message when I leave the line to continue programming

Process_XL_Macro1(fName, Rename_Fieldnames)

Not use why.

fName = Me.Txt_AZDOR_File.Value

assigns the value of a text box which contain the filename including path.
When I revert back to the original routines without passing the fName
variable everything appears to function properly. The original subroutine
has been tested both locally and in the Standard Module.

Rod

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Rod-
>
> If you change the procedure parameter list, then you have to change
> all the calls to the procedure. Looks like you need to add a string
> variable before the ProcType.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications SQL Queries for Mere Mortals
> http://www.viescas.com/ (Paris, France)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> rodbevill
> Sent: Sunday, July 21, 2013 10:15 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Error after moving Sub to Public Module -
> help
>
> Good Afternoon Everyone,
> I am moving a Subroutine from a form's VBA code to a Public Module. I
> made a simple change by adding an extra parameter to make it more
> universal. The original sub definition and call were:
>
> Private Sub Process_XL_Macro(ProcType As Integer) with call
> Process_XL_Macro(Rename_FieldNames)
>
> Rename_FieldNames is defined as a constant
>
> After moving to the Public Module the definition is:
>
> Public Sub Process_XL_Macro(Fname as string, ProcType As Integer) will
> call Process_XL_Macro (fName, Rename_FieldNames)
>
> fName is the Path\Filename string
>
> When adding the call to code, I get "Expecting =" error. Any suggestions
of
> the cause. Remove the Fname parameter appears to work fine. Using
Access
> 2010 on Windows 7.
>
> Rod
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links