Solved Problems inserting records into a table using SQL : maybe an error in my concatenation ?

[alan2013]

Could someone help me pinpoint where I'm going wrong with the following, please ?

I'm attempting to insert a record into a Table named tblTasks, using the VBA code below.

Dim strTaskInsertionSQL As String
Dim strTaskTitle As String

strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"
strTaskTitle = "Task 1"

DoCmd.RunSQL strTaskInsertionSQL

However, it's not working. I get the following message, and when I click on 'Yes', no record is inserted.

If instead I use the following code, it DOES work.

Dim strTaskInsertionSQL As String
Dim strTaskTitle As String

strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"
strTaskTitle = "Task 1"

DoCmd.RunSQL "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"

As I want to be able to expand on this and introduce other 'Task titles', I'd really like to be able to use strTaskInsertionSQL.

Why is the first block of code not picking up the value for strTaskTitle ? Perhaps an issue with the cancatenation of strTaskInsertionSQL ?

Thanks in advance for any pointers.

 

[ebs17]

strTaskTitle = "Task 1"
strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"

Swap the order. The variable should first contain the correct value before you use it.

 

[Gasman]

Debug.print your sql string.
That should show you your errors.

 

[GPGeorge]

Could someone help me pinpoint where I'm going wrong with the following, please ?

I'm attempting to insert a record into a Table named tblTasks, using the VBA code below.

Dim strTaskInsertionSQL As String
Dim strTaskTitle As String

strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"
strTaskTitle = "Task 1"

DoCmd.RunSQL strTaskInsertionSQL

However, it's not working. I get the following message, and when I click on 'Yes', no record is inserted.

View attachment 112976




If instead I use the following code, it DOES work.

Dim strTaskInsertionSQL As String
Dim strTaskTitle As String

strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"
strTaskTitle = "Task 1"

DoCmd.RunSQL "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"

As I want to be able to expand on this and introduce other 'Task titles', I'd really like to be able to use strTaskInsertionSQL.

Why is the first block of code not picking up the value for strTaskTitle ? Perhaps an issue with the cancatenation of strTaskInsertionSQL ?

Thanks in advance for any pointers.

Is TaskCurrent a boolean (yes/no) field or a short text field?

 

[alan2013]

Is TaskCurrent a boolean (yes/no) field or a short text field?

It's a Short Text field, in which I enter Yes or No. Thanks for your interest. In any case, my error seems to be in the order of my lines of code, as pointed out by ebs17.

 

[alan2013]

strTaskTitle = "Task 1"
strTaskInsertionSQL = "INSERT INTO tblTasks (TaskTitle, TaskCurrent, Status) VALUES ('" & strTaskTitle & "', 'Yes', 'A');"

Swap the order. The variable should first contain the correct value before you use it.

Thanks, ebs17. I've taken that on board, and altered by code to take that into account.