SQL syntax errors and problems

[Lumpy]

I am a complete noob with Access, and have been struggling along for a while and now reached a point where I am just stuck. I had made a custom form where some of it populates through other SQL queries and some of the information gets entered in by hand/keyboard. Now I am at the point where I want to save the data in the form to a table when the user clicks the button and I am having some syntax errors.

The first way I tried is like this...

Private Sub savequote_Click()
 
SQLTEXTINFO = "INSERT INTO Quote_Info (QuoteNumber, thisdate, CustomerPO, SalesPersonName, Discount, DealerNumber, Dealer, DealerAddress, DealerCity, DealerState, DealerZip, ContactName, ContactPhone, ContactFax, ContactEmail, TotalSkids, TotalWeight, Tozip, QuoteTotal, FreightCost, YFClass, YFZip, YFweight) VALUES Me.Quote_number, Me.Quote_date, Me.Dealer_po, Me.Quote.salesperson, Me.Dealer_pricing, Me.Dealer_id, Me.Dealer_name, Me.Dealer_address, Me.Dealer_city, Me.Dealer_state, Me.Dealer_zip, Me.Dealer_contactname, Me.Dealer_contactphone, Me.Dealer_contactfax, Me.Dealer_contactemail, Me.Total_skids, Me.Total_weight, Me.Zip_ship, Me.Total_cartcost, Me.Freight_cost, Me.YF_class, Me.YF_zip, Me.YF_weight;"
 
DoCmd.RunSQL SQLTEXTINFO
 
End Sub
 

And attempting to run the above code gives a syntax error. I made a slight change to the code, by putting ( ) around the VALUES like the following and a little pop up box will begin to ask me for all the values for every item listed in the VALUES (...) part of the code.

Private Sub savequote_Click()
 
SQLTEXTINFO = "INSERT INTO Quote_Info (QuoteNumber, thisdate, CustomerPO, SalesPersonName, Discount, DealerNumber, Dealer, DealerAddress, DealerCity, DealerState, DealerZip, ContactName, ContactPhone, ContactFax, ContactEmail, TotalSkids, TotalWeight, Tozip, QuoteTotal, FreightCost, YFClass, YFZip, YFweight) VALUES (Me.Quote_number, Me.Quote_date, Me.Dealer_po, Me.Quote.salesperson, Me.Dealer_pricing, Me.Dealer_id, Me.Dealer_name, Me.Dealer_address, Me.Dealer_city, Me.Dealer_state, Me.Dealer_zip, Me.Dealer_contactname, Me.Dealer_contactphone, Me.Dealer_contactfax, Me.Dealer_contactemail, Me.Total_skids, Me.Total_weight, Me.Zip_ship, Me.Total_cartcost, Me.Freight_cost, Me.YF_class, Me.YF_zip, Me.YF_weight);"
 
DoCmd.RunSQL SQLTEXTINFO
 
End Sub
 

So I am at a little lost on how to handle this situation. I don't even know if this is the best method to insert new data into the table, or if I am able to use Me.name variables in this manner even though all the Me.name variables all get assigned a value earlier in the form. Any help or direction would be greatly appreciated.

Thank You.

[Jim Doherty]

I am a complete noob with Access, and have been struggling along for a while and now reached a point where I am just stuck. I had made a custom form where some of it populates through other SQL queries and some of the information gets entered in by hand/keyboard. Now I am at the point where I want to save the data in the form to a table when the user clicks the button and I am having some syntax errors.

The first way I tried is like this...

Private Sub savequote_Click()
 
SQLTEXTINFO = "INSERT INTO Quote_Info (QuoteNumber, thisdate, CustomerPO, SalesPersonName, Discount, DealerNumber, Dealer, DealerAddress, DealerCity, DealerState, DealerZip, ContactName, ContactPhone, ContactFax, ContactEmail, TotalSkids, TotalWeight, Tozip, QuoteTotal, FreightCost, YFClass, YFZip, YFweight) VALUES Me.Quote_number, Me.Quote_date, Me.Dealer_po, Me.Quote.salesperson, Me.Dealer_pricing, Me.Dealer_id, Me.Dealer_name, Me.Dealer_address, Me.Dealer_city, Me.Dealer_state, Me.Dealer_zip, Me.Dealer_contactname, Me.Dealer_contactphone, Me.Dealer_contactfax, Me.Dealer_contactemail, Me.Total_skids, Me.Total_weight, Me.Zip_ship, Me.Total_cartcost, Me.Freight_cost, Me.YF_class, Me.YF_zip, Me.YF_weight;"
 
DoCmd.RunSQL SQLTEXTINFO
 
End Sub
 

And attempting to run the above code gives a syntax error. I made a slight change to the code, by putting ( ) around the VALUES like the following and a little pop up box will begin to ask me for all the values for every item listed in the VALUES (...) part of the code.

Private Sub savequote_Click()
 
