+ Post New Thread
Results 1 to 7 of 7
How do you do....it? Thread, How would I... in Technical; Morning, Any ideas how I would go about writing to two tables within an MS Access DB within the same ...
  1. #1
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    18

    How would I...

    Morning,

    Any ideas how I would go about writing to two tables within an MS Access DB within the same event procedure BeforeUpdate/AfterUpdate VBA code?

    The code I am using looks a little like that below,

    "Private Sub Form_Current()
    Dim l_action As String
    l_action = "Enter Action Here"
    Dim l_user As String
    l_user = fOSUserName()
    Dim dbs As Database
    Dim rs1 As Recordset
    Set dbs = CurrentDb
    Set rs1 = CurrentDb.OpenRecordset("tbl_One")
    With rs1
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update
    End With
    rs1.Close
    End Sub"

    However, where it says <Set rs1 = CurrentDb.OpenRecordset("tbl_One")> I would also like it to update another table at the same time, I.E tbl_Two.

    Any ideas? Or is this simply not possible?!

    Thanks in advance.

  2. #2

    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    171
    Thank Post
    55
    Thanked 19 Times in 18 Posts
    Rep Power
    22
    Would this work? The edits are in blue.

    "Private Sub Form_Current()
    Dim l_action As String
    l_action = "Enter Action Here"
    Dim l_user As String
    l_user = fOSUserName()
    Dim dbs As Database
    Dim rs1, rs2 As Recordset
    Set dbs = CurrentDb
    Set rs1 = CurrentDb.OpenRecordset("tbl_One")
    Set rs2 = CurrentDb.OpenRecordset("tbl_Two")
    With rs1
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update
    End With
    rs1.Close
    With rs2
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update
    End With
    rs2.Close

    End Sub"

  3. #3
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    18
    Now then, thats kinda of what I tried, only thing I didn't do was

    Dim rs1, rs2 As Recordset

    Which is perhaps why i didn't work. Looking at the layout, it should in theory work.

    I'll give that a go. Cheers for that!

  4. #4
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    18
    Quote Originally Posted by jcs808 View Post
    Would this work? The edits are in blue.

    "Private Sub Form_Current()
    Dim l_action As String
    l_action = "Enter Action Here"
    Dim l_user As String
    l_user = fOSUserName()
    Dim dbs As Database
    Dim rs1, rs2 As Recordset
    Set dbs = CurrentDb
    Set rs1 = CurrentDb.OpenRecordset("tbl_One")
    Set rs2 = CurrentDb.OpenRecordset("tbl_Two")
    With rs1
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update
    End With
    rs1.Close
    With rs2
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update
    End With
    rs2.Close

    End Sub"
    Okay, I gave that a try, however the VBA editor within Access is debugging on the .Update at the of rs2.

    With rs2
    .AddNew
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Table row] = Me![Table row]
    ![Date] = Mid(Now(), 1, 10)
    ![Time] = Mid(Now(), 12, 10)
    ![Process] = l_action
    ![User] = l_user
    .Update < HERE
    End With
    rs2.Close
    End Sub"

    Could this be relationships within the DB do you think?

  5. #5
    leco's Avatar
    Join Date
    Nov 2006
    Location
    West Yorkshire
    Posts
    2,026
    Thank Post
    595
    Thanked 125 Times in 119 Posts
    Rep Power
    42
    This might be a dumb question so ignore if it is. How does the recordset know that it has another update to do? Does the update flag stay open until the end of the Sub?

  6. #6
    acrobson's Avatar
    Join Date
    May 2007
    Location
    Tyne & Wear
    Posts
    519
    Thank Post
    5
    Thanked 6 Times in 6 Posts
    Rep Power
    18
    Quote Originally Posted by leco View Post
    This might be a dumb question so ignore if it is. How does the recordset know that it has another update to do? Does the update flag stay open until the end of the Sub?
    Good question!!!

    The original code is set to look at this module within Access -

    Function audit(l_action As String)

    Dim l_user As String
    Dim rs1 As New ADODB.Recordset

    rs1.Open "tbl_Audit_Trail", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    l_user = fOSUserName()

    rs1.AddNew
    rs1("Date") = Mid(Now(), 1, 8)
    rs1("Time") = Mid(Now(), 10, 8)
    rs1("Process") = l_action
    rs1("User") = l_user
    rs1.Update
    rs1.Close

    End Function

    Looking at that, would I need to do some work in the form of adding an "rs2" as new recordset or something like that?

  7. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,562
    Thank Post
    1,469
    Thanked 1,221 Times in 830 Posts
    Rep Power
    752
    Ok I'll admit straight off I haven't fully read your code so excuse me if this is a useless point, but I always use SQL commands to update tables in access, makes things much easier to read and updating multiple tables would be simple

    Code:
    DoCmd.RunSQL "SQL Statements Here"

SHARE:
+ Post New Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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