Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL INSERT syntax - inserting from controls in form? 2

Status
Not open for further replies.

cutestuff

Technical User
Sep 7, 2006
162
CA
hi,

I have a quick question.
I have a form where a user selects certain criteria and when they click the 'upload' button I would like it to first save the information selected from the criteria into a table (tblMain). My code for saving the information is below.

stSQL = "INSERT INTO tblMainLog(Project, Company, SystemType, System, Section) SELECT lblProject, txtContractor, cboSystemType, cboSystem, txtSection"

I can't get it to work though. What's the syntax to refer to a form control?
Any suggestions?

Thanks so much in advance.
 
A starting point:
Code:
stSQL = "INSERT INTO yourTable (NumericField, DateField, TextField) " _
 & "VALUES (" & Me![NumericControl] & ", " _
 & "#" & Format(Me![DateControl], "yyyy-mm-dd") & "#, " _
 & "'" & Replace(Me![TextControl], "'", "''") & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
hi PHV,

Thank you so much for your help.
I am trying it (with just one criteria for now as I was getting confused with all the & and " :). However, I am getting an "object doesn't support property or method error. It compiles all right though. I don't know why it's giving me an error.

Thank you...
 
What is your actual code ? which line is highlighted when in debug mode at the time the error raises ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
hi,

I don't know what I did but I tested it again and it's not giving me the error anymore. However, it's not inserting it into the table either. It just does nothing.

stSQL = "INSERT INTO tblMainLog(Section)" & "VALUES (" & Me![txtSection] & " ')"

Can you tell me what I'm doing wrong?

Thank you so much again...
 
I guess that Section is defined as text.
Code:
stSQL = "INSERT INTO tblMainLog(Section) VALUES ('" & Me![txtSection] & "')"
CurrentDb.Execute stSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
It worked!!!

Yay!!!

Thank you SO MUCH!!!
I could give you a hug! :)

Thanks again! You're a GENIUS!
 
hi,

I hate to bother you again,
but can you write the sql statement to include 2 (or more) columns? I keep getting syntax errors when I'm adding fields.

Thanks.
 
My post stamped 11 Apr 07 17:19 showed you an example for 3 columns ...
 
Thank you. I will try it out.
I'm just having problems with the syntax when the VALUES have more than 1 text statement. I think I'm getting confused with all the ' and " . I think all of the fields are text fields too.

 
You are like my co-worker, she also had problems with that.

If you have a very simple Insert statement:
Code:
str = "INSERT INTO MyTable (Field1, Field2, Field3) 
VALUES ('text for field1', 'some other text', 'Another piece of text')"
Single quotes are needed around strings in your SQL.

Now, to replace just one string with, let's say txtText.Text:
Code:
str = "INSERT INTO MyTable (Field1, Field2, Field3) 
VALUES ('[blue][b]" & strText.Text & "[/b][/blue]', 'some other text', 'Another piece of text')"
Double quotes are needed at the end of your str string, then & to concantinate another string to the exsiting one, then txtText.Text, then you need to add ( & ) another string, that's why double quotes, and so on.

Can you see it now? :)

Also, if you can have a single quote in your string, Replace it with 2 single quotes:
Code:
Replace(txtText.text, "'", "''")

You will end up with:

Code:
str = "INSERT INTO MyTable (Field1, Field2, Field3) 
VALUES ('[blue][b]" & Replace(txtText.text, "'", "''") & "[/b][/blue]', 'some other text', 'Another piece of text')"



Have fun.

---- Andy
 
THANK YOU! :)

Now I see all the quotes. :)

You're a lifesaver!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top