Insert into with a where clause? Possible

[ddikeht]

I am just trying something easy, but running into an issue here is the code

Dim SQL_Text as String
          SQL_Text = "Insert Into Metrics (State) Values ('"& me.State & "')"
          SQL_Text = SQL_Text & "Where (Me.Checkbox ='" & True & "');"

Basically, I am just trying to insert a field from a form, if the checkbox next to it is checked. I am using a query off a table to populate the form, and inserting it into another table called Metrics.

What the hay am I doing wrong here?

 

[vbaInet]

You need a space before WHERE, i.e. " Where, or you can put it after the last cloging brace, i.e. "' "

 

[ddikeht]

VbaInet - Thanks for the response. I tried it both ways, and I am still getting an error. Really cannot figure out why as it is so simple. Here is the modified code.

Dim SQL_Text As String

SQL_Text = "Insert Into Metrics (State) Values ('" & Me.State & "')"

SQL_Text = SQL_Text & " Where (Me.Checkbox = '" & True & "');"

I added the space as proposed, but still get "Missing Semicolon at the end of SQL statement error. Putting the space after the brace gives a syntax error. Any other tips?

 

[vbaInet]

Forgot to mention you're using the WHERE clause in the wrong context. That whole line is syntactically wrong. Here's a link re the syntax:

http://www.techonthenet.com/sql/insert.php

 

[ddikeht]

VBA - Thanks again. Read the link and I am pretty good at SQL, but really suck at formatting it in Access. Is there are easy way to write this? I tried in Access, but I think I am worse off than before. Any help you could give (or anyone else) would be greatly appreciated.

 

[vbaInet]

Well, there's never a time in sql where you use a WHERE clause in an INSERT INTO statement unless it was in a subquery.

The WHERE clause shouldn't be there at all. Unless you want an UPDATE statement.

 

[ddikeht]

Ok, so back to the original question. How would I write the insert statement to only insert when there is a checkbox checked on the form? I have a continuous form that has editable fields. I am only wanting to perform an insert on the records that have a checkbox next to them. How would you do this?

 

[vbaInet]

Here you go:

SQL_Text = "Insert Into Metrics (State) Values ('" & Me.State & "') " & _
           "Where ([FieldName] = '" & True & "');"

Substitute the field name the checkbox is bound to in there.

 

[ddikeht]

VBA - Thanks for all of the time you are putting into this. You are really nice. I put in the code

SQL_Text = "Insert Into Metrics (State) Values ('" & Me.State & "') " & "Where ([Checkbox] = '" & True & "');"

And it gives me the same error, which is missing semicolon at end of SQL statement. The complete code is;

Private Sub Command13_Click()

Dim SQL_Text As String

SQL_Text = "Insert Into Metrics (State) Values ('" & Me.State & "') " & "Where ([Checkbox] = '" & True & "');"

DoCmd.RunSQL SQL_Text

End Sub

So, Still in the state of Confusion. I know this should not be as hard as I am making it.

 

[vbaInet]

It's quite late here so I'm allowed to miss things

Here it is:

SQL_Text = "Insert Into Metrics (State) Values ('" & Me.State & "') " & _
           "Where ([FieldName] = True);"

 

[ddikeht]

VBA-

No love, still the same "missing semicolon error" semicolon is there, GRRRR

 

[vbaInet]

I actually made the same mistake. I think I need to sleep.

What you need is to use a subquery. So:

SQL_Text = "Insert Into Metrics (State) " & _
                "SELECT State FROM TableName WHERE FieldName = True;"

When a user ticks a checkbox and moves out of that record, it gets saved so you can refer directly to the table.

 

[ddikeht]

VBA -

You hit a home run. It runs without an error on my button. I imagine I need to put this in one of the form events? Which do you recommend so it runs for each individual record? THANK YOU SO MUCH

 

[vbaInet]

If you're using a button for this, it's more than sufficient. But place it on the Header or Footer section of your form.