Requery subform

Question

I want to requery my sub form by converting my SQL code into String and then perform requery function. The problem is, I cannot convert the SQL code below into Proper string that the .RecordSource can evaluate.

Here is my original SQL code:

SELECT tbl_Records.Inst_ID, [tbl_Instruments].[Type] & "-" & [tbl_Instruments].[Item] AS [Tool ID], tbl_Records.Cal_Date AS Cal_Date, tbl_Cal_By.Name_ABBR AS Cal_By, tbl_Records.As_Received, tbl_Records.Result, DateAdd("d",[tbl_Instruments]![Lead_Time],DateAdd("m",[qry_CofC]![Calibration Freq],[tbl_Records]![Cal_date])) AS Next_Due, DateDiff("d",Date(),DateAdd("d",[tbl_Instruments]![Lead_Time],DateAdd("m",[qry_CofC]![Calibration Freq],[tbl_Records]![Cal_date]))) AS Due
    FROM (tbl_Cal_By RIGHT JOIN (tbl_Instruments INNER JOIN tbl_Records ON tbl_Instruments.ID = tbl_Records.Inst_ID) ON tbl_Cal_By.ID = tbl_Records.BY) INNER JOIN qry_CofC ON tbl_Instruments.ID = qry_CofC.ID
    WHERE (((Exists (SELECT 1 FROM tbl_Records t
                     WHERE t.Inst_ID = tbl_Instruments.ID
                          AND t.Cal_date > tbl_Records.Cal_Date))=False));

And this is how I re-query :

Dim SQL As String
SQL = "ABOVE ORIGINAL SQL CODE"

subform_Records.Form.RecordSource = SQL
subform_Records.Form.Requery

Answer 1

The problem is likely to be the quotes (") in your query - try escaping them by doubling up - so

[tbl_Instruments].[Type] & ""-"" &

etc.