Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old August 13th, 2015, 10:58 AM
Idealsteve Idealsteve is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2015
Posts: 2 Idealsteve User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 34 m 44 sec
Reputation Power: 0
Parameter Problem with VBA

I am pretty new to visual basic and I have created some code which exports a load of queries to excel based on query names I have stored in a table, however I am encountering problems with a few of the queries as they run based on a value I have put in a form as it creates an error as it says I have no parameters.

Can anyone help

This is the code I am using

Code:
Option Compare Database
Function cmdExportQueries()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryQueryList")
Dim oXLApp As Excel.Application       'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Set oXLApp = New Excel.Application  'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Create new workbook
oXLApp.DisplayAlerts = False
oXLBook.Worksheets(3).Delete
oXLBook.Worksheets(2).Delete
Dim qryNumber As Integer
qryNumber = 1
Do Until rst.EOF
    Dim rstQry As DAO.Recordset
    Dim qrySQL As String
    Dim QryName As String
    QryName = rst!QueryName
 
    Dim FrmName As String
    FrmName = rst!FormName  

    qrySQL = "SELECT * FROM [" & QryName & "]"
    
    Set rstQry = CurrentDb.OpenRecordset(qrySQL)
    Set oXLSheet = oXLBook.Worksheets(qryNumber)
    oXLSheet.Range("A2").CopyFromRecordset rstQry
  
    For iCols = 0 To rstQry.Fields.Count - 1
        oXLSheet.Cells(1, iCols + 1).Value = rstQry.Fields(iCols).name
        oXLSheet.Cells(1, iCols + 1).Font.Bold = True
    Next  
    oXLSheet.Cells(1, rstQry.Fields.Count).AutoFilter
    oXLSheet.name = Left(QryName, 30)
    oXLBook.Sheets.Add After:=oXLBook.Sheets(qryNumber)
    qryNumber = qryNumber + 1
    rstQry.Close
    rst.MoveNext
Loop
oXLApp.DisplayAlerts = True
oXLBook.Worksheets(qryNumber).Delete
Set oXLSheet = oXLBook.Worksheets(1)
oXLBook.Sheets(1).Activate
Dim FilePath As String
FilePath = "F:\LSF Querys\"
Dim TheFileName As String
TheFileName = "BACS_1516_" & Year(Now()) & "_" & Month(Now()) & "_" & day(Now()) & ".xlsx"
oXLBook.SaveAs FilePath & TheFileName
oXLBook.Close
Set oXLBook = Nothing
Set oXLApp = Nothing
rst.Close
Set rst = Nothing
End Function


Thanks
Steve

Reply With Quote
  #2  
Old September 3rd, 2015, 08:22 AM
AjitK29 AjitK29 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2015
Location: India
Posts: 3 AjitK29 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 29 m 41 sec
Reputation Power: 0
Send a message via Skype to AjitK29
Can you specify the parameter error which you are getting? And for which line?

Reply With Quote
  #3  
Old September 3rd, 2015, 11:22 AM
Idealsteve Idealsteve is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2015
Posts: 2 Idealsteve User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 34 m 44 sec
Reputation Power: 0
Quote:
Originally Posted by AjitK29
Can you specify the parameter error which you are getting? And for which line?


Hi

The Error is appearing on the following line.
Code:
Set rstQry = CurrentDb.OpenRecordset(qrySQL)


The code works fine and exports all of my queries if I type my own values in the queries. The problem occurs when I am using the value in a text box on a form to run the query. the error I keep getting is

Runtime error '3061'
Too Few Parameters. Expected 1.

Thanks
Steve

Reply With Quote
  #4  
Old September 7th, 2015, 09:00 AM
AjitK29 AjitK29 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2015
Location: India
Posts: 3 AjitK29 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 29 m 41 sec
Reputation Power: 0
Send a message via Skype to AjitK29
I think this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Parameter Problem with VBA


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap