0

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
Ajean
  • 5,528
  • 14
  • 46
  • 69
Jason Chan
  • 45
  • 10
  • does your sql code work in a saved query? if so, you can just reference your record source to that saved query and use me.subformname.requery – SunRay Mar 08 '16 at 21:11

1 Answers1

0

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

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

etc.

FJT
  • 1,923
  • 1
  • 15
  • 14