updating field with sql or vba 1

[mrwendell]

I have a issue where I want to update a field, with a timestamp via commandbtn.

I have a subform (adminsub) that onopen queries a table with results displayed as datasheet, with a field (udate)which will hold my(timestamp.

cannot loop thru the query since I did not index that field due to unknown how many records will or can be generated from the query.

once I complete my tasks with the queries, in this case checkall checkboxes as true via commandbtn... I want either in the same commandbtn(checkall) or a new one...insert the current timestamp in the field(udate) of this qry...there by updating the underlying table.

I know too simple huh? but I have tried variations cannot get it too work... the most I have achieved is updating only the first record.

appreciate any help out there!

 

[JonFer]

Can you post the SQL you use to display the records that will need to be timestamped? Are all of the records timestamped or just a subset that you have modified?

John

http://www.interautomateconsulting.com

 

[mrwendell]

here is the code I used to check all that are produced by my query. I tried to modify this code to do the same thing except donot check but timestamp a field called udate. couldnt get it to work.
I tried replacing .admin with .udate = now.... deleted the remainder including -1....ran that no luck

and yes i only want to timestamp the same records i just checked... in a separate form i code the same box with...if admin = true then udate = now...worked like a charm...it appears ACCESS is not recognizing the admin as check if i use the "checkallbtn"...so i tried a refresh btn..no luck.

Private Sub checkall_Click()
On Error GoTo Err_checkall_Click

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE qryMorrisonRDM04 SET qryMorrisonRDM04.admin = 1 WHERE (((qryMorrisonRDM04.admin)<> 1)); ", -1


DoCmd.SetWarnings True

Exit_checkall_Click:
Exit Sub

Err_checkall_Click:
MsgBox Err.Description
Resume Exit_checkall_Click
End Sub

 

[JonFer]

You can update more than one field at a time by separating them with commas:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE qryMorrisonRDM04 SET qryMorrisonRDM04.admin = 1, qryMorrisonRDM04.udate = Now() WHERE (((qryMorrisonRDM04.admin)<> 1)); ", -1

DoCmd.SetWarnings True


John

http://www.interautomateconsulting.com

 

[mrwendell]

ABSOLUTELY FANTASTIC!!! THANKS JonFer

I do wanna ask you about the syntax of that code...

WHERE (((qryMorrisonRDM04.admin)<> 1)); ", -1

is WHERE critical? what if I just said update .admin=1 udate= now.... would it not just update it?? also,

what function does -1 serve?

I know youre probably busy just curious...anyway THANKS so much for your help.

 

[JonFer]

If Admin is a Yes/No, I would probably use this to be clear:

Set Admin=True Where Admin=False

This updates just the false values to true with the result being the same as if you didn't use a WHERE (all records will be true for Admin) but it is inefficient to update Admin when it is already true so I would keep the WHERE.

John

http://www.interautomateconsulting.com