Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Unhappy Unanswered: little problem w/ INsert query and DoCmd****nSQL

    My problem is that apparently someone syphoned all the programming knowledge out of my brain while I slept. I don't know where I forgot how to insert data into a table. Maybe it's just a syntax problem.

    I've got my patients table:
    tblPatients
    - intDOCnum
    - strLName
    - strFName
    - intSite

    THen I've got this form that I Just called "EnterStuff" just...for the sake of calling it something. The Module for that form goes like this:

    Code:
    Private Sub btnEnterPatient_Click()
        Dim GoOn As Boolean
        Dim strSQL As String
        Dim db As Database
        Dim rst As Recordset
        
        GoOn = False
        GoOn = CheckMissing()
        If GoOn Then Exit Sub
        
        DOCnum = Me.txtDOCnum
        Site = Me.cmbSite
        LName = Me.txtLName
        FName = Me.txtFName
        
        strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
        strSQL = strSQL + "VALUES (" & DOCnum & ", " & LName & ", " & FName & ", " & Site & ")"
      
        DoCmd****nSQL (strSQL)
    (I don't know why, but this message board is inserting **** in the code for some reason... O_o That's supposed to be DoCmd DOT RunSQL. It doesn't appear like that in my VB window.)

    SO anyway, when I click the enter patient button, It starts throwing prompts at me. What's messed up is that the text in the prompt popups are the values from my form's variables.

    someone help me...my migraine is coming back.
    Last edited by atsukoarai86; 07-08-09 at 12:35.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    May 2009
    Posts
    258
    I'm thinking your problem–or part of it–lies in here:
    Code:
    strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
        strSQL = strSQL + "VALUES (" & DOCnum & ", " & LName & ", " & FName & ", " & Site & ")"
    For a DOCnum of 12345, an LName of "Doe", an FName of "John", and a Site of 999, it would produce the following SQL statement:
    Code:
    INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)VALUES (12345, Doe, John, 999)
    Do you see anything wrong with the above statement?

    Ax

  3. #3
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question

    yup yup... I replace it with

    Code:
     'DoCmd.SetWarnings False
        'strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
        'strSQL = strSQL + "VALUES ('" & DOCnum & "', '" & LName & "', '" & FName & "', '" & Site & "')"
      
        'RunSQL strSQL, dbFailOnError
    And now that works just fine. Now I'm experimenting with another method and it's just... really really chapping my hide because I can't figure out how to get it to work.

    ORIGINALLY, I was gonna try to run the append query that I wrote:
    Code:
    INSERT INTO tblPatients ( intDOCnum, strLName, strFName, intSite )
    VALUES([Forms]![EnterStuff]![txtDOCnum], [Forms]![EnterStuff]![txtLName], [Forms]![EnterStuff]![txtFName], [Forms]![EnterStuff]![cmbSite]);
    Using the code:
    Code:
        Dim stDocName As String
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
    
        stDocName = "qryAddPatient"
    
        Set db = CurrentDb
        Set qdf = db.QueryDefs(stDocName)
    
        qdf.Execute , dbFailOnError
    But I get an error titled "Microsoft Visual Basic" that says
    Compile Error:

    Wrong number of arguments or invalid property assignment.
    Normally I'd just go with what works, and just hard-code the SQL query into the form's module but... It irritates me to no end when I can't get something to work. Maybe we can shed a little light on this.
    Quote Originally Posted by Ax238
    I'm thinking your problem–or part of it–lies in here:
    Code:
    strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
        strSQL = strSQL + "VALUES (" & DOCnum & ", " & LName & ", " & FName & ", " & Site & ")"
    For a DOCnum of 12345, an LName of "Doe", an FName of "John", and a Site of 999, it would produce the following SQL statement:
    Code:
    INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)VALUES (12345, Doe, John, 999)
    Do you see anything wrong with the above statement?

    Ax
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  4. #4
    Join Date
    May 2009
    Posts
    258
    You can just use DoCmd.OpenQuery "qryAddPatient"

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think your issue may result from using + instead of & while appending your "VALUES" clause.

    strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
    strSQL = strSQL & "VALUES (" & DOCnum & ", " & LName & ", " & FName & ", " & Site & ")"

    Also, are you SURE you're getting that output? Have you set a breakpoint and used the intermediate window to double check the assumption?

    also, also... you're missing quotes around your strings.
    oh yeah... documentation... I have heard of that.

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

  6. #6
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by Ax238
    You can just use DoCmd.OpenQuery "qryAddPatient"
    Yeah, I have tried that, and all it does is open up the query with all the parameter names as the column headings. Not what I'm needing it to do. <_<
    Attached Thumbnails Attached Thumbnails problem.JPG  
    Last edited by atsukoarai86; 07-09-09 at 13:26. Reason: missing..something...
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  7. #7
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by Teddy
    I think your issue may result from using + instead of & while appending your "VALUES" clause.

    strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
    strSQL = strSQL & "VALUES (" & DOCnum & ", " & LName & ", " & FName & ", " & Site & ")"

    Also, are you SURE you're getting that output? Have you set a breakpoint and used the intermediate window to double check the assumption?

    also, also... you're missing quotes around your strings.
    Using + instead of & has never presented a problem; it assigns the the string as it's supposed to.

    Code:
    strSQL = "INSERT INTO tblPatients (intDOCnum, strLName, strFName, intSite)"
    strSQL = strSQL + "VALUES ('" & DOCnum & "', '" & LName & "', '" & FName & "', '" & Site & "')"
    This piece of code works just fine, I don't see that it's missing any quotes.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  8. #8
    Join Date
    May 2009
    Posts
    258
    Did you specify acAdd on the DoCmd.OpenQuery?

  9. #9
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Thumbs up hmmmm

    Quote Originally Posted by Ax238
    Did you specify acAdd on the DoCmd.OpenQuery?
    hmmmm... no, I'll go see if that works.

    30 seconds later...

    Hey that worked! Huzzah thanks very much for the assistance.

    I've decided as far as using the QueryDefs .Execute method goes, I'll just have to do a little more research on that. It's convenient to know 2 ways to do something, but I always like to know all my options.

    Thanks again!
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  10. #10
    Join Date
    May 2009
    Posts
    258
    Excellent! Glad you were able to get it working.

Posting Permissions

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