Results 1 to 12 of 12
  1. #1
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6

    Using a query dao.recordset when opening a report

    I’m trying to use this little tid bit of vba code to fill in some textboxes on a report I’m putting together without any kind of luck finding anything on google.

    Private Sub Report_load()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    10 Set db = CurrentDb
    20 Set rst = db.OpenRecordset("FoodLogDetails")
    30 If rst.RecordCount = 0 Then GoTo Cleanup
    40 rst.MoveFirst
    50 Do Until rst.EOF
    60 Select Case rst![WhichMeal]
    61 Case "Breafast" 'if matched add to textbox others
    70 txtBrkCalTot = txtBrkCalTot + TotalCalories 'total calories


    80 Case "AM Snack"

    90 Case "Lunch"

    100 Case "PM Snack"

    110 Case "Dinner"

    120 Case "Evening Snack"

    130 End Select
    140 rst.MoveNext
    150 Loop
    160 Debug.Print WhichMeal
    Cleanup:
    db.Close
    rst.Close
    Set rst = Nothing
    End Sub
    I've tried different thing and kept getting different errors. The one I get with the current snip is at line 20, Run time error # 3061 - Too few parameters. Expected 1. I’m using a query for my recordset and the same one the report is bound to. I’ve used this code in other parts of my app and it’s worked fine. But its been used with forms and subforms not reports. I’m thinking that may have something to do with it but not sure. If anybody could help I sure would appreciate it. I’m using Access 2013

    Steven
    Last edited by steven22554; 07-17-2014 at 04:34 PM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Does the FoodLogDetails query have dynamic parameter?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6
    Not sure what you mean about dynamic parameters. I'm still learning things with vba and other parts but I'm a comin along.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Dynamic parameter would be criteria in the query that requires user to input a value in a popup prompt or that references a control on form.

    Post the SQL statement of the query. Switch query builder to SQL View to see the statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    61 Case "Breafast" 'if matched add to textbox others

    Did you mean Breakfast??

    What is this "Set qdef = Nothing"??

  6. #6
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6
    june7
    no to the Dynamic parameter. I have had absolutely no luck at all with SQL.
    Oh BTW! thanx for the quik responce.

    orange;
    line 61, That's how a case statement works "I hope, I hope". I ain't got that far yet. Can't get past line 20.
    It's supposed to be "set rst=nothing"

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    20 Set rst = db.OpenRecordset("FoodLogDetails", dbOpenDynaset)

    or

    20 Set rst = DBEngine(0)(0).OpenRecordset("FoodLogDetails", dbOpenDynaset)

    Maybe you should tell us why you are doing this. Could be there is a simpler way to accomplish.

    Is the query a CROSSTAB? Does the query use other queries - any with a dynamic parameter? Post SQL statement(s).

    If you want to provide db, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6
    june7
    let me do some back tracking here. I am building an nutritional and health app for diabetics. Keeping track of insulin usage, food nutrition and exercise with the ability to print reports to make working with my doctor a little easier. Now back to your question.

    I made the report with the wizard and all the data is filtered by date and is in the correct spot. I am trying to add in text boxes below each column "Calories" Total Fat", "Protein", of numbers for totals filtered according to which meal "Breafast", "AM Snack", "Lunch", and so on, of the day. here is an image of my report. Thanx for the help so far.
    Click image for larger version. 

Name:	FoodLogDetailsReport.png 
Views:	11 
Size:	37.4 KB 
ID:	17320

    I'v changed line 20 to something similar to this with the same results run time error.
    the query is a select query
    What gets me how the code work great with other part of my code just not in this report. Just gives me that beatin my head against a wall feelin.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't need the VBA procedure.

    Put textboxes in a footer section. Expression in each textbox like:

    =Sum(IIf([Meal]="breakfast",[Calories],0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6
    june7
    With the immortal words of Gomer Pyle "Thank Ya! Thank Ya! Thank Ya! Thank Ya! Thank Ya!".
    Change all the parts to match all my fields and the rest and it worked great. The only bad part yet to come is all the typin I'm goin to have to do. I wish I had all the book learnin youins has had.
    Thank you very much.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Copy/Paste should help speed that up. Or select a group of textboxes, enter expression in ControlSource then visit each individually to fix the particulars.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    steven22554 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6
    Already got it done. just was funnin with ya. but thanks a heap. I tried something similar to what you suggested only difference was I used Dsum with no luck. several ways with negative results.

    thanks
    steven

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 05-30-2013, 02:02 PM
  2. Replies: 1
    Last Post: 05-27-2013, 08:54 AM
  3. Replies: 1
    Last Post: 01-10-2012, 03:44 AM
  4. Opening a report
    By mwabbe in forum Access
    Replies: 3
    Last Post: 09-17-2010, 09:19 AM
  5. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums