Insert into with a WHERE statement

[hazeleyre23]

Hi,

I was wondering if it is possible to do an INSERT INTO with a WHERE statement in VBA.

Below is a copy of my code, however I only want it to insert on the same ID Line, is this possible?

            CurrentDb.Execute "INSERT INTO dbo_Agent_Data(ToDate)" & _
            " VALUES('" & Me.txtUpdateToDate.Value & "')"

thanks

[pbaldy]

Try the other format:

INSERT INTO...
SELECT...
FROM...
WHERE...

[hazeleyre23]

Hi, it is coming up with a syntax error, would you be able to advise what I am doing wrong (sorry fairly new to Access).

            CurrentDb.Execute "INSERT INTO dbo_Agent_Data(ToDate)" & _
            " SELECT ('" & Me.txtUpdateToDate.Value & "')" & _
            " FROM Forms![ToDateUpdate]" & _
            " WHERE id_New = '" & Me.txtUpdateID.Value & "'"

thank you very much

[pbaldy]

The FROM would need to be the name of a table.

[John_G]

If I understand you correctly, you do not want to use INSERT at all. Insert creates new records in your table; to change data in existing records (adding data into blank fields in existing records is not an INSERT), you need to use UPDATE.

Your statement might look something like this:

currentdb.Execute "Update dbo_Agent_Data set ToDate = #" & me!txtUpdateToDate & "# WHERE id_new = " & me!txtUpdateID

The # signs are date delimiters in MS Access SQL.

This assumes that the table dbo_Agent_Data is not the recordsource of your form. Does the form have a recordsource? It may not, but that's OK, it doesn't have to.

Post back if you need further clarification.

[hazeleyre23]

Hi,

Yes that is exactly what I am after, update the line rather then insert a new line, thank you

I have put that statement into my code but I am now receiving an error message:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column"

My Identity column column is ID, this is also my Primary Key. Not sure what I need to do next.....

thank you for your help.

[Homegrownandy]

Why not ue the design view? its a lot easier haha

[John_G]

OK, I didn't know you were using SQL Server. I don't use it, so I can't help much. I'm sure there are other gurus here who can.

I'll keep watching this thread so I can learn the answer, too!

[hazeleyre23]

Ok thank you anyway for your help.

[hazeleyre23]

I solved it....

CurrentDb.Execute "UPDATE dbo_Agent_Data SET ToDate = #" & Me!txtUpdateToDate & "# WHERE id_NEW = " & Me!txtUpdateID & "", dbSeeChanges

[John_G]

Glad you got it working - I'll have to look that option up.

If only every problem had a solution as easy as that.....sigh!