UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Argument For Function In Access, Access 2010    
 
   
bakersburg9
post Mar 1 2018, 07:25 PM
Post#1



Posts: 5,209
Joined: 2-November 04
From: Downey, CA


I want to export the results of my query to Excel - I have a saved export that is attached to a button on my main form - the process would work fine, but before I get a chance to click the button, the form "goes away" (is covered up) - I have to close my last action, which is this macro involving several steps - I wrote a function which calls my saved export, and appended it to my macro, using the "RUN CODE" option - but I get a nasty error message - I tried again, using intellisense, and I got prompted with an open paren: exportResultsToExcel( what do I put in parenthesis ? If I leave it blank, just (), then access doesn't like that - says unrecognizable function, blah, blah, blah...
Go to the top of the page
 
zaxbat
post Mar 1 2018, 08:03 PM
Post#2



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Might adapt your query into a make table query.....so what you see in the query is saved into a table. Then at your leisure you can send the table to excel. Probably want to delete the table after you send it to excell.



SELECT field1, field2, field3, etc INTO mytemptablename FROM .....etc...etc...etc the remainder of your query here.....;


You simply insert the words "INTO mytemptablename" between your last selected field and the FROM directive and SQL will create a table and throw the query results into it...slick and simple. Put any table name you like...i just used mytemptablename as an exampe. But, this will overwrite any table with the same name so be careful not to use the name of any valuable tables in your DB.
This post has been edited by zaxbat: Mar 1 2018, 08:05 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
tina t
post Mar 1 2018, 09:23 PM
Post#3



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
I wrote a function which calls my saved export, and appended it to my macro, using the "RUN CODE" option - but I get a nasty error message - I tried again, using intellisense, and I got prompted with an open paren: exportResultsToExcel( what do I put in parenthesis ? If I leave it blank, just (), then access doesn't like that - says unrecognizable function, blah, blah, blah...

the function needs to be stored in a standard module, and declared as a Public Function. and does the function have any arguments?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Mar 2 2018, 04:45 AM
Post#4


UtterAccess VIP
Posts: 10,061
Joined: 10-February 04
From: South Charleston, WV


Don't know if this will help you or not but it is a function which creates a worksheet from a recordset:

CODE
Function SendXL(rstObj As DAO.Recordset)
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object, strRightchars As String
    Dim iCount As Integer, fldCount As Long, icol As Long, recArray() As String, RecCount As Long, irow As Long, strColnam As String
    
' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
  
    ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True
    SetForegroundWindow xlApp.hWnd
    
    ' Copy field names to the first row of the worksheet
    fldCount = rstObj.Fields.Count
    For icol = 1 To fldCount
        xlWs.Cells(1, icol).Value = rstObj.Fields(icol - 1).NAME
    Next
    xlWs.Rows("1:1").Font.Bold = True
    
    ' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
        
        ' Copy the recordset to the worksheet, starting in cell A2
        rstObj.MoveFirst
        xlWs.Cells(2, 1).CopyFromRecordset rstObj
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets
        
    Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel
    
        ' Copy recordset to an array
        recArray = rstObj.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel
        
        ' Determine number of records

        RecCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
        

        ' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For icol = 0 To fldCount - 1
            For irow = 0 To RecCount - 1
                ' Take care of Date fields
                If IsDate(recArray(icol, irow)) Then
                    recArray(icol, irow) = Format(recArray(icol, irow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(icol, irow)) Then
                    recArray(icol, irow) = "Array Field"
                End If
            Next irow 'next record
        Next icol 'next field
            
        ' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(RecCount, fldCount).Value '= TransposeDim(recArray)
    End If

    ' eliminate columns whose names end in 'ID'
    
    fldCount = rstObj.Fields.Count
    iCount = fldCount
    Do While iCount > 0
        For icol = 1 To iCount
            strColnam = xlWs.Cells(1, icol).Value
            strRightchars = Right(strColnam, 2)
            If strRightchars = "id" Then
                xlWs.columns(icol).select
                xlApp.selection.Delete
                Exit For
            End If
        Next
        iCount = iCount - 1
    Loop
    ' Auto-fit the column widths and row heights
    xlApp.selection.CurrentRegion.columns.AutoFit
    xlApp.selection.CurrentRegion.Rows.AutoFit
    xlApp.errorcheckingoptions.backgroundchecking = False
    xlWs.Cells(1, 1).select

    ' Close ADO objects
    rstObj.Close
    Set rstObj = Nothing
      
    ' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

    Set xlApp = Nothing

End Function

--------------------
Robert Crouser
Go to the top of the page
 
zaxbat
post Mar 2 2018, 08:43 AM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


As usual, several different ways you could handle this. Tina's solution may be the most straight forward since it would take almost no changes (assuming your only problem is one of scope). If you do not understand scope and it's importance in Access, then you really need to get a handle on that.

http://sourcedaddy.com/ms-access/the-scope...-variables.html


This is a nice little tutorial .... but there are others... just make sure you understand it. Without this knowledge Access is going to drive you nuts.
This post has been edited by zaxbat: Mar 2 2018, 08:45 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
bakersburg9
post Mar 2 2018, 12:00 PM
Post#6



Posts: 5,209
Joined: 2-November 04
From: Downey, CA


Tina,
Thanks for chiming in - it is in a standard module, and is a Public Function... I just need a suggestion for the argument - what do I put in the parenthesis ?
Go to the top of the page
 
zaxbat
post Mar 2 2018, 12:17 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Just to make sure.....you understand that the standard modules in VBA are not same as Class, Form, or Report modules? In the pic, the arrow points to a standard module....the function needs to reside there and be declared as public.
Attached File(s)
Attached File  standardmodule.png ( 39.1K )Number of downloads: 1
 

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
tina t
post Mar 2 2018, 02:49 PM
Post#8



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
it is in a standard module, and is a Public Function... I just need a suggestion for the argument - what do I put in the parenthesis ?

well, i don't know, hon. you didn't answer my question about whether the function has any arguments. can you post the function, pls? also, hmm, being that this is a newer (.accdb) version of Access, i don't know about changes in how macros work - syntax of arguments and so on. can you run a test to see if you're using the correct syntax in the macro to call a function? how about writing a test macro that calls a built-in function like Date(). if that works, try a simple UDF that does something simple, like opening a msgbox to say hi. if both work for you, then a simple UDF that uses an argument, like maybe

CODE
Public Function MyText(ByVal str As String)

    Msgbox str

End Function

and call the function as MyText("hi")

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
bakersburg9
post Mar 2 2018, 06:30 PM
Post#9



Posts: 5,209
Joined: 2-November 04
From: Downey, CA



OK, so let me summarize:

I have a saved export named "Export-InvoicesToUpdate"


I have that object attached to a button on my form that works fine.

As part of my process, I have another button which carries out several actions
... that works perfectly, but when I run it, afterwards, I can't click my export to excel button, simply because it's covered up - and there's no advantage to all this button clicking - I want to piggyback this process of exporting to excel in the "first" button - add RUN CODE to the series of steps
performed by clicking the button

So I wrote a public function, housed in a standard module, which is simply:
CODE
DoCmd.RunSavedImportExport ("Export-InvoicesToUpdate")


I get an error message:

CODE
The expression you entered has a function name that Microsoft Access can't find

Attached File(s)
Attached File  PublicFunctionExportToExcel.png ( 54.23K )Number of downloads: 2
 
Go to the top of the page
 
bakersburg9
post Mar 2 2018, 06:56 PM
Post#10



Posts: 5,209
Joined: 2-November 04
From: Downey, CA


Well, I figured it out !!! cool.gif when I hovered my mouse pointer over the function name, it said: "? tell me more..." I clicked on it, and got:
QUOTE
To run a Sub procedure or event procedure written in Visual Basic, create a Function procedure that calls the Sub procedure or event procedure. Then use the RunCode macro action to run the Function procedure.
Haven't had much help with good old F1 over the years, but once I was blind, and now I see !

I did just what it said! (with not much confidence it would work) - I wrote a sub routine, and used the public function to call the sub - and it worked ! Go figure !

So to that stupid button that was eclipsed, I say: "bye, Felicia!"

Thanks, everyone for leading me to the answer ! cool.gif

Steve
Go to the top of the page
 
tina t
post Mar 2 2018, 07:38 PM
Post#11



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


good work finding a solution, Steve! :) in looking at the screenshot you posted (and forgive me, i am not familiar with the development interfaces in the .accdb versions of Access), it looks like you named a standard module as exportReportToExcel, and then gave the function in the module the same name exportReportToExcel. if that's correct, then my guess is that was the actual problem; in fact, i'm surprised you didn't get an error when you compiled the code - you can't name a function (or sub, AFAIK) the same name as a module. well, you can, of course, but it will err out.

i don't know if you didn't get the conflict error because there is no Option Explicit statement at the top of the module, but it's possible. best practice is to include the Option Explicit statement at the top of every module whether public or private, immediately below the Option Compare Database statement. suggest you set your copy of the software to include the statement by default on all new modules, but you'll have to add it manually to existing modules as the changed setting does not affect existing modules.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
zaxbat
post Mar 2 2018, 08:54 PM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yep, I learned that the hard way....option explicit always now....I went on many a wild goose chase before i learned that.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 01:07 AM