Updating a table from a form. SQL not working!

Topic

New Reply

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 .

29600-TitleLog

Reply | Quote

Replies

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:
[INDENT]debug.print strsql[/INDENT]

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.

Reply | Quote

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 & ")"
[COLOR=#ff0000]  DoCmd.RunSQL
[/COLOR]    
  
End Sub

So you are not actually passing the strSQL variable to DoCmd.RunSQL. I suspect that is the cause of your problem.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote