Solved

Access 2003 not recognising VBA script as a callback function

Posted on 2011-09-19
4
441 Views
Last Modified: 2012-06-21
Hello,

I have written the following vba script, and following the advice from a previous question have setup a custom buton in an Access toolbar using the function_name() criteria in 'on action'. (I've used the name of the function) However when I click the custom button I get an error message saying "Can't run the macro or callback function - make sure it exists and takes the correct paramiters"

VBA code atatched.

Am I doing something wrong here?

Thanks

Josh
Option Compare Database

Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String


Option Explicit


'  This module requires references to the
'  following object libraries:
'
'  1. Microsoft Excel X.X Object Library,
'    where X.X is the Excel Version Number.
'
'  2. One of the following:
'
'    For mdb files:
'      Microsoft DAO 3.6 Object Library
'      (DAO360.DLL)
'    For ACCDB files (Access 2007):
'      Microsoft Office 12 Access Database Engine Objects
'      (ACEDAO.DLL)
'      This reference should be set already.
'
'  To set the reference, in the VBA editor:
'    Tools > References.


Public Function Export_BR_Data()

  '  Excel constants:
  'Const strcXLPath As String = "C:\Josh\My Docs\Project List Data\OUTPUT\AG_ACCRUALS_TEMPLATE.xls"
 ' Const strcWorksheetName As String = "Sheet1"
  Const strcCellAddress As String = "A2"
  
  '  Access constants:
  Const strcQueryName As String = "QRY109_BRCHARGERPT_COLLATE_JOURNAL"
  Const strcQueryName2 As String = "QRY100_BR_CHARGERPT_OVER_6WEEKS_OLD_AND_CLOSED"
  Const strcQueryName3 As String = "QRY101_BR_CHARGERPT_FUTURE_TIMESHEETS"
  Const strcQueryName4 As String = "QRY102_BR_CHARGERPT_OVER_50_HOURS"
  Const strcQueryName5 As String = "QRY111_BRCHARGERPT_ACC_COST_CODE_ERRORS"
  Const strcQueryName6 As String = "QRY110_BRCHARGERPT_ROLE_ERRORS"
  
  
  '  Excel Objects:
  Dim objXL As Excel.Application
  Dim objWBK As Excel.Workbook
  Dim objWS As Excel.Worksheet
  Dim objRNG As Excel.Range
  
  ' Excel Varaiables:
  
  Dim RW, x As Integer
  Dim AC As String
  
  
  '  DAO objects:
  Dim objDB As DAO.Database
  Dim objQDF As DAO.QueryDef
  Dim objRS1 As DAO.Recordset

  
  'SQL statements:
  Dim SSQL As String
  Dim intcolindex As Integer
  
  'Get Sheet 1 Data______________________________________________________________________________
  
  SSQL = "SELECT * FROM " & strcQueryName
  
      '  Open a DAO recordset 5 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
  
     'Open a new XL file
     
  Set objXL = New Excel.Application
  objXL.Visible = True
  
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWBK = objXL.Workbooks.Add
          Set objWS = objWBK.Worksheets("Sheet1")
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "Journal"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
  
  'Next Sheet
  
   SSQL = "SELECT * FROM " & strcQueryName2
  
      '  Open a DAO recordset 5 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
    
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWS = objWBK.Worksheets("Sheet2")
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "OLD AND CLOSED"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
  
'Next Sheet
  
   SSQL = "SELECT * FROM " & strcQueryName3
  
      '  Open a DAO recordset 1 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
    
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWS = objWBK.Worksheets("Sheet3")
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "FUTURE TS"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
  
  'Next Sheet
  
   SSQL = "SELECT * FROM " & strcQueryName4
  
      '  Open a DAO recordset 1 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
    
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWS = objWBK.Worksheets.Add
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "50 +"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
  
   'Next Sheet
  
   SSQL = "SELECT * FROM " & strcQueryName5
  
      '  Open a DAO recordset 1 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
    
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWS = objWBK.Worksheets.Add
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "Cost Code Errors"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
  
   'Next Sheet
  
   SSQL = "SELECT * FROM " & strcQueryName5
  
      '  Open a DAO recordset 1 on the query:
  Set objRS1 = CurrentDb.OpenRecordset(SSQL)
    
    
   '  Open Excel and point to the cell where
          '  the recordset is to be inserted:
         
          Set objWS = objWBK.Worksheets.Add
          Set objRNG = objWS.Range(strcCellAddress)
          objRNG.CopyFromRecordset objRS1
  
   'put in column heads
         
         AC = "A1"
         For intcolindex = 0 To objRS1.Fields.Count - 1
         
           objWS.Range(AC).Offset(0, intcolindex).Value = objRS1.Fields(intcolindex).Name
            
         Next
  
  'Format Sheet
  
  objWS.Name = "JRSS Errors"
  
  
  'close recordset
  objRS1.Close '_________________________________________________________________________________
 
Exit_SaveRecordsetToExcelRange:

CleanUp:

  '  Destroy Excel objects:
  Set objRNG = Nothing
  Set objWS = Nothing
  Set objWBK = Nothing
  Set objXL = Nothing
  
  '  Destroy DAO objects:
 ' If Not objRS1 Is Nothing Then
  '  objRS1.Close
  '  Set objRS1 = Nothing
 ' End If
  Set objQDF = Nothing
  Set objDB = Nothing
  
GoTo Closeses
    
Error_Exit_SaveRecordsetToExcelRange:

  MsgBox "Error " & Err.Number _
    & vbNewLine & vbNewLine _
    & Err.Description, _
    vbExclamation + vbOKOnly, _
    "Error Information"
    
  GoSub CleanUp
  Resume Exit_SaveRecordsetToExcelRange

MsgBox ("Job Complete")

Closeses:
End Function

Open in new window

0
Comment
Question by:bedsingar
  • 3
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36559166
Try the following troubleshooting steps:

- Make sure you have checked the reference for Microsoft Excel
- Make sure you have checked the DAO reference
- Ensure that your function runs as expected without the toolbar button
- Double-check the spelling of your function name (I usually copy paste to make 100% certain when in doubt)
- I'm pretty sure you need the parentheses after the function name Export_BR_Data(), but try it both with and without
-  I took a quick look over your function and didnt see any, but double-check that if there are any other functions/subs being called that they are also defined as Public.

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36559202
One more thought -

Make certain that the module where your function resides does NOT have the same name as the function itself.

So if your function name is Export_BR_Data(),  give the module that houses it a name like modExport_BR_Data or anything other than "Export_BR_Data".
0
 

Author Comment

by:bedsingar
ID: 36559242
You're a star :) (And I need to learn not to close threads so quickly!)

solution was in your second post & no () required.

Thanks

Josh
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36559298
Glad to help out -

Just fyi, If you run into issues with a solution I've posted even after a question is closed, if I am around I am always happy to work through it with you if you just post a comment to the original thread.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now