Results 1 to 4 of 4
  1. #1
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35

    Writing a query based on input from a text box on a form (from query in query list, not in VBA)

    I have a toggle button that, when pressed, is supposed send the value ("parameter" field) of a list selection to a table (tblConsumed) it is also supposed to send the value of a textbox ("used_today" field). It also records the date. In VBA, the the following code works correctly:
    Code:
    DoCmd.RunSQL "insert into tblConsumed([day], parameter, used_today) values (date(), [Forms]![frmvettest]![lstParameter], [Forms]![frmvettest]![txtSlidesUsed]);"
    I'm trying to get away from VBA though. I created a query, called qrySendSlide, in the query list in Access that looks like this (same as the VBA code):
    Code:
    INSERT INTO tblConsumed ( [day], parameter, used_today )
    VALUES (date(), [forms]![frmVetTest]![lstParameter], [forms]![frmvettest]![txtslidesused]);
    When the toggle button is pressed, this is the VBA code that I'd like to ideally use:
    Code:
    DoCmd.RunSQL "qrySendSlide"
    When this code runs, however, I get the error: "Run-time error '3129': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT', or 'UPDATE'." (Even though I have the "insert" statement.)

    I also tried making the query using the query design. After doing this, the SQL comes out to look like this:
    Code:
    INSERT INTO tblConsumed ( [day], Forms!frmvettest!lstParameter, Forms!frmvettest!txtSlidesUsed )
    SELECT Date() AS Expr1, [Parameter] AS Expr2, [Used_Today] AS Expr3;
    I get the same error with this code.

    Also, the data types do match. Day: Date/Time, Parameter: Text, used_today: Number (integer). I left out the ID (Autonumber).



    Is there a way for me to do this using a pre-written query in the query list outside of VBA, or do I need to write out that entire query in VBA.

    Thank you,
    Brad

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    You shouldnt need vba. you can do most everything with queries.
    tho, you run queries with
    docmd.openquery "qrySendSlide"

    .runSQL is only for sql text.
    The paramaters should be done from a form. Put the form path in the criteria of the query.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    The INSERT VALUES really should work.

    Field names cannot be dynamic in a query object.

    SELECT must have FROM clause.

    INSERT INTO tblConsumed ([day], [parameter], used_today)
    SELECT Date() AS Expr1, AForms!frmvettest!lstParameter AS Expr2, Forms!frmvettest!txtSlidesUsed AS Expr3 FROM tblConsumed;

    Parameter is a reserved word. Should not use reserved words as names. Day is also an intrinsic function and really should not use as field name.

    What's wrong with running the SQL in VBA? You are running a VBA procedure anyway.

    I prefer:

    CurrentDb.Execute "insert into tblConsumed([day], [parameter], used_today) values (date(), [Forms]![frmvettest]![lstParameter], [Forms]![frmvettest]![txtSlidesUsed]);"
    Last edited by June7; 07-12-2017 at 02:42 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35
    OK. Thanks, it works correctly now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-02-2015, 05:33 PM
  2. Replies: 8
    Last Post: 06-04-2014, 10:41 AM
  3. A query based on user input in form
    By drhassan in forum Queries
    Replies: 17
    Last Post: 01-20-2014, 06:00 PM
  4. Replies: 5
    Last Post: 08-07-2012, 11:14 AM
  5. Run a query based on an input from a form
    By apoorv in forum Queries
    Replies: 4
    Last Post: 07-11-2011, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums