Using a query dao.recordset when opening a report

[steven22554]

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:banghead:

 

[CJ_London]

is FoodLogDetails a table or query? Assuming the latter, does it refer to a form for one of its parameters? If so, is that form open?

 

[steven22554]

CJ_London
It is a query and it is used for 2 other forms neither of which is open. there is one parameter that refers to a global user number.

 

[vbaInet]

I'm not even sure why you've chosen not to use a subreport.

 

[CJ_London]

and the global user number is initiated how?

 

[steven22554]

CJ_London
when then app opens it open a user login form that set the user number via TempVars.Add "CurrentUserID", cboUserLogin.Value get it from the user profile table.

 

[CJ_London]

OK, suggest you test this value is correct at the time the recordst is opened.

You can either use the immediate window or debug.print or put a messagebox in your code just before your openrecordset action.

 

[steven22554]

CJ_London
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 know that part works cause the query has the userid as one of the fields. I've switched between users and the data changes to the other user.

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.

 

[steven22554]

CJ_London
Thanks for all your input. i have solved the problem with the help with someone else.
thanks again
steven

 

[CJ_London]

Glad you got it sorted. For the benefit of followers of the thread, why don't you post the solution?

 

[steven22554]

My solution to my problem is:
Instead of using all the vba code, just use the expression builder for each text box an insert the following: =Round(Sum(IIf([Breakfast]="Evening Snack",[TotalCalories],Null)),1), I rounded it to keep it from getting to large of results. that was the first one there are 11 others also. Alot easier with the old cut and paste routine.