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 January 11th, 2006, 01:08 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Referring to a field and updating Programmatically

I can usually find my answers by googling, or checking this forum, but for some reason this one eludes me.

Details:

I am trying to check one table using the Dfirst domain aggregate, and copy that value (Date) to a second table in a new record. The new table is nothing more than a checklist that tells me if that date has been processed. I can not change the original table to do this, and my ultimate goal is to use the checklist to control aspects of a form. Basically I can't figure out how to reference the new table/record/field to PASTE the date in to. The rest of it I have pretty much figured out. Here's my completely non-working code, but at least it will give you an idea:

Code:
Private Sub Append_D2A_Click()
On Error GoTo Err_Append_D2A_Click

    Dim stDocName As String
    Dim Store_Date As Date
    Dim rst As Recordset
    Set rst = CurrentDb().OpenRecordset("Archived_Bit")

'    stDocName = "Append Daily to Linked Archive"
'    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Store_Date = DFirst("Date", "tbl_Inventory")
    rst.Fields(1) = Store_Date

Exit_Append_D2A_Click:
    Exit Sub

Err_Append_D2A_Click:
    MsgBox Err.Description
    Resume Exit_Append_D2A_Click

End Sub
I know I have to get the record moved to the end of the table before I add, but so far my code does not get past the "Set rst" line.

Thanks for any help!

  #2 (permalink)  
Old January 11th, 2006, 04:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

This should get it:

Dim strSQL As String
strSQL = "INSERT INTO Archived_Bit([Date]) VALUES (" & "'" & DFirst("Date", "tbl_Inventory") & "'" & ")"
DoCmd.RunSQL strSQL


Add DoCmd.SetWarnings False and DoCmd.SetWarnings True before and after the RunSQL command if you want to turn the warnings off.

HTH,

Bob

  #3 (permalink)  
Old January 11th, 2006, 10:59 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm thinking you will need to wrap the date in pound signs:

Dim strSQL As String
strSQL = "INSERT INTO Archived_Bit([Date]) VALUES (#" & DFirst("Date", "tbl_Inventory") & "#)"
CurrentDB.Execute strSQL, dbFailOnError

If you use the CurrentDB.Execute method there is no reason to mess with SetWarnings.

HTH RuralGuy (RG for short) acXP WinXP Pro
  #4 (permalink)  
Old January 12th, 2006, 02:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

The domain aggregate functions all return Variants, so single quotes or pound signs produce the same result. Either will move the value into a date/time field. Thanks for CurrentDb.Execute!

Bob

  #5 (permalink)  
Old January 12th, 2006, 10:28 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks guys that code works perfectly! Looks lke I am going to have to work on my SQL knowledge. :)



Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Field in Form cthibault Access 2 January 8th, 2008 03:37 PM
Programmatically update field default value srcLakeJake Access VBA 6 October 26th, 2007 01:29 PM
Updating Last_Updated Field arholly Access 4 January 17th, 2007 12:05 PM
Updating a Date field based on another field arholly Access VBA 6 November 22nd, 2006 11:19 AM
Updating a field using a SELECT statement? katie123 Access VBA 1 April 12th, 2006 10:23 AM





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