SQL Error Assigning RecordSource to subForm (1 Viewer)

[Sorrells]

I searched the forums for an answer without success. The SQL assignment below is returning the error:

Microsoft Access can't find the field "Task_Ordered" referred to in your expression.

The SQL receiving this error is:

Me![subTask_Maint].Form.RecordSource = "SELECT [Task_Ordered].Task_Order_ID, " & _
"[Task_Ordered].House_Room_ID, [Task_Ordered].Task_Order, [Task_Ordered].Task_Name, " & _
"[Task_Ordered].Schedule_Frequency, [Lookup:Frequency].Frequency, " & _
"[Task_Ordered].Week, [Task_Ordered].Day " & _
" FROM [Lookup:Frequency] INNER JOIN [Task_Ordered] ON [Lookup:Frequency].Freq_Index = " & [Task_Ordered].Schedule_Frequency & _
" WHERE ([Task_Ordered].House_Room_ID = " & Get_House_Room_ID & _
" And ([Task_Ordered].Week = " & 6 & _
" Or [Task_Ordered].Day = " & 9 & "))" & _
" ORDER BY [Task_Ordered].Task_Order"

where subTask_Main is the name of the subform and both Task_Ordered and Lookup:Frequency are tables.

The above SQL was derived by copying the SQL View of the a query as displayed below.

SELECT Task_Ordered.Task_Order_ID, Task_Ordered.House_Room_ID, Task_Ordered.Task_Order, Task_Ordered.Task_Name, Task_Ordered.Schedule_Frequency, [Lookup:Frequency].Frequency, Task_Ordered.Week, Task_Ordered.Day
FROM [Lookup:Frequency] INNER JOIN Task_Ordered ON [Lookup:Frequency].Freq_Index = Task_Ordered.Schedule_Frequency
WHERE (((Task_Ordered.House_Room_ID)=Get_House_Room_ID()) AND ((Task_Ordered.Week)=6)) OR (((Task_Ordered.Day)=9))
ORDER BY Task_Ordered.Task_Order;

I suspect that I have a subtle syntax error with all of these continue lines and quotations.

Any assistance and/or advice will be greatly appreciated!

 

[pottera]

Hi Sorrells, You are missing quotations on the sixth line and also in the 7th line around "get_room_no" - not sure if it's intentional.

hope it helps ... Peter R.

 

[Sorrells]

Peter,

Thanks for your response.

The 6th line is a wrap-around and is to my knowledge correct. It is an integer value. Line 7 is the same way.

The query runs and if I knew how to do so behind a command button, I'd just assign the query to the recordsource and run it.

But I've never figured that one out either!

Thanks for looking it over, I'd sure like to know why the query runs and this is giving me an errror!

 

[Jon K]

The code to assign the query name to the RecordSource is:-

Me![subTask_Maint].Form.RecordSource = "queryName"


You can also select the query for the RecordSouce in the subform design view.

 

[Sorrells]

Jon K,

Thanks for the syntax! I still don't know the problem with the SQL but the query sure runs good! I will return to the SQL question later when my client is not pressing me so hard.