Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 29th, 2011, 10:36 AM
Authorized User
 
Join Date: Apr 2005
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default Check box and updating a field

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
 
Old November 29th, 2011, 07:09 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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"
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
The Following User Says Thank You to HiTechCoach For This Useful Post:
markhh (December 1st, 2011)
 
Old December 1st, 2011, 04:58 AM
Registered User
 
Join Date: Nov 2011
Posts: 6
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

'If checkbox = True then .....
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
 
Old December 1st, 2011, 06:02 AM
Authorized User
 
Join Date: Apr 2005
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default

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
 
Old December 1st, 2011, 07:39 AM
Registered User
 
Join Date: Nov 2011
Posts: 6
Thanks: 0
Thanked 3 Times in 3 Posts
Default

For the second option you must replace 'Request.Execute' by 'Currentdb.Execute' as proposed by HiTechCoach. Give it a try
 
Old December 1st, 2011, 08:01 AM
Authorized User
 
Join Date: Apr 2005
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default

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
 
Old December 1st, 2011, 08:25 AM
Registered User
 
Join Date: Nov 2011
Posts: 6
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
 
Old December 1st, 2011, 09:41 AM
Authorized User
 
Join Date: Apr 2005
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default

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
 
Old December 1st, 2011, 09:52 AM
Registered User
 
Join Date: Nov 2011
Posts: 6
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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.
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
 
Old December 1st, 2011, 10:27 AM
Authorized User
 
Join Date: Apr 2005
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default

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

Mark





Similar Threads
Thread Thread Starter Forum Replies Last Post
Change check box field to false in MS Acess VB saban ASP.NET 1.0 and 1.1 Basics 0 March 13th, 2007 05:19 AM
Updating a Date field based on another field arholly Access VBA 6 November 22nd, 2006 11:19 AM
Updating a check box in a dataenvironment davecritch Beginning VB 6 1 June 16th, 2006 11:03 AM
how to check the value of a field hertendreef ASP.NET 2.0 Basics 3 June 13th, 2006 03:53 PM
Need HELP!!! Record Input in Field - Check box on karlzoe BOOK: Beginning ASP 3.0 1 April 7th, 2005 09:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.