Referring to a field and updating Programmatically

vmerc · January 11th, 2006, 01:08 PM

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!

Bob Bedell · January 11th, 2006, 04:45 PM

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

RuralGuy · January 11th, 2006, 10:59 PM

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

Bob Bedell · January 12th, 2006, 02:13 AM

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

vmerc · January 12th, 2006, 10:28 AM

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