SQL Update - New user

[marysilvaramos]

Hi:

My name is Maricruz, i am new programming with vba and have issues understanding the syntax of DoCmd.RunSQL Update. This is the line

DoCmd.RunSQL ("UPDATE Guardians SET Guardians.Address = Address_Students WHERE Guardians.ID = Id;")

id is a field in the form. When I execute it through a button in the form, a dialog shows asking if I want to update all the records, which is not true because i just want to update 1 record.

All the help is very welcome!

thanks very much in advance

Mary

 

[pbaldy]

You have to concatenate the form reference into the SQL string:

"UPDATE Guardians SET Guardians.Address = Address_Students WHERE Guardians.ID = " & Me.ID

Also, CurrentDb.Execute is more efficient than RunSQL and doesn't throw the warnings:

http://www.baldyweb.com/SQLWarnings.htm

 

[marysilvaramos]

Thanks for replying, Paul. it worked well when i use runsql, but when i tried with currentdb.execute, it shows the following error "runtime error 3061, too few paramenters. expected 1".

Thanks very much your help. It is appreciated.

Mary

 

[pbaldy]

What exactly is your code? I suspect you didn't concatenate the form reference.

 

[marysilvaramos]

Thanks for helping, Paul. Here is the line:

CurrentDb.Execute ("UPDATE Guardians SET Guardians.Address = Address_Students WHERE Guardians.ID = " & Me.ID)

Mary

 

[pbaldy]

That exact SQL works with RunSQL and not with Execute? What is the data type of the ID field? If it works with RunSQL I guess I might stay with it, but it doesn't make sense.

 

[marysilvaramos]

Hi Paul:

I am using a button to do the field update. this is its content:

Private Sub CopyStudentAddress_Click()
Me.Refresh
CurrentDb.Execute ("UPDATE Guardians SET Guardians.Address = Address_Students WHERE Guardians.ID = " & Me.ID)
Me.Refresh
End Sub

the id field is autonumber.

Thanks again for helping.

Mary

 

[pbaldy]

I can't see why that would work with RunSQL and not with Execute. Can you post the db here?

 

[marysilvaramos]

Thanks Paul for your help!