Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Location
    Bronx, NY
    Posts
    3

    Unhappy Unanswered: Updating Specific Items in an Access Table

    I am using Access 2007. What I have is a table (tblFY_Table) that has columns for CFY, A, B, and C. The structure is shown here:

    tblFY_Table

    CFY A B C
    10 24 16 22
    11 18 33 6
    12 1 1 1
    13 1 1 1


    There's also a main table that has Name, FY, and "y/n" fields for A, B and C. When you input a new record, you enter a Name, a Fiscal Year (FY), and whether or not the new entry has grants A, B, or C. The counter also is different each year. If you state that a record has grant B, the OnUpdate instructs it to go to the tblFY_Table and look under CFY and pull the current value of that entry. So for instance, if it were FY 11, and we had B, then it would pull a "33" into "B" from the table by using the command:

    B = DLookup("[B]", "[tblFY_Table]", "[tblFY_Table.CFY]= val([FY])")

    After the value is pulled to the main table, the specific item in tblFY_Table needs to be incremented by 1 and written back. I need help doing this part. I don't know how to specify rows and columns in a table for updating. Just pointing me in the right direction would be great. I've run out of ideas.

    Roy

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically there are two methods to update the contents of a row (in your case the contents of a column of a row) in a table:

    1. The SQL way:

    Code:
    Dim strSQL as String
    strSQL = "UPDATE <MyTable> SET <SomeColumn> = <SomeValue> WHERE <Criteria to identify the row in the table>;"
    CurrentDb.Execute strSQL
    2. The Procedural way (here using DAO):

    Code:
    Dim rst as DAO.Recordset
    Set rst = CurrentDb.OpenRecordSet("<MyTable>", dbOpenDynaset)
    rst.FindFirst "<Criteria to identify the row in the table>"
    If rst.Nomatch = False Then
        rst.Edit
        rst!<SomeColumn> = <SomeValue>
        rst.Update
    Else
        ' Row was not found
    End If
    There are some possible variations for both methods but you get the general idea.

    Caution: If several rows match the criteria, each method behaves differently:

    Method 1. will update ALL rows matching the criteria.
    Method 2. will only update the first row matching the criteria (but this can be changed with a loop).
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Location
    Bronx, NY
    Posts
    3

    Will Try Out Suggestions

    Thanks for the quick response, Sinndho. The second method (Procedural) appears to be exactly what I need. I will try it out and will update this thread with my experience. Thanks for the guidance as I was going crazy.

  4. #4
    Join Date
    Apr 2010
    Location
    Bronx, NY
    Posts
    3

    Thanks a lot. My sanity is saved.

    Hi, Sinndho,

    I followed the advice you gave me and modified your code to suit my tables and structure. It worked great. I have to add some syntax to trap errors and stuff like that, but the main concept is now working. Thanks a lot for your help. Maybe some day, I can help someone else, also. This is the AfterUpdate code in my form.

    Code:
    Private Sub Sec_A_YN_AfterUpdate()
    If Sec_A_YN = "y" Then
    '      My number is created on the fly by stringing together the FY field,
    '      plus a dash, then the right 4 characters of the current value in the
    '      tblFY_Table under the current FY. I divided by 10000 so that I could
    '      get the leading zeroes and then chopped off the decimal point.
    '      Then I add another dash and an "A" to indicate Section A.
    Sec_A = FY + "-" + Right(Str(DLookup("[A]", "[tblFY_Table]", "[tblFY_Table.CFY]= val([FY])") / 10000), 4) + "-" + "A"
    Dim rst As DAO.Recordset
    '      I dimmed tempval as a string because in the second tempval line below,
    '      a string is required.  I realize there must be a simpler way to do this,
    '      but at my level of experience, it's the best I can do.
    Dim tempval As String
    tempval = Right(Val(Str([FY])), 2)
    tempval = "[CFY] = '" & tempval & "'"
    Set rst = CurrentDb.OpenRecordset("tblFY_Table", dbOpenDynaset)
    rst.FindFirst tempval
        rst.Edit
    '      This is the increment by 1 of the current column "A" in the tblFY_Table
    '      for the current FY
        rst![A] = rst![A] + 1
        rst.Update
    End If
    End Sub
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by RoyAlomar View Post
    Maybe some day, I can help someone else
    You already have helped others by replying with your solution which is greatly appreciated by everyone. Without speaking for Sinndho, I'm sure he appreciated the response and kind words. He gives good advice.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome, and thanks for keeping us informed.
    Have a nice day!

Posting Permissions

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