Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating a table from a form. SQL not working!

    SO, I am on the uphill side of this learning curve, and its hard going. I have a simple db, that askes the user to populate a form, and then, when he's convinced its the data he needs, he clicks the submit button. So, I used forums and tutorials, and I have a bit of code... but it glitches every time, syntax error, and the "can't find macro" error which is really confusing because I am not using a macro. Here is the code I am trying to use in the ON-CLICK event Procedure of the submit button.

    Private Sub Command49_Click()

    Dim strSQL As String
    strSQL = _
    "INSERT INTO tblTheGoods " & _
    "(Record Number, [Stock Number], [Color], Year, [Make], " & _
    "[Model], Date, Car Cost, [Bought From], Vin last6, " & _
    "[Period], [Buyer], Trans_Cost, Buyer Fee, Total Avg)" & _
    " VALUES (" & _
    Me.tboRecordNumber & ", " & _
    "'" & Me.tboStockNumber & "', " & _
    "'" & Me.cboColor & "', " & _
    Me.cboYear & ", " & _
    "'" & Me.cboCarMake & "', " & _
    "'" & Me.cboCarModel & "', " & _
    Format(Me.tboTodaydate, "\#mm\/dd\/yyyy\#") & ", " & _
    Me.tboCarCost & ", " & _
    "'" & Me.cboSeller & "', " & _
    "'" & tboVin & "', " & _
    "'" & Me.cboPeriod & "', " & _
    "'" & Me.cboBuyer & "', " & _
    Me.tboTransportationCost & ", " & _
    Me.cboBuyerFee & ", " & _
    Me.tboTotalAverage & ")"

    CurrentDb.Execute strSQL, dbFailOnError

    End Sub
    ---------------------------------------------------------
    I see I can attatch files here.. Here is the program. Any guidance would be appreciated. Thank you .

    TitleLog.zip
    Last edited by Karl1971; 2011-12-12 at 12:08.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Just out of curiosity, why didn't you use a bound form?

    Also, you should get in the habit of renaming your controls. If you had to refer to Command49 at some other place in your code, do you thing you'd remember what it is? Maybe today, but not 6 months from now.

    As for your problem, stick this line of code before your Execute statement:
    debug.print strsql

    You can then look at the contents of strsql in your immediate window, and even copy it to a query to see what error you get when you try to run it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    This is the actual code you have in the form:
    Code:
    Private Sub Command49_Click()
    Dim strSQL As String
        strSQL = _
            "INSERT INTO tblTheGoods " & _
                "(Record Number, [Stock Number], [Color], Year, [Make], " & _
                "[Model], Date, Car Cost, [Bought From], Vin last6, " & _
                "[Period], [Buyer], Trans_Cost, Buyer Fee, Total Avg, Miles)" & _
            " VALUES (" & _
                Me.tboRecordNumber & ", " & _
                "'" & Me.tboStockNumber & "', " & _
                "'" & Me.cbocolor & "', " & _
                Me.cboYear & ", " & _
                "'" & Me.cboCarMake & "', " & _
                "'" & Me.cboCarModel & "', " & _
                Format(Me.tboTodayDate, "\#mm\/dd\/yyyy\#") & ", " & _
                Me.tboCarCost & ", " & _
                "'" & Me.cboSeller & "', " & _
                "'" & tboVin & "', " & _
                "'" & Me.cboPeriod & "', " & _
                "'" & Me.cbobuyer & "', " & _
                Me.tboTransportationCost & ", " & _
                Me.cboBuyerFee & ", " & _
                Me.tboTotalAverage & ")"
      DoCmd.RunSQL
        
      
    End Sub
    So you are not actually passing the strSQL variable to DoCmd.RunSQL. I suspect that is the cause of your problem.

  4. #4
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, if I understand correctly, you say why use an unbound form? I started with the idea that the table was going to be dependent on the form not the other way around. I tried to make the table, then use a bound form, but I had issues making some other SQL in the program work. Namely the case statements in the car make picker. I think I should go that route again. i am going to try to make this code work... but failing that, I am going to make a new form based on the table that I named tblTheGoods.
    Thanks for your help Sir.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In addition to ruirib's note you need to put brackets around the field names in your Insert clause for all the fields that have spaces within the names, e.g., [Record Number], [Car Cost], etc. You have brackets around some of the fields but not all the multi-word field names.
    But I would agree with Mark's recommendation of using a bound form, which would eliminate the need for the SQL altogether.

  6. #6
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was under the impression that the brackets were used to denote text vs numbers as input! Ok, noted. I intend to split this database as to have multiple users in at once. I thought you couldnt use bound forms in that type of situation. After I read your posts.. I reconstructed the db using a bound form, and thats ok.. except I dont want to give the user any way to manually manipulate the table. I want the form to be the only interface... I have a subform now, that shows the table, and I have locked it.. so they can't change it in the subform view.. I will continue down this path.. in hopes that I can effectively split the DB later. Thanks for all your help, and if anybody has anything else.. I would love to hear it.

  7. #7
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For what it's worth, I will post the working product.. well, it works, but its all text values atm.




    Private Sub MethodSubmitForm_Click()
    Dim strSQL As String
    strSQL = _
    "INSERT INTO tblTheGoods " & _
    "(Date_Entered, Stock_num, VIN_num, Car_Make, Car_Model, Car_Color, Car_Seller, Buyer, Buyer_Fee, Period, Trans_Cost, Miles, Car_Cost, Car_Year)" & _
    " VALUES (" & _
    "'" & Me.tboDate & "', " & _
    "'" & Me.tboStockNum & "', " & _
    "'" & Me.tboVIN & "', " & _
    "'" & Me.cboCarMake & "', " & _
    "'" & Me.cboCarModel & "', " & _
    "'" & Me.cboColor & "', " & _
    "'" & Me.cboSeller & "', " & _
    "'" & Me.cboBuyer & "', " & _
    "'" & Me.cboBuyerFee & "', " & _
    "'" & Me.cboPeriod & "', " & _
    "'" & Me.tboTransCost & "', " & _
    "'" & Me.tboMiles & "', " & _
    "'" & Me.tboCarCost & "', " & _
    "'" & Me.cboYear & "')"


    CurrentDb.Execute strSQL, dbFailOnError
    End Sub

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    If you plan to use a distributed architecture, it may work with Access alone, though probably SQL Server would be better as a backend. In such a scenario, bound forms can be problematic.
    Splitting a DB app in a front end and back end doesn't implicate the need to use unbound forms. The distribution - front end and back end located in different machines on a network - usually will mean a lot more care, to minimize network traffic and thus improve app performance, which means that unbounded forms may be better in some circumstances.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Karl1971 View Post
    I was under the impression that the brackets were used to denote text vs numbers as input! Ok, noted. I intend to split this database as to have multiple users in at once. I thought you couldnt use bound forms in that type of situation. After I read your posts.. I reconstructed the db using a bound form, and thats ok.. except I dont want to give the user any way to manually manipulate the table. I want the form to be the only interface... I have a subform now, that shows the table, and I have locked it.. so they can't change it in the subform view.. I will continue down this path.. in hopes that I can effectively split the DB later. Thanks for all your help, and if anybody has anything else.. I would love to hear it.
    Splitting the database does not prevent you from using bound forms! And using a bound form doesn't give the user any special way to manually manipulate the tables other than what you allow on the form. There are other means to prevent this, like restricting access to the database window, using a .mde (or accde) file to restrict access to code, etc.

    Also, using SQL server as a backend doesn't prevent you from using bound forms either. Access provides a mechanism to link to a backend; the backend can be another Access database, SQL server, or other databases. The problem is usually not the backend itself, but where it is located. Using linked tables and bound forms against a remote backend can cause problems. But if the backend is on the same LAN, then no problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Splitting the database does not prevent you from using bound forms! And using a bound form doesn't give the user any special way to manually manipulate the tables other than what you allow on the form. There are other means to prevent this, like restricting access to the database window, using a .mde (or accde) file to restrict access to code, etc.

    Also, using SQL server as a backend doesn't prevent you from using bound forms either. Access provides a mechanism to link to a backend; the backend can be another Access database, SQL server, or other databases. The problem is usually not the backend itself, but where it is located. Using linked tables and bound forms against a remote backend can cause problems. But if the backend is on the same LAN, then no problem.

    Thanks for that response. I actually have come quite a bit further along on this DB. got my unbound form working, a couple bound subforms working, some functionality issues in place.. now, I am going to try to split it(my first attempt ever) and see what breaks. Thanks again for the insight.

  11. #11
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Karl1971,

    I just noticed this posting. It was mentioned in the lastest issue of Windows Secrets.

    Brackets are required when you include spaces or other special characters in the names of fields, tables and controls. They are also often-times needed when you use reserved words as well. My advice is to avoid the need altogether, by using proper naming conventions. Here are some links that provide helpful information:

    Special characters that you must avoid when you work with Access databases
    http://support.microsoft.com/?id=826763

    Problem names and reserved words in Access
    http://allenbrowne.com/AppIssueBadWord.html

    My copy of "AccessLinks.doc", a Word document with a collection of links and helpful tips:
    http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  12. #12
    New Lounger
    Join Date
    Dec 2011
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This is just the kind of help I need

    Thanks for the explanation and links... copying code or regurgitating by rote is inferior to actually understanding whats going on. Man, there is a lot to learn about this. Something you could spend the next 10 years perfecting. So here I am yr .5, and thanks to you guys, I am cutting my teeth. I appreciate it.


    Karl

Posting Permissions

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