simple way to update record in table ?

[murray83]

evening folks

just quick question this code below works fine adding records to the table

DoCmd.RunSQL ("INSERT INTO main ( FromBay, ToBay, [Requested By]  )VALUES (fromBay.value, toBay.value, Authorised.value  )")

my question is this, is there a simple sql statment to update a record, all updating record i have seen run to quite a few lines and have tried

UPDATE main

this just added a new record

i want the next person in the process to fill out text box on form and then press update and then have it update the newest record

ta all

 

[pbaldy]

One way:

http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html

 

[murray83]

DoCmd.RunSQL ("Update main SET [ActionedBy] = Action.Value ,  [Status] = txtStatus.Value")

from that link i have worked this out and it kinda works which is great

my only qualm is it updates the whole table to what ever value is put in my txtstatus and not just the current record

any thoughts

 

[plog]

Use a WHERE clause:

https://www.w3schools.com/sql/sql_update.asp

 

[murray83]

ok can see why it has filled whole table as i had omitted the WHERE

but what would i put for the where have tried this and no surprise it didn't work

DoCmd.RunSQL ("Update main set [ActionedBy] = Action.Value ,  [Status] = txtStatus.Value, where Me.NewRecord=true ")

 

[pbaldy]

The comma before where would cause an error, and you need to refer to a field in the data.

 

[murray83]

ok comma removed and have this but still got a hole to fill for it to work

DoCmd.RunSQL ("Update main set [ActionedBy] = Action.Value ,  [Status] = txtStatus.Value where [ID] =  ")

as i belive i should have it look at the ID so it just does it to the newest one but not sure how/what that would look like

 

[pbaldy]

If it's the record on the form, why not just update a textbox on the form?

 

[Orthodox Dave]

Can I recommend using the query design facility in access. That way you can switch between design view (which is a really clear way of seeing what you are doing) and SQL view (which gives you the code with all the correct commas and semicolons!)

One day I may learn SQL code properly, but meanwhile the above makes life so much easier.

 

[plog]

DoCmd.RunSQL ("Update main set [ActionedBy] = Action.Value ,  [Status] = txtStatus.Value where [ID] =  ")

Ever see the movie Inception? DoCmd.RunSQL is like that--you are in VBA, but writing SQL which is a string, but sometimes you need to pop out of SQL to use a variable that's in VBA, and then go right back into the SQL string. You got lost at what level you were writing code.

Ultimately DoCmnd.RunSQL runs a string: DoCmd.RunSQL StringSQL

StringSQL needs to be a string in VBA, but valid also SQL. When you write a string in VBA it looks like this:

SomeVariable = "PlogRules"

SomeString = "This is the hardcoded part of the string. To use add data from a variable I must end the hardcoded part like this - " & SomeVariable & " - Then I can get back into the hardcoded part like this."

That's called escaping. It's how you tell the VBA processor which part is hardcoded string and should be placed in your string as is, and what part is from a variable and instead should use the value. You never escaped out of your SQL string to use variable information.