Results 1 to 3 of 3
  1. #1

    Updating a table in VBA

    I am using an append query to upload info into a database. What I'd like to do is create some code that will run the query then place the year into a field. I tried to use a structure like I would for entering values into a field on a form and Access didn't like it. Does anyone have any ideas??? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Private Sub Extract_Click()
    On Error GoTo Err_Extract_Click

    Dim stDocName As String

    stDocName = "Extract"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Dim MyDate, AdjustedDate
    MyDate = Date ' MyDate contains the current system date.
    AdjustedDate = Right(MyDate, 2)
    AdjustedDate = "20" + AdjustedDate
    tables.CR_Table.CRID = AdjustedDate

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Extract_Click

    End Sub

  2. #2
    Star Lounger
    Join Date
    Mar 2001
    Dudley, Midlands, England
    Thanked 0 Times in 0 Posts

    Re: Updating a table in VBA

    I'm a bit confused as to what you're trying to do, but I'll have a go...

    Firstly, if you just want the year, you can use the Year() function. To get the current year, use Year(Date()).

    I'm not quite sure if you want to store this date into one record of a different table or in each record that you've appended. If it's the latter than add Year(Date()) as a column in the append query and set the field you're appending to accordingly.

    If you just want to update a single value in a record in a table, you could do it in code, but if you know which record(s) you want to update then you could create an update query containing something like:

    UPDATE CR_table SET CRID = Year(Date())

    If this table contains multiple records and you only want to update one of them then you can add a WHERE clause to the update query.

    Hope this helps,


  3. #3

    Re: Updating a table in VBA

    Thanks! I'll try it out. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts