Results 1 to 14 of 14
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    help with runsql where statement

    i have a database for request, the employee submits a request and then the supervisor goes to my form and either selects "delete" or "proceed" which then changes the Boolean field to "false" so that the record does not come up again as a new entry. I have a form based on a query that sorts records that are not "true" in a Boolean field. the form has a combo box called "cboreview" with two options, "delete" or "proceed". when the supervisor selects proceed and then clicks next record i'm trying to get the record inserted into the table "workQueT" and then change the Boolean field on the form to "false" so that it does not requery. it works great except when i try and run the insert to only pick up the "proceed" records. not sure what i'm doing wrong but i'm sure that since this is my first attempt at this its probably something simple.

    Private Sub btnspl_Click()



    Dim strsql As String
    strsql = "INSERT INTO [WorkQueT]( [RequestID], [LocID], [DeptID], [SystemID], [AssetID], [ComponentID], [PartID], [OperatorID], [healthID], [WorkID], [Summary], [Desc], [PriorityID], [RequestDate], [review], [done] )"
    strsql = strsql + "SELECT [RequestT].[RequestID], [RequestT].[LocID], [RequestT].[DeptID], [RequestT].[SystemID], [RequestT].[AssetID], [RequestT].[ComponentID], [RequestT].[PartID], [RequestT].[OperatorID], [RequestT].[healthID], [RequestT].[WorkID], [RequestT].[Summary], [RequestT].[Desc], [RequestT].[PriorityID], [RequestT].[RequestDate], [RequestT].[review], [RequestT].[done]"
    strsql = strsql + "where cboreview='Proceed'"

    DoCmd.SetWarnings False
    DoCmd.RunSQL (strsql)
    DoCmd.SetWarnings True

    If Not IsNull(Me.cboreview) Then
    Me.done = True
    End If


    End Sub

    any help would be appreciated, thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Always do a Debug.Print strsql + "where cboreview='Proceed'"
    before running/executing the query to see how Access has rendered the SQL.

    & is the concatenation operator not +

    In your sql
    "INSERT...... (SELECT..... FROM TableName" & " where cboreview='Proceed' )"

    Make sure you have leading spaces (at least 1) in the string with the Where

    You will find with action queries, you can use a newer construct

    CurrentDB.Exeute strSQL, dbFailOnError which will run the query and report an error if an error should occur. It will not ask you a series of OK to proceed type steps as you get with RUNSQL.... for which you have suppressed Warnings.

    Good luck with your project.

    See the error and debugging tips here

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks Orange, not familiar with Debug.print, did some searching but have not found anything so far. I did change to the execute command and checked my spacing on the where but still getting the same error, " run-time error 3075: syntax error(missing operator) in query expression'[request].[done] where cboreview = 'proceed'". i'm lost, any more help or suggestions?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is the info from Chip Pearson, the link I suggested





    Debugging VBA Code

    This page describes methods for debugging your VBA code.

    Introduction
    Debugging a program is one of the most important steps in software development. Knowledge of VBA's debugging tools can make debugging easier and more productive. This page describes several of VBA's built-in debugging tools you can use when testing and debugging your application.
    Stepping Through Code
    One of the first methods to debug code is to step through the code one line at a time. To step through code, put the cursor on the first line of code to be analyzed and press F8 or choose Step Into on the Debug menu. The next line of code to be executed will be displayed in yellow background with a black font. Note that the highlighted line of code has not yet been executed -- it is the next line to execute.
    If your code calls another procedure, stepping through the code with F8 will cause execution to enter the called procedure in a line-by-line sequence. If you want to execute the called procedure without stepping through it, press SHIFT F8. This will execute the called procedure and then pause on the line of code after calling the procedure. If you are already stepping through a procedure, you can press CTRL F8 to resume code execution line-by-line. At any time you are paused either in step-by-step mode or at a breakpoint (see below), you can press F5 or Continue from the Run menu to cause VBA to run to completion or until a pause statement is encountered.
    Whenever you are paused in step-by-step mode, you can query or change a variable's value from the Immediate window.
    Break Points And The Stop Command
    A breakpoint is a marker placed on a line of code that causes execution to pause immediately before executing that line. You can add a breakpoint to a line of code by putting the cursor on the line of code in question and pressing F9, choosing Toggle Breakpoint on the Debug menu, or clicking in the left margin next to the line of code. When a breakpoint is set, the line is displayed in brick-red background with a white font. When you run the code, execution will pause immediately before the line of code with the breakpoint and will display it in yellow background with a black font. Note than the line in yellow has not yet been executed -- it is the next line of code to run.
    While the code is paused at the breakpoint, you can issue commands in the Immediate window to change or query a variable's value. To view the content of a variable, enter a ? character followed by the name of the variable and then press ENTER. You can change a variable's value by entering VariableName = NewValue in the Immediate window and pressing ENTER.
    If the Immediate window is not visible (typically at the bottom of the VBA Editor screen), press CTRL G or choose Immediate Window from the View menu to make the window visible.
    To remove a breakpoint, put the cursor on the line of code and press F9. You can clear all breakpoints by choosing Clear All Breakpoints from the Debug menu or pressing CTRL SHIFT F9.
    VBA also provides the Stop command. This simply stops code execution on that line of code and enters break mode.
    Once you are finished debugging the code, be sure to go back and clear all breakpoints (choose Clear All Breakpoints from the Debug menu or press CTRL SHIFT F9) and be sure to remove or comment out all Stop statements.
    When you are paused at a breakpoint or in step-by-step mode, you can change the next line to be executed, either before the current line to re-run a section of code, or after the line to skip statements. Right-click the line where you want execution to resume and right-click and choose Set Next Statement or choose Set Next Statement from the Run menu. Execution will resume at the selected line of code.

    The Debug Command

    VBA provides a Debug object with two properties, Print and Assert that you can use display a variable's value and to control the program flow. Debug.Print will write what follows it to the Immediate window. Code execution is not interupted. After displaying the text in the Immediate window, code execution continues to run. You can mix literal text with variable names in the Debug.Print statement. For example,
    Debug.Print "The value of variable X is: " & X
    You can display several variables at once in the Immediate window by separating them with commas. For example,
    Debug.Print X, Y, Z
    The Debug.Assert command is a conditional breakpoint that will cause execution to pause on the Debug statement if the expression that following the Assert statement is False. For example,
    Debug.Assert Var >= 0
    This will pause on the Debug.Assert statement if Var >= 0 is False; that is, it will pause if Var is negative. It may seem backwards that execution is paused when the condition is False rather than True, but the Assert method was adopted from the C language, and its usage remained the same as in C.
    Be sure to remove or comment out the Debug.Print and Debug.Assert statements when you are finished debugging. You generally don't want these statements to be operative during normal usage of your application.

    You can also see if this youtube video by Steve Bishop is helpful.


  5. #5
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your sql string does not include a FROM component

  6. #6
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks Orange, I believe I have watched that video before but wasn't pertinent then as I didn't need it at the time rewatching it now is making more sence now that i'm trying to learn and use the code. for now as a quick fix I put a if statement in front of the sub looking for "proceed". any more suggestions would be helpful to figure out the where statement.

  7. #7
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    ok, still need some more help. with lots of research today, I believe the select case works best for my situation but when I use the currentdb.execute command it creates all the records from my table in the SQL, making all the records in the new table. let me explain a little, I have the header showing the full record with the buttons that will create the new record and below in the form is the rest of the sorted records to be reviewed next in a continuous form. how do I get the execute sql to only update the active record on the header of the form?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Did you see the post by Ajax (Post # 5)? The "SELECT" query is missing the "FROM" clause; what table to pull the data from.
    The SQL from your post #1 should look like
    Code:
    strsql = "INSERT INTO [WorkQueT] ([RequestID], [LocID], [DeptID], [SystemID],
     [AssetID], [ComponentID], [PartID], [OperatorID], [healthID], [WorkID], [Summary], [Desc], [PriorityID], [RequestDate], [review], [done] )"
    strsql = strsql & "SELECT [RequestID], [LocID], [DeptID], [SystemID], [AssetID], [ComponentID], [PartID], [OperatorID], 
    [healthID], [WorkID], [Summary], [Desc], [PriorityID], [RequestDate], [review], [done] FROM RequestT"
    strsql = strsql & " WHERE cboreview='Proceed'"


    how do I get the execute sql to only update the active record on the header of the form?
    That is where the PK field comes in or you have a way to indicate which is the active record (as in a check box).
    (the WHERE clause)

  9. #9
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks ssanfu. I had that in the new one that I hadn't posted. sorry for this code being different but I was getting confused on the other one so I created a simpler version, one table with records called table1, one table waiting on records called proceed and a simple form. it works until I add the where clause, without the where clause it adds all the records but where I try and narrow it down to the record in focus I don't get anything? I know its some simple learning curve thing but i'm not finding it.

    Dim x As String
    x = cbopro.Value
    Select Case x
    Case Is = "proceed"
    boxpro.Value = True
    Dim strsqlpro As String
    strsqlpro = "INSERT into [proceed],[tableid],[name1]"
    strsqlpro = strsqlpro & "SELECT [table1].[tableid],[table1].[name1]FROM [table1]"
    strsqlpro = strsqlpro & "WHERE boxpro.value='True';"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strsqlpro)
    DoCmd.SetWarnings True
    'CurrentDb.Execute (strsqlpro)
    MsgBox "proceed done"

    DoCmd.GoToRecord , , acNext
    cbopro.Value = ""

    Case Is = "delete"
    done.Value = True
    MsgBox "delete"
    DoCmd.GoToRecord , , acNext
    cbopro.Value = ""

    End Select


    End Sub

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When you are posting code, you should click on the hash mark (#) and paste the code between the "code" tags.

    Now I am confused...

    Maybe this example dB will help you. You can add more names, then click on the selected check box and click the "add button". The insertions will display in the list box in the form footer.

    It is very,very simple.

  11. #11
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't disable warnings until you have the code running properly - as it is you need a space before the WHERE

  12. #12
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    test1.zip

    Thank you for you continued help and patience, i'm still trying to learn and this is my first attempt at using a SQL inside a VBA code. sorry for the confusion ssanfu. attached is my test DB that I am trying to get to work. ssanfu, I was able to get the results you got but what I am attempting if its possible is to only move the record in focus and not the whole table. if I use the query1 it works, only one record is moved and not the whole table, but when I attempt the same SQL in the code i'm getting no results. what am I missing here.

  13. #13
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    ok, so I think I have it. I was looking around pbaldy's site on debugging VBA and noticed something in one of his codes, so I tried it and so far it worked.
    strsql = strsql & " WHERE table1.tableid=" & Me.tableID
    please feel free to check my work and make suggestions if you see a better way.

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    please feel free to check my work and make suggestions if you see a better way.
    OK.... here goes.
    I have a better idea of what you are trying to do.

    Access Setup - This is the changes to Access I do:

    FILE/OPTIONS
    Current Database option
    -Application Options section
    ....select "Overlapping Windows (radio button)
    ....uncheck "Enable Layout View"
    ....uncheck "Enable Design Changes for tables in Datasheet View"

    -Name autocorrect options section
    ....uncheck "Track name AutoCorrect" (aka AutoCorrupt)


    Object Designers option
    -Form/Report design view section
    ....Check "Always use event procedures


    -Error checking in form and report design view section
    ....uncheck "Check for unassociated label and control
    ....uncheck "Check for new unassociated labels

    ....all other options checked



    In VBA IDE:
    Tools/Options
    -Editor Tab
    ....uncheck "Autosyntax check
    ....Check "Require Variable Declaration"

    ------------------------------------------------------------------

    Read about "Naming conventions" - Pick one and try and stick with it.
    "Value" is the default property. You don't have to type it.

    Code:
         x = cbopro.Value
    is the same as
    Code:
         x = cbopro
    ------------------------------------------------------------------

    ALWAYS put the declarations (Dim statements) at the TOP of the procedure.
    I have see where there is a DO loop and there are DIM statements inside the loop - a big No-No.
    ------------------------------------------------------------------

    I finally figured why the WHERE clause wasn't working correctly.
    You had
    Code:
    strsqlpro = strsqlpro & " WHERE (((Table1.tableID)=[Form1]![tableID]))"
    You cannot refer to a form this way. You don't have the collection in the clause.
    In VBA, the proper way is
    Code:
    strsqlpro = strsqlpro & " WHERE Table1.tableID = " & Forms!Form1.tableID
    and you have to concatenate the value.

    Or, if the code is in the form module (not a standard module), you can use the "ME" keyword.
    "Me" is a shoretcut for "Forms!FormName".
    This would also be correct (and work):
    Code:
     strsqlpro = strsqlpro & " WHERE Table1.tableID = " & Me.tableID & ";"
    ------------------------------------------------------------------

    I always use the "Me" keyword when referring to to a control on a form. Helps differentiate variable names from form controls. Especially if you don't use a prefix for code variables.
    Which is easier to know which is the control?
    Code:
        x = cbopro
        x = Me.cbopro
    Along with that, take the time to rename control to something useful.
    "Text4" and "Combo3" aren't very descriptive, are they?
    And Access has the bad habit of naming a bound control the same as the field name.
    I would change the name of a text box control bound to field "FName" to "tbFName".
    That way I know the control name is "tbFName" and the field bound to it is "FName". Access can get confused (sometimes) if the control name is the same as the bound field name.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-27-2015, 12:25 PM
  2. Replies: 6
    Last Post: 02-24-2014, 09:26 AM
  3. Compile Error: Syntax Error in DoCmd.RunSQL Statement
    By Evilferret in forum Programming
    Replies: 1
    Last Post: 08-27-2012, 12:32 PM
  4. problem with my DoCmd.RunSQL statement
    By cgclower in forum Access
    Replies: 1
    Last Post: 07-24-2011, 06:12 PM
  5. my first DoCmd.RunSQL
    By broecher in forum Programming
    Replies: 4
    Last Post: 11-05-2010, 09:35 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