Is there a way to run an Append SQL that only looks at the current records from a form (I really hope so) and if so could someone please help me with the syntax to make it work? I would be quite grateful. Also, if anyone has a better way to add records that would be helpful as well. I'm trying to add records from a subform to another table. The SQL works, however it runs it for ALL residents and not just the current resident.
DoCmd.RunSQL "INSERT INTO [Weekly Targets] ( MedicalRecordNumber, AdmissionNumber, TargetBaseline, WeeklyTargetBehavior )SELECT [tbl BPRS Rating Tool].[MR#], [tbl BPRS Rating Tool].[Admit#], [tbl BPRS Rating Tool].Uncooperativeness, 'Uncooperativeness' AS Expr1 FROM [tbl BPRS Rating Tool] Where ((([tbl BPRS Rating Tool].UncooperativenessDaily)=True))"
The issue is that I only want to add this for the new residents (i.e. current view) and not ALL residents already in the system. Also, there are 20 other targets, however they will not always be added...hence the existing Where clause. Is it possible to pass the MR# from the form to the SQL Statement as an additional part of the Where clause?
How would I place that into the SQL statement? When I tried Where ((([tbl BPRS Rating Tool].UncooperativenessDaily)=True) AND (([tbl BPRS Rating Tool].MR#)=Forms![BPRS Rating Tool]!MedicalRecordNumber))" It gave me a syntax error. All the parenthesis and brackets and such gets confusing. Sorry, but I'm still learning this stuff (mostly by trial and error and the help of kind people more knowledgeable than I).