Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 29th, 2011, 10:36 AM
Authorized User
Points: 303, Level: 6
Points: 303, Level: 6 Points: 303, Level: 6 Points: 303, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , United Kingdom.
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
Reply With Quote
  #2 (permalink)  
Old November 29th, 2011, 07:09 PM
Friend of Wrox
Points: 1,015, Level: 12
Points: 1,015, Level: 12 Points: 1,015, Level: 12 Points: 1,015, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
The Following User Says Thank You to HiTechCoach For This Useful Post:
markhh (December 1st, 2011)
  #3 (permalink)  
Old December 1st, 2011, 04:58 AM
Registered User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Location: Germany
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 .....
Reply With Quote
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
  #4 (permalink)  
Old December 1st, 2011, 06:02 AM
Authorized User
Points: 303, Level: 6
Points: 303, Level: 6 Points: 303, Level: 6 Points: 303, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , United Kingdom.
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
Reply With Quote
  #5 (permalink)  
Old December 1st, 2011, 07:39 AM
Registered User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Location: Germany
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
Reply With Quote
  #6 (permalink)  
Old December 1st, 2011, 08:01 AM
Authorized User
Points: 303, Level: 6
Points: 303, Level: 6 Points: 303, Level: 6 Points: 303, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , United Kingdom.
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
Reply With Quote
  #7 (permalink)  
Old December 1st, 2011, 08:25 AM
Registered User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Location: Germany
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
Reply With Quote
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
  #8 (permalink)  
Old December 1st, 2011, 09:41 AM
Authorized User
Points: 303, Level: 6
Points: 303, Level: 6 Points: 303, Level: 6 Points: 303, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , United Kingdom.
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
Reply With Quote
  #9 (permalink)  
Old December 1st, 2011, 09:52 AM
Registered User
Points: 42, Level: 1
Points: 42, Level: 1 Points: 42, Level: 1 Points: 42, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Location: Germany
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.
Reply With Quote
The Following User Says Thank You to Harry For This Useful Post:
markhh (December 1st, 2011)
  #10 (permalink)  
Old December 1st, 2011, 10:27 AM
Authorized User
Points: 303, Level: 6
Points: 303, Level: 6 Points: 303, Level: 6 Points: 303, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , United Kingdom.
Posts: 71
Thanks: 23
Thanked 0 Times in 0 Posts
Default

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

Mark
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:59 AM.


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