SQLTEXTINFO = "INSERT INTO Quote_Info (QuoteNumber, thisdate, CustomerPO, SalesPersonName, Discount, DealerNumber, Dealer, DealerAddress, DealerCity, DealerState, DealerZip, ContactName, ContactPhone, ContactFax, ContactEmail, TotalSkids, TotalWeight, Tozip, QuoteTotal, FreightCost, YFClass, YFZip, YFweight) VALUES (Me.Quote_number, Me.Quote_date, Me.Dealer_po, Me.Quote.salesperson, Me.Dealer_pricing, Me.Dealer_id, Me.Dealer_name, Me.Dealer_address, Me.Dealer_city, Me.Dealer_state, Me.Dealer_zip, Me.Dealer_contactname, Me.Dealer_contactphone, Me.Dealer_contactfax, Me.Dealer_contactemail, Me.Total_skids, Me.Total_weight, Me.Zip_ship, Me.Total_cartcost, Me.Freight_cost, Me.YF_class, Me.YF_zip, Me.YF_weight);"
 
DoCmd.RunSQL SQLTEXTINFO
 
End Sub
 

So I am at a little lost on how to handle this situation. I don't even know if this is the best method to insert new data into the table, or if I am able to use Me.name variables in this manner even though all the Me.name variables all get assigned a value earlier in the form. Any help or direction would be greatly appreciated.

Thank You.

Hi Lumpy,

Given you are a newbie you have at least discovered the VBA window then :)

You are on the right track just needs tweaking thats all.

There are two methods for entering data into the table one is via the .AddNew method of an Recordset object there are lots or references on how to do that using that way on here so I won't reinvent the wheel on that one you can quite easily research that on here.

Your post uses the DoCmd.RunSQL command to do what that command is designed to do within Access, namely to run an 'Action' query using the SQL language provided by you and taken as part of its argument. In so providing that SQL you provide a 'string' of characters on which the command bases itself.

So what is the string? the string is a continuous sequence of characters taken together to form the argument. Somewhat obvious you might think, but not to some of the people some of the time.

In your case you need to build that string from a combination of literal characters and values provided by the contents of screen controls. The way you have it at the moment is NOT doing that it is saying this:

"I want to insert the string description 'Me.Quote_number' into the quote number field of the table" understand the difference? you are NOT saying take the value from this control and pass that value to the string.

So in building the string for the insert SQL as you have it there, you need to amend and build it along these lines (I break it into parts for reasons of clarity and demonstrating how you string build hopefully rather than anything else)

 Dim MyInsert AS String 
Dim MyValues as String
 
MyInsert ="INSERT INTO Quote_Info (QuoteNumber, thisdate, CustomerPO, SalesPersonName, Discount, DealerNumber, Dealer, DealerAddress, DealerCity, DealerState, DealerZip, ContactName, ContactPhone, ContactFax, ContactEmail, TotalSkids, TotalWeight, Tozip, QuoteTotal, FreightCost, YFClass, YFZip, YFweight) "
 

 MyValues= "VALUES (" 
 
MyValues = MyValues & " ' " & Me!QuoteNumber &" ', "
MyValues = MyValues & " # " & Me!QuoteDate &" #, " 
MyValues = MyValues & " ' " & Me!Dealer_po &" ', "
 

You keep on doing this till you exhaust your values list as it applies to your list of insert fields. What are these bits either side of my control names? I hear you ask. They are delimiting characters that are telling the SQL what the data type is that is being passed # denotes a date value for instance.

Now the next effect of this is that the string variable MyValues is increasing in length each time you append unto itself so that when you get to the DoCmd.RunSQL command the actual string you are passing to the command is a joined combination of the variables MyInsert and MyValues.......... like this:

 DoCmd.RunSQL MyInsert & MyValues

That is the fundamental idea basically! If you want to see the value of these variables 'from your screen' instead of running the command then place and unbound textbox on your screen and call it txtMySQL then place this command

Me!txtMySQL=MyInsert & MyValues

immediately before the DoCmd.RunSQL command line like so and comment out the DoCmd command:

 Me!txtMySQL=MyInsert & MyValues 
'DoCmd.RunSQL MyInsert & MyValues
 

Incidentally it would be useful for you to research the difference on here between the BANG and DOT operators on here ( ie: Me! and Me. )for your own information and to understand the difference

Hope this helps you

Regards

Jim :)

[Lumpy]

Thank You Jim for all the information. That was exactly the direction I was looking for. It helped me to not only get my INSERT statement working but also provided me with additional insight on how VBA works. Thanks again!

[Jim Doherty]

Thank You Jim for all the information. That was exactly the direction I was looking for. It helped me to not only get my INSERT statement working but also provided me with additional insight on how VBA works. Thanks again!

You're very welcome I'm glad it helped you

Jim :)

[NeoPa]

This is all good learning Lumpy, but the way Access is designed to allow you to add data into tables from a form is via a bound form with bound controls. It is possible (as you've found out) to add data from a form programatically and using SQL, but this simply requires a lot of programming to replace what Access is already designed to do for you.