Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL String (2003)

    I entered the following on the "On Click" event of a "CommandButton". However, it doesn't work. I've been reading about this all day long to no avail. Did I miss a comma? I would like to do it on my own but, it's not working. Any ideas welcomed. Thanks!
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    Dim strSQL As String
    strSQL = "INSERT INTO tblActionID ( ActionID, SubjectID ) WHERE (((tblActionID.ActionID)=GetplaceHolderAcTion()) AND ((tblActionID.SubjectID)=GetPlaceHolderCS()));"
    CurrentDb.Execute strSQL, dbFailOnError

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL String (2003)

    What you missed is the entire section of the SQL that tells it what values to insert. What are you trying to do with the WHERE clause? The basic syntax of the INSERT INTO statement is ...

    <code>INSERT INTO target [(field1[, field2[, ...]])]
    VALUES (value1[, value2[, ...])</code>

    You need to tell use exactly what you're trying to accomplish.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL String (2003)

    Thanks for your help, Charlotte!

    What I really want to do is add a single new record to "tblActionID" from data entered by the user into two "TextBox" on a Form, and then close the Form. No scrolling or deletions or editing through the Table. That's about it! The "TextBox" are bound to the corresponding fields in "tblActionID". Do you think that waht follows will work?

    Dim strSQL As String

    strSQL = "INSERT INTO tblActionID (ActionID, SubjectID) VALUES (" & Me.strCS & ", " & Me.intAddCA & ");"
    CurrentDb.Execute strSQL, dbFailOnError
    - - - - - - - - - - -
    DoCmd.RunSQL strSQL
    - - - - - - - - - - -
    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL String (2003)

    If strCS contains a text value, you must enclose the value in quotes, you can use Chr(34) for this:

    strSQL = "INSERT INTO tblActionID (ActionID, SubjectID) VALUES (" & Chr(34) & Me.strCS & Chr(34) & ", " & Me.intAddCA & ");"

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL String (2003)

    If the form is bound to the table, you don't need a SQL statement at all. Just enter the data and click a Save button to save the record and close the form.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL String (2003)

    Hans ... thanks for the info. My fields are numeric but I'm sure that I'll encounter a situation where your suggestion will come in handy.

    Charlotte ... Thanks to you, too. I don't want users to scroll through the Table and make nasty thing. I just want my form to show two blank TextBox controls that they fill and the data gets entered in the Table. The form should save the record to the Table and close as soon as a Button is clicked. In addition to the SQL statement, I've tried DoCmd.Save acTable, "tblActionID", but the items are not saved. Honestly, although I'm reading and learning like crazy, this one I can't simply get. Any guidance from you, Hans or any other fellow member will be appreciated. Thanks!

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL String (2003)

    Hans ... the "str" was a mistake I didn't take the time to fix; until now. I'm attaching the file, as suggested. I hope I didn't leave anything pertinent out. Thanks for the help!
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL String (2003)

    If both fields are numeric, why is one control named strCS and the other one intAddCA? "str" is used to indicate a string, and "int" for an integer.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL String (2003)

    If you don't want the data to be saved to the table automatically, the form should be unbound, i.e. the Record Source property of the form and the Control Source property of the text boxes should be blank.

    You must remove the code to save the table - it would save the design of the table, not the data.

    There is no trace of the SQL string in the code behind your form. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL String (2003)

    Hans ... I changed the "TextBox" controls in the form to include an "input mask" and a "general number" format with no decimals. Then I tried the following two flavors on the "Click" event of "cmdAccept"; the only difference between the two flavors being the statement after the 'strSQL = "INSERT ... ' declaration.

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    Flavor A:
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    If MsgBox("Do you really want to ADD a Case Action for" & vbCrLf & _
    GetPlaceHolderSubjectName & " - CS" & GetPlaceHolderCaseSubject _
    & "/" & GetPlaceHolderAction & "?", vbYesNo + vbCritical, _
    "Verify Before Proceeding!") = vbNo Then
    Exit Sub

    Else

    placeHolderAction = intAddCA
    intCS.SetFocus

    Dim strSQL As String
    strSQL = "INSERT INTO tblActionID (ActionID, SubjectID) VALUES (" & Me.intAddCA & "," & Me.intCS & ");"
    CurrentDb.Execute strSQL, dbFailOnError

    End If

    DoCmd.Close acForm, "frmAddActions"
    DoCmd.OpenForm "frmTasksScreen"

    Exit Sub

    duplicateError:
    MsgBox "Here we go again!"
    Exit Sub
    Resume

    DoCmd.Close acForm, "frmAddActions"
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    Flavor B:
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    If MsgBox("Do you really want to ADD a Case Action for" & vbCrLf & _
    GetPlaceHolderSubjectName & " - CS" & GetPlaceHolderCaseSubject _
    & "/" & GetPlaceHolderAction & "?", vbYesNo + vbCritical, _
    "Verify Before Proceeding!") = vbNo Then
    Exit Sub

    Else

    placeHolderAction = intAddCA
    intCS.SetFocus

    Dim strSQL As String
    strSQL = "INSERT INTO tblActionID (ActionID, SubjectID) VALUES (" & Me.intAddCA & "," & Me.intCS & ");"
    DoCmd.RunSQL strSQL

    End If

    DoCmd.Close acForm, "frmAddActions"
    DoCmd.OpenForm "frmTasksScreen"

    Exit Sub

    duplicateError:
    MsgBox "Here we go again!"
    Exit Sub
    Resume

    DoCmd.Close acForm, "frmAddActions"
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    When I run with Flavor A, it goes to the error handler "duplicateError:" (which name I have to change) and leaves me staring at the form like I did when I saw a Playboy for the first time. When with flavor B, I get the following message ...

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    Microsoft Office Access can't append all the records in the append query. (in Bold letters)

    Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) tothe table due to key violations, 0 record(s) due to lock violations, and 1 record(s) due to validation rule violations.
    Do you want to run the action query anyway?
    To ingnore the error(s) and run the quesry, click Yes.
    For an explanation of the causes of the violations, click help.
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    ... and then leaves me staring at the form like I did when I saw a Playboy for the first time, also.

    You know that I really appreciate your assistance but I'm beginning to feel like a fifth wheel. Your guidance will be appreciated. The best to you and yours!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL String (2003)

    The field Investig has Required set to Yes, so you MUST supply a value, otherwise you won't be able to add a new record. Or set the Required property to No and allow the field to remain blank.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL String (2003)

    You can do the things you're trying to do, but if you design the form to open in adddata mode, the user can't scroll through the records because the form opens to a new record, period. When they click the button to close the form/save the record, you will have added a new record painlessly and without any SQL, but if you are going back to a form that displays all the records, you would need to requery that form to make sure it picked up the newly added record.
    Charlotte

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL String (2003)

    To Charlotte and Hans ... I hope I can learn half as much as you guys know. The little system is finally starting to come together. I don't know if my co-workers will ever use it but I'm having a lot of fun and learning a lot while doing this. Once again, thanks and the best to you!

Posting Permissions

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