Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    Question Unanswered: Syntax Question for SQL Statement

    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.
    Last edited by RAPUNIT; 10-05-05 at 02:24.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    The INSERT INTO Statement

    The INSERT INTO statement is used to insert new rows into a table.

    Syntax

    INSERT INTO table_nameVALUES (value1, value2,....)

    You can also specify the columns for which you want to insert data:

    INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....)


    .
    Last edited by CyberLynx; 10-05-05 at 02:33.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    This is what I have

    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?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup, there is. Use the syntax:

    forms!yourFormName!ControlWithYourPrimaryKey
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    Sorry, still learning

    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).

  6. #6
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    Eureka

    Apparently using the # symbol when naming fields is not a good idea. Thanks for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •