Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Invalid Procedure when running report

    I have a report that runs via VBA.
    It basically sorts by user selection on the month.

    it was working until a few days ago when I got the INVALID PROCEDURE ERROR (Error 5 i think).

    Here's the code;
    Code:
    Private Sub btnCatTotPrev_Click()
    Dim db As DAO.DATABASE
    Dim qry As DAO.QueryDef
    Set db = CurrentDb()
    Set qry = db.QueryDefs("Rpt_TotalCategorySortBy")
    qry.SQL = "SELECT [" & Forms!ReportsMain!cboUserMonth & "] AS SortBy, " & vbCrLf & _
    "[Category], [Total Of JobID], " & vbCrLf & _
    "Jan, Feb, Mar, " & vbCrLf & _
    "Apr, May, Jun, " & vbCrLf & _
    "Jul, Aug, Sep, " & vbCrLf & _
    "Oct, Nov, Dec " & vbCrLf & _
    "FROM Rpt_TotalCategory " & vbCrLf & _
    "ORDER BY [" & Forms!ReportsMain!cboUserMonth & "] DESC , [Category];"
    Set qry = Nothing
    Set db = Nothing
    
    DoCmd.OpenReport "TotCategory", acViewPreview
    Anyu suggestions as to get this working again please?

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    tbh I dont like the way you choose to open report with the query
    save your report with no record source and put a simpe code in OnLoad event of report to use your SQL as recordsource(i.e. Me.RecordSource ="yourSQL") then open the report from where you want.
    if you follow my case then your SQL must be changed to this:
    Code:
    SQL="SELECT [Forms]![ReportsMain]![cboUserMonth] AS SortBy, " _
        & "[Category], [Total Of JobID], Jan, Feb, Mar, Apr, May, Jun, " _
        & "Jul, Aug, Sep, Oct, Nov, Dec "  _
        & "FROM Rpt_TotalCategory "  _
        & "ORDER BY [Forms]![ReportsMain]![cboUserMonth] DESC , [Category];"
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think there is a lot of confusion between the presntation layer and the data layer.
    use you queries to extract and manipulate data
    use the form or report to format the data

    I don't understand why you are tying to insert a value into the query that is derived from a form.
    I don't understand why you have a series of hard code text lines hard code in query eg: "Jan, Feb, Mar, " & vbCrLf & _

    "SELECT [Category], [Total Of JobID] FROM Rpt_TotalCategory ORDER BY [Category];"

    unless the comobo box on the form is a multi select box I don't see hwat that is giving you in terms of extracting the data

    if you need a date banding then put it in a where clause.
    if this query changes frequently then Id do as Aran suggests and put the query in the report, not try and save it.. There are good reasons to save queries programatically.. say if the same query has to be run in a few months time, or the same query has to be run from several reports. It can also be a good idea to save the query if its fiendishly complex and you will get a performance benefit by submitting the query to the db so the query analyser can parse the query prior to use, although Ive rarely found that to be the case when usign JET.. server DB's yes, JET no.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    the form has a selection combo with MONTHS (eg: JAn, FEB) and the user selects this and then presses view report. This then displays a totals report where the selected month has them in alphanumeric order.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •