Insert Into .... Where .....

[onlylonely]

G'day All,

I'm facing with SQL code that not allowed me to insert value into table.

CurrentDb.Execute "INSERT INTO IssuesTbl (Complete) Where PartNumber = " & Me.PartNumber & " AND IssueID = " & Me.cmbID & "" & _
"VALUES('CLOSE')"

CloseSQL = "SELECT Complete FROM IssuesTbl Where PartNumber ='" & Me.PartNumber & "' AND ID = '" & Me.cmbID & "'"


CurrentDb.Execute "INSERT INTO CloseSQL(Complete)" & _
"VALUES('Close')"

Your advice is highly appreciated.

ID	PartNumber	Issues	Link	Complete
3	123	Good year	123

[alansidman]

Look at this tutorial to ensure that your syntax is correct.

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

[onlylonely]

Look at this tutorial to ensure that your syntax is correct.

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

Hi Alansidman,

Thank for your reply. Ya, i know the syntax is wrong. But i can't find a syntax which using Insert Into .... Where...
usually they use insert into ...select ....

CurrentDb.Execute "INSERT INTO IssuesTbl(Complete)" & _
"VALUES('CLOSE')" & _
"Where [IssuesTbl].[PartNumber] = " & Me.PartNumber & " And [IssuesTbl].[ID] = " & Me.cmbID & ""

I've tried this code but still not able to ADD the "CLOSE" into Complete column. Any advice?

[aytee111]

Get the query working first in design view, then copy and paste the SQL.

[CJ_London]

You also don't appear to have a field called 'IssueID'.

if using currentdb.execute, also add dbfailonerror as the second parameter - that way you will get the error reported as if running the query directly.

Also worthwhile building the sgl string as per your closesql, then debug.print it. You can then see if it makes sense and can copy and paste from the immediate window into the query sql window and try to run it.

[onlylonely]

You also don't appear to have a field called 'IssueID'.

if using currentdb.execute, also add dbfailonerror as the second parameter - that way you will get the error reported as if running the query directly.

Also worthwhile building the sgl string as per your closesql, then debug.print it. You can then see if it makes sense and can copy and paste from the immediate window into the query sql window and try to run it.

Hi Ajax,

Thanks for your reply. I'm only able to understand 50percents of your comment.
It's too profound to me. You are right, i notice that I've a typo on "IssuesID"
My table name has no bracket, may i know why do you think that i've a bracket for my table name?

[CJ_London]

I edited my post to correct - it looked like there was no space between IssuesTbl and (Complete)

I'm suggesting your code should be something like

dim sqlstr as string

sqlstr="INSERT.....
debug.print sqlstr 'see immediate window for the calculated string, copy and paste in the sql window into the query builder
currentdb.execute sqlstr, dbfailonerror

[onlylonely]

I edited my post to correct - it looked like there was no space between IssuesTbl and (Complete)

I'm suggesting your code should be something like

dim sqlstr as string

sqlstr="INSERT.....
debug.print sqlstr 'see immediate window for the calculated string, copy and paste in the sql window into the query builder
currentdb.execute sqlstr, dbfailonerror

Hi AJax,

Thank You for your reply. I think i've figured out something new. Which is using

Function Update()
Dim Complete As String


Update IssuesTbl
Set Complete = "A"
WHERE PartNumber = " & Me.PartNumber & "


End Function

But it pops out "Object required". Any advice?

[rpeare]

Function Update()

Dim sSQL As String

'update statement where part number is a numeric field (i.e. the primary key of the 'parts' table)
'sSQL = "Update IssuesTbl Set Complete = 'A' WHERE PartNumber = " & Me.PartNumber 
'update statement where part number is a text field (i.e. the primary key of the 'parts' table)
'sSQL = "Update IssuesTbl Set Complete = 'A' WHERE PartNumber = '" & Me.PartNumber  & "'"

currentdb.execute(Complete)

End Function

uncomment the sql statement builder that applies to you

[onlylonely]

Function Update()

Dim sSQL As String

'update statement where part number is a numeric field (i.e. the primary key of the 'parts' table)
'sSQL = "Update IssuesTbl Set Complete = 'A' WHERE PartNumber = " & Me.PartNumber 
'update statement where part number is a text field (i.e. the primary key of the 'parts' table)
'sSQL = "Update IssuesTbl Set Complete = 'A' WHERE PartNumber = '" & Me.PartNumber  & "'"

currentdb.execute(Complete)

End Function

uncomment the sql statement builder that applies to you

Hi Rpeare,

Thank You for your reply. But I'm still getting error when i using the code.
I've checked multiple times, all the location and names are correct.

[rpeare]

can't tell from what you posted. If you modified the code I gave you I'd need to see that, you still have everything commented out so there's no way of telling what you're doing or what you changed, nor can I see the event procedure that's calling the update, nor can I see whether or not (still) the partnumber is text or number, I think it make be text just because of it's alignment but I don't really know.

Finally I made an error in my initial post

it should be currentdb.execute(ssql)