Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 28th, 2005, 08:24 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Runtime Error 3061 - too few parameters

I am hoping someone could help. I have a database which users will access to update the status and forecast of business objectives and sub-objectives on a monthly basis.

The users will open the form which presents a parameter form based on a parameter query. The users will select the reporting month from a combobox which opens the reporting form (grouped by objective with the sub-objectives listed in a subdatasheet form for that specific month).

This works perfectly by restricting the sub-objectives by the month. The users will then update the status and forecast of these sub-objectives.

The problem arises when I go to export the status and forecasts of that specified month to a PowerPoint presentation (for reporting at our monthly management meetings) I am presented with "Error 3061 : Too few parameters.Expected 1". Sometimes this number is 2. and breaks on the line below.

Set recStatus = dbStatus.OpenRecordset(strSQL, dbOpenDynaset)

strSQL contains the code:

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & Forms!frmCriteria!cboMonth & "';"

I am at a total loss. When I go to debugging, the value being passed is correct (e.g. 1 for Jan etc).

I'm certainly not the best at SQL and vba for that matter, therefore any help would be greatly appreciated.


Thanx in advance


P.S. I've attached the complete code for the export button below.

Private Sub cmdExport_Click()

Dim dbStatus As Database
Dim recStatus As Recordset
Dim strSQL As String
Dim strStatus As String
Dim intValueStatus As Integer
Dim intValueForecast As Integer
Dim objPPApp As PowerPoint.Application
Dim objPPPres As PowerPoint.Presentation
Dim strFile As String
Dim intSOID As Integer
Dim strStatusShape As String
Dim lngSlideID As Long
Dim strStatusBox As String
Dim intEnablerID As Integer
Dim intForecastID As Integer
Dim strFace As String
Dim myDef As QueryDef


strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & Forms!frmCriteria!cboMonth & "';"

strFile = "H:\PoaP.ppt"

Set dbStatus = CurrentDb
Set recStatus = dbStatus.OpenRecordset(strSQL, dbOpenDynaset)

Set objPPApp = New PowerPoint.Application
objPPApp.Visible = msoTrue
Set objPPPres = objPPApp.Presentations.Open(strFile, msoFalse)

With objPPPres

    Do While Not recStatus.EOF

        intValueStatus = recStatus.Fields("fkStatusID").Value
        intValueForecast = recStatus.Fields("fkForecastID").Value
        intSOID = recStatus.Fields("ParentID").Value
        intEnablerID = recStatus.Fields("EnablerID").Value
        intForecastID = recStatus.Fields("EnablerID").Value

        Select Case intSOID

        Case 10
            lngSlideID = 1012

        Case 20
            lngSlideID = 1017

        Case 30
            lngSlideID = 1022

        Case 40
            lngSlideID = 1028

        Case 50
            lngSlideID = 1035

        Case Else
            MsgBox "A slide does not exist for this strategic objective!"

        End Select

        .Slides.FindBySlideID(lngSlideID).Select

        Select Case intEnablerID

        Case 11
            strStatusBox = "Rectangle 56"
        Case 12
            strStatusBox = "Rectangle 129"
        Case 13
            strStatusBox = "Rectangle 145"
        Case 21
            strStatusBox = "Rectangle 101"
        Case 22
            strStatusBox = "Rectangle 109"
        End Select


        Select Case intForecastID

        Case 11
            strFace = "AutoShape 120"
        Case 12
            strFace = "AutoShape 130"
        Case 13
            strFace = "AutoShape 146"
        Case 21
            strFace = "AutoShape 102"
        Case 22
            strFace = "AutoShape 110"
        End Select



        Select Case intValueStatus

        Case 1
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strStatusBox).Select
            With objPPApp.ActiveWindow.Selection.ShapeRange
                .Fill.Visible = msoTrue
                .Fill.Solid
                .Fill.ForeColor.RGB = RGB(0, 250, 0)
            End With
            strStatus = "Green"
            Debug.Print strStatus

        Case 2
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strStatusBox).Select
            With objPPApp.ActiveWindow.Selection.ShapeRange
                .Fill.Visible = msoTrue
                .Fill.Solid
                .Fill.ForeColor.RGB = RGB(250, 250, 0)
            End With
            strStatus = "Amber"
            Debug.Print strStatus

        Case 3
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strStatusBox).Select
            With objPPApp.ActiveWindow.Selection.ShapeRange
                .Fill.Visible = msoTrue
                .Fill.Solid
                .Fill.ForeColor.RGB = RGB(250, 0, 0)
            End With
            strStatus = "Red"
            Debug.Print strStatus
        Case Else
            MsgBox "Either the status/forecast of all enablers have not been set!"

        End Select


        Select Case intValueForecast

        Case 1
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strFace).Select
            objPPApp.ActiveWindow.Selection.ShapeRange.Adjustm ents.Item(1) = 0.8111
        Case 2
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strFace).Select
            objPPApp.ActiveWindow.Selection.ShapeRange.Adjustm ents.Item(1) = 0.7649
        Case 3
            objPPApp.ActiveWindow.Selection.SlideRange.Shapes( strFace).Select
            objPPApp.ActiveWindow.Selection.ShapeRange.Adjustm ents.Item(1) = 0.7181
        Case Else
            MsgBox "The status/forecast of all enablers have not been set!"
        End Select

    recStatus.MoveNext

    Loop


End With

recStatus.Close
objPPPres.SaveAs FileSave
objPPPres.Close
objPPApp.Quit

End Sub
Reply With Quote
  #2 (permalink)  
Old June 28th, 2005, 09:02 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, here is your sql string:

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & Forms!frmCriteria!cboMonth & "';"


1. Make sure that the field "fkMonthID" actually exists in your query and is spelled correctly.

2. Make sure your query is spelled "qryEnablerLookup"

3. do a debug.print or a msgbox(strSQL) or add a watch to see exactly how this string looks. If fkMonthID is a numeric field, then the single quote surrounding the expected value will give you another error after the "too few parameters" error is solved. (Data type mismatch)

These steps should solve your problem. Most likely you have misspelled the field name.

Reply With Quote
  #3 (permalink)  
Old June 28th, 2005, 09:16 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thanx for getting back.

I have double checked all the spellings etc but unfortunately I am still have the same trouble. If I select Feb from the drop down combobox for example - the value 2 is passed which is correct.

Could it simply be that I am trying to pass an integer into a string variable?
Reply With Quote
  #4 (permalink)  
Old June 28th, 2005, 09:47 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & Forms!frmCriteria!cboMonth & "';"

Try replacing with this:

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & str(Forms!frmCriteria!cboMonth) & "';"

or

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = " & str(Forms!frmCriteria!cboMonth) & ";"

or

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = " & Forms!frmCriteria!cboMonth & ";"

My guess is one of these will work...but of course, let me know!

Reply With Quote
  #5 (permalink)  
Old June 28th, 2005, 10:05 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi again.

I have tried each of the SQL statements in the code but unfortunately no luck. I am finding this very strange indeed as the query only has a single parameter and the debugging shows the correct value.

I've tried changing this to run the statement from the underlying table and this seems to work but am rather dubious on staying with this method due to column sorting and any inherent errors that may appear.

I'm really sorry for being a pain but your help is greatly appreciated.
Reply With Quote
  #6 (permalink)  
Old June 28th, 2005, 11:09 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

is fkMonthID a parameter or a field?

Reply With Quote
  #7 (permalink)  
Old June 28th, 2005, 11:34 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've taken fkMonthID from a field in tblResults and on the criteria entered [forms]![frmCriteria]![cboMonth].
Reply With Quote
  #8 (permalink)  
Old June 28th, 2005, 12:56 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

then it isnt neccessary to even have:

strSQL = "SELECT * FROM qryEnablerLookup WHERE [fkMonthID]" & " = '" & Forms!frmCriteria!cboMonth & "';"

all you need is

strSQL = "SELECT * FROM qryEnablerLookup;"

since [fkMonthID] is already selected for in qryEnablerLookup. So if your code works with the above assignment to strSQL instead of your old one, then you should be fine. Again, let me know.


Reply With Quote
  #9 (permalink)  
Old June 29th, 2005, 04:26 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again but unfortuantely I'm still experiencing the same error message.

My original though was the exactly same - as the rows have already been restricted it should be straight forward but unfortunately I have obviously missed something along the way.

I've opened the query direct from the database window after the query has been run and all the correct information is visible.

I find it strange that it works by redirecting the selected rows through the root table but not the query.

I'm really sorry for being a pain!!
Reply With Quote
  #10 (permalink)  
Old June 29th, 2005, 09:32 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Duh! you know,i think you need to remove the parameter from the query.

Try doing that.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing parameters to crystal reports at runtime tbhakee Crystal Reports 14 September 11th, 2013 05:49 AM
hi i got runtime error 13 Type Mismatch error sriharsha345 Access VBA 2 February 21st, 2008 09:30 AM
SQL runtime error 3061 chef01_99 Access VBA 2 June 17th, 2007 06:27 AM
mysterious error runtime error '451' coyotworks Excel VBA 1 May 12th, 2006 03:57 PM
passing parameters at runtime using crystal report anilkumar Crystal Reports 2 January 3rd, 2004 01:35 AM



All times are GMT -4. The time now is 07:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.