Insert statement not working

[slcollie]

I am not too good with writing code but I researched INSERTS and cant understand why this code does not work. It seems to be unhappy about my table TbL_IF but I have no idea why. Any ideas


Private Sub Status_AfterUpdate()
If Status = 4 Then
INSERT INTO Tbl_IF(ready)
VALUES (True)
Else If Status = 15 Then
INSERT INTO Tbl_viewing(ready)
VALUES (True)
Else: End If
End Sub

 

[pbaldy]

You can't have SQL like that. You can put it in a string and execute it:

CurrentDb.Execute "INSERT INTO..."

 

[slcollie]

Sorry im not too good at SQL. Its just an MS Access 2010 database and the layout above is what all the websites say to do. I tried to follow your instructions but it keep putting an extra " after the first line.



Private Sub Status_AfterUpdate()
If Status = 4 Then
CurrentDb.Execute "INSERT INTO Tbl_IF(ready)"
VALUES (True)
Else If Status = 15 Then
INSERT INTO Tbl_viewing(ready)
VALUES (True)
Else: End If
End Sub

 

[pbaldy]

You want the entire SQL statement inside the quotes. As short as it is, put it all on one line.

 

[toast]

Private Sub Status_AfterUpdate()

Dim strSQL as String

If Status = 4 then
	strSQL = "INSERT INTO Tbl_IF (ready) VALUES (true);"
Elseif Status = 15 then
	strSQL = "INSERT INTO Tbl_viewing (ready) VALUES (true);"
End if

If strSQL <> "" then currentdb.Execute(strSQL)

End Sub

You probably want some error handling in there.
If you're going to start adding more "ifs" you might want to consider using a SELECT CASE rather than If...Then...Else

 

[pbaldy]

You dont need to disable warnings with the execute method, and it's more efficient anyway.

 

[jdraw]

slcollie,

There are some tutorials with vba and SQL at Martin Green's site:
http://www.fontstuff.com/access/index.htm

 

[slcollie]

The code priovided works great for me and I will look at tutorials too - many thanks for the help