Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| 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 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
  #1 (permalink)  
Old February 15th, 2004, 11:22 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default Update table with a recordset - help!!!

Hi,

I need help and I have no experience with recordsets.

I want to update a table after a specific report is printed. The report is generated on a click event. It is based on several tables. The main one has a yes/no field with a default value to yes (checked box). All the records with a value of yes in this field are printed. I actually print 1 enveloppe per record.

After the enveloppes are printed, I want to change the value of the yes/no field to NO (or unchecked).

Here is the VB code I did:



    Dim stDocName As String
    Dim db As Database
    Dim rec As Recordset
    Dim strmsg As String

    stDocName = "Enveloppes"
    DoCmd.OpenReport stDocName, acNormal

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("Req_EnveloppesSelection", OpenDynaset)


    Do Until rec.EOF
        rec.Edit
        Print = 0
        rec.Update
        rec.MoveNext
    Loop

    rec.Close



For some reason, the table is not updated. I checked if the recordset is updatable (if rec.updatable = true then...) and ran the code step by step. The record is updatable but is not updated. Please help me...

Chantal

  #2 (permalink)  
Old February 16th, 2004, 10:19 AM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If Print is the name of the field that holds the checkbox, then change the line to read as follows:

    
Code:
rec!Print = 0


Another way to address the field is:

    
Code:
rec.fields("Print") = 0




Darrell L. Embrey
  #3 (permalink)  
Old February 16th, 2004, 09:47 PM
Authorized User
 
Join Date: Nov 2003
Location: Hillsboro, Ohio, USA.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to bjackman Send a message via MSN to bjackman
Default

You could also add a sql string that updates the recordset and changes all the values to no. I have a payroll database that keeps track of flex time hours, after the update query runs it sets a yes/no value to true so the database ignores the record from then on. Hope this helps.

Branden

  #4 (permalink)  
Old February 17th, 2004, 08:06 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

Thank you very much. I added the exclamation mark between rec and the field name and it works.

Thank you very much Darrell.

Branden, I will try the sql for another update I have to do. Thank you too.

Chantal



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update Recordset using oledb.command jmhemadri Classic ASP Databases 5 September 11th, 2008 03:46 PM
Update one table to another table using DTS in SQL Printmaker SQL Language 0 July 24th, 2007 07:17 AM
Diff between Recordset.update & insert cmd mp3pm VB Databases Basics 6 September 1st, 2006 03:54 PM
Recordset Update question smarks Classic ASP Databases 2 March 14th, 2005 03:57 AM
Update parent table with the sum of child table gbrown SQL Language 2 November 9th, 2004 07:53 AM





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