Check box and updating a field

markhh · November 29th, 2011, 10:36 AM

Hi Folks, I am using Access 2003, can anyone tell me how to do the following - when a check box is checked on my form how can I get it to update a field in a table. The update is just inserting the date as in "Now()".

Many Thanks
Mark

HiTechCoach · November 29th, 2011, 07:09 PM

There are several methods you can use.

For example

1) if the field to be updates is part of the form's record source then place a text box on the form that is bound to the control. In the check box's after update event use

Code:

 Me.txtMyDateField = Now()

2) if the field is not in the form's record source then run an update .

Example after update event:

Code:

CurrentDB.Execute "Update myTable set MyTable.MyDateField = Now"

Harry · December 1st, 2011, 04:58 AM

But don't forget to check the content of the checkbox first

'If checkbox = True then .....

markhh · December 1st, 2011, 06:02 AM

Hi HiTechCoah, I tried both options and the below errors was the result of each. Please note - I have left out a very important piece of information - the frm is based on a query.

Compile Error: Invalid use of property

Code:

Me.Authorised Date = Now()"

The second option
Compile error: Method or data member not found (.Execute was highlighted)

Code:

Private Sub Authorised_AfterUpdate()
Request.Execute "Update tblComments set tblComments.Authorised Date = Now()"
End Sub

Regards
Mark

Harry · December 1st, 2011, 07:39 AM

For the second option you must replace 'Request.Execute' by 'Currentdb.Execute' as proposed by HiTechCoach. Give it a try

markhh · December 1st, 2011, 08:01 AM

Hi All, and many thanks for your help

My code now looks like this,

Code:

Private Sub Authorised_AfterUpdate()
CurrentDb.Execute "Update tblComments set tblComments.Authorised Date = Now()"
End Sub

It now comes up with a Syntax error in UPDATE statement

Note: when i put in CurrentDB it changes to CurrentDb. Also I have addes () to the Now method (Now())

Regards
Mark

Harry · December 1st, 2011, 08:25 AM

Hi Mark,

there is a space between Authorised and Date in the fieldname. In this case you should code:
CurrentDb.Execute "Update tblComments set tblComments.[Authorised Date] = '" & Now & "'"
take care of the single quotes . I tested this statement succesfully

markhh · December 1st, 2011, 09:41 AM

Hi there, well spotted (the space) it did not occur to me - I took the space out.

It now works - too well infact as it updates all records with today's date. This is what I do, I tick the text box and select the next record at the bottom of the form then a Write conflict dialog box appears telling me the record has been changed by another user since............

Save record - Copy changes - Drop changes.

Code:

Private Sub Authorised_AfterUpdate()
CurrentDb.Execute "Update tblComments set tblComments.AuthorisedDate = Now()"
End Sub

Any ideas

Mark

Harry · December 1st, 2011, 09:52 AM

Mark,
of course it needs a 'where'-clause to select the records to be updated. A simple Update-statement always uses all records in the table. I do not know what the criteria is to select a specific record in your table.
The write conflict is a result of the fact that you change a table which is part of the query you used for the form. Perhaps you should use a 'Requery' of the form to avoid that message.

markhh · December 1st, 2011, 10:27 AM

Hi Again Harry, many thanks for all your informative replies,

Mark