SQL INSERT syntax - inserting from controls in form? 2

[cutestuff]

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.

 

[PHV]

A starting point:

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

 

[cutestuff]

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...

 

[PHV]

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

 

[cutestuff]

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...

 

[PHV]

I guess that Section is defined as text.

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

 

[cutestuff]

It worked!!!

Yay!!!

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

Thanks again! You're a GENIUS!

 

[cutestuff]

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.

 

[PHV]

My post stamped 11 Apr 07 17:19 showed you an example for 3 columns ...

 

[Andrzejek]

Yo may also take a look at

http://www.tek-tips.com/viewthread.cfm?qid=1350103&page=1

where I helped PALman. it is VB6 code, but you can use all of that in your Project.

Have fun.

---- Andy

 

[cutestuff]

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.

 

[Andrzejek]

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

If you have a very simple Insert statement:

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:

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:

Replace(txtText.text, "'", "''")

You will end up with:

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

 

[cutestuff]

THANK YOU!

Now I see all the quotes.

You're a lifesaver!