error when trying to append the tempTable

[gstylianou]

Hello to all friends,


I'm trying to write a code so that it gets the data from a query (QryPrint) and then to put them in a temporary table (tblWeekData) so that I can present them in a report.


I have written the following code but the vba gives error constantly on DietID which is a field (into query). I would appreciate if some friend from the forum could help me

Sub OpenReport()
Dim i As Integer, x As Integer, tmpMax As Integer, TheFood As String
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
CurrentDb.Execute "DELETE * FROM tblWeekPlan"
Set rsTarget = CurrentDb.OpenRecordset("tblWeekPlan", dbOpenDynaset)
For i = 1 To 25


rsTarget.AddNew
rsTarget.Fields("iNum") = i
rsTarget.Update
Next


For i = 1 To 7
Set rsSource = CurrentDb.OpenRecordset("SELECT QryPrint.* FROM QryPrint WHEREDietCode =" & [DietID] & " AND DayCode =" & i & " ORDER BY [Type of Meal]")
If rsSource.RecordCount Then
If tmpMax < rsSource.RecordCount Then tmpMax = rsSource.RecordCount
rsSource.MoveFirst
rsTarget.MoveFirst
For x = 1 To rsSource.RecordCount
rsTarget.FindFirst "iNum=" & x
rsTarget.Edit
TheFood = Nz(DLookup("FoodName", "tblFoodAnalysis", "[FoodID]=" & rsSource.Fields("Food")))
rsTarget.Fields("MealCat") = Nz(rsSource.Fields("[Type of Meal]"), "")
rsTarget.Fields("d" & i) = TheFood & " - " & Nz(rsSource.Fields("Conc"), "")
rsTarget.Update
rsSource.MoveNext
Next
End If
Next
CurrentDb.Execute "DELETE * FROM tblWeekPlan WHERE iNum > " & tmpMax

[pbaldy]

What error do you get? Where is DietID coming from (or supposed to be coming from)? Have you added a space between WHERE and DietCode? That's the exact spelling of the field name in the query?

By the way, is there a reason you can't just base the report on the query?

[gstylianou]

Hi pbaldy,

Thanks for your reply. The error is : "Cannot find the DietCode" but in the query there is this field...

Really don't know....

[ItsMe]

You are not answering all the questions Paul is asking. Also, I do not believe I have ever encountered that error message.

[pbaldy]

Along with the other questions, see if this helps find the problem:

http://www.baldyweb.com/ImmediateWindow.htm

what is the SQL of qryPrint?