Delete data in a field after update 2

[BrenoAguiar]

here is the Update procedure I have. It sends 3 items of one table (units) to another one (unitnotesarchive). But how can I delete only the data in [Notes], in my current form, from table "units" after updating the "unitnotesarchive" table?
here it is:


Dim subm2 As Recordset

Set subm2 = CurrentDb.OpenRecordset("unitnotesarchive", dbOpenDynaset)

With subm2
.AddNew
![Computer Code] = Me.[Computer Code]
![Notes] = Me.[Notes]
![whoarchived] = Me.[userunit]
.Update

End With


subm2.Close

Thanks again for the help!

 

[VBslammer]

You'll have to figure out what key field to identify the record with, but you can execute an action query directly:

[tt]DoCmd.RunSQL "UPDATE Units SET [Notes] = NULL WHERE Units.ID = " & Me!ID[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[BrenoAguiar]

Beautifull. But I'm just getting a "missing operator" error in the expression:

DoCmd.RunSQL "UPDATE Units SET [Notes] = NULL WHERE Units.[Computer code] = " & Me![Computer Code]


right over: Units.[Computer code]= " & Me![Computer Code]

It is reading the Me![Computer code] fine but NOT the Units.[Computer code]

 

[Bugget]

Two things about the SQL string. First using the UPDATE keyword is actually going to leave a row there (Access may get rid of it if the whole row is NULL but in a strict SQL world it would stay). This may be what you intended as you state "only the data in [Notes]" but if you want to delete the whole row you need to use the DELETE keyword.

Second, because the whole section is in quotes, your IDE will not show you the value of Units.[Computer code] so that is probably not where the error is. I am going to guess that [Computer code] is of a text type. If this is true you need to put single quotes around it in the final SQL string. Here is what I would try:

DoCmd.RunSQL "UPDATE Units SET [Notes] = NULL WHERE Units.[Computer code] = '" & Me![Computer Code] & "'"

If you want to delete the whole row, all you need to do is change UPDATE to DELETE. Hope this helps!

- Tom

 

[BrenoAguiar]

Right on the Spot Tom!

I trully appreciate your help with the SQL. that's where I get confused all the time. But Help like yours make me understand better.

And, yes, it was supposed to update ONLY the data in the "notes" field! So the Cmd worked great!

Star for you