Results 1 to 5 of 5
  1. #1
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25

    VBA not updating the tables


    Here's my code:

    Code:
    Private Sub cmdChangeRMA_Click()
        'Allocate for a database
        Dim dbs As DAO.Database
        Set dbs = CurrentDb
        
        'Allocate for the recordsets
        Dim rstRMA As DAO.Recordset
        Dim rstProduct As DAO.Recordset
        Dim rstRecordMatch As DAO.Recordset
        Set rstRMA = dbs.OpenRecordset("tblRMA", dbOpenDynaset)
        Set rstProduct = dbs.OpenRecordset("tblProduct", dbOpenDynaset)
    
    
        ' a bunch of validation code for the input from the user...
        ' saving a string, parsing it into a double dblNewRMA
    
    
        'Convert value of current RMA (oldRMA) to a double
        Dim dblOldRMA As Double
        dblOldRMA = CDbl(Me.Txt_RMA.Value)
    
        'Clone a record and change the RMA to the new RMA
        Dim sqlFindRMA As String
        sqlFindRMA = "SELECT * FROM tblRMA WHERE RMA = " & dblOldRMA
        'Extract matching RMA from DB
        Set rstRecordMatch = dbs.OpenRecordset(sqlFindRMA, dbOpenDynaset)
        
        'rstRecordMatch should only return one record
        rstRecordMatch.MoveLast
        If Not (rstRecordMatch.RecordCount = 1) Then
            'This code SHOULD never execute
            MsgBox ("rstRecordMatch returned" & rstRecordMatch.RecordCount _
                & "Results." & vbCrLf & "Check your code." & vbCrLf _
                & "Now exiting macro.")
            Exit Sub
        End If
        
        'Copy all of the fields
        With rstRMA
            .AddNew
            !RMA = dblNewRMA
            !Customer = rstRecordMatch!Customer
            !DropShip = rstRecordMatch!DropShip
            !DateIn = rstRecordMatch!DateIn
            !DateOut = rstRecordMatch!DateOut
            !PONum = rstRecordMatch!PONum
            !InvNum = rstRecordMatch!InvNum
            !Classification = rstRecordMatch!Classification
            !Warranty = rstRecordMatch!Warranty
            !TgtDateOut = rstRecordMatch!TgtDateOut
            !RepairStatus = rstRecordMatch!RepairStatus
            !POStatus = rstRecordMatch!POStatus
            !Priority = rstRecordMatch!Priority
            !Comments = rstRecordMatch!Comments
            
            
            '!EmplAssigned = rstRecordMatch!EmplAssigned    'this line intentionally
                                                                              ' commented out
            
            .Update
    
        End With
    
        'Find and update any entries in tblProduct
        rstProduct.MoveFirst
        Do Until rstProduct.EOF
            If rstProduct!RMA = dblOldRMA Then
                rstProduct.Edit
                rstProduct!RMA = dblNewRMA
                rstProduct.Update
            End If
            rstProduct.MoveNext
        Loop
        
        'Delete the original RMA record
        rstRMA.FindFirst ("RMA = " & dblOldRMA)
        rstRMA.Delete
    
        rstRMA.Close
        rstProduct.Close
        rstRecordMatch.Close
        dbs.Close
        
        rstRMA = Nothing
        rstProduct = Nothing
        rstRecordMatch = Nothing
        dbs = Nothing
        
    End Sub
    Here's the explanation:
    The above code is located in a form which views the entries in tblRMA. tblRMA contains information for each RMA that we repair. The primary key field is "RMA". It has a relationship with tblProduct. tblProduct contains multiple serial/model number combinations as well as the RMAs to which they are assigned. So when I display the form, the tblRMA information is listed and any relevant entries in the tblProduct table are also displayed for that RMA.

    So I have a button on that page that will change the RMA number (primary key) for both tables where it matches on the old entry. The algorithm (simplified) is: Check both tables for entries. Create a duplicate entry in tblRMA with the new RMA, update all the entries in tblProduct with the new RMA, delete the old tblRMA entry. So I have the code do that, and the debug statements that I had in there show the code executing until the end, with no errors or runtime exceptions. However if I try to navigate to other records on the form, or even open either tblRMA or tblProduct, the changes are not reflected.

    Help please.

  2. #2
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    Also the tables are linked to a back end

  3. #3
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25

    Solved!

    Since the tables were linked apparently they wont update with DAO databases. SO, I explicitly set the dbs variable to open the back end, and worked with it that way.
    Code:
        Const strDatabasePath As String = "S:\...TEST Back End.accdb"
        Dim resMsgBoxResult As VbMsgBoxResult
        Dim strRMANew As String
        Dim dblNewRMA As Double
        
        
        
        Dim blIsBadString As Boolean
        
        'Allocate for a database and query definition
        Dim dbs As DAO.Database
        'COMMENTED THIS OUT
        'Set dbs = CurrentDb
    
        'CHANGED IT TO THIS
        Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDatabasePath)

    So that problem is solved. Now I need to figure out how to have the form refresh automatically. Guess I'll be reading the developer reference.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by weasel7711 View Post
    Since the tables were linked apparently they wont update with DAO databases.
    Well, that is a totally incorrect statement. Yes, they will update using DAO on linked tables and you don't need to open the backend. I'm not sure, just by quickly glancing at your original code, what could be wrong but it sure isn't that linked tables can't be updated with DAO since they can.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    So I have a button on that page that will change the RMA number (primary key) for both tables where it matches on the old entry. The algorithm (simplified) is: Check both tables for entries. Create a duplicate entry in tblRMA with the new RMA, update all the entries in tblProduct with the new RMA, delete the old tblRMA entry. So I have the code do that, and the debug statements that I had in there show the code executing until the end, with no errors or runtime exceptions. However if I try to navigate to other records on the form, or even open either tblRMA or tblProduct, the changes are not reflected.

    Help please.
    Why are you creating duplicate entries? Changing the primary key then deleting the original? I don't understand why you would want to do this. Why can't you keep the data on your original table? Honestly by duplicating data and/or deleting it you're really asking for trouble a better idea is to add a flag to your table that either invalidates the data (like a void) or forces the record to no longer show (a job complete yes/no box for instance).

    My cringing about duplicating data aside if the data is on a form you are probably going to have to do something like me.refresh or me.requery the subform if it is one. You can update the data all you want but unless you refresh the contents of your form I don't think you're going to see the changes you just made.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. User Input/Updating Tables Help
    By hawkins in forum Access
    Replies: 3
    Last Post: 06-07-2011, 04:48 PM
  2. Updating 4 Tables From 1 Form
    By kevsim in forum Forms
    Replies: 2
    Last Post: 11-15-2010, 03:08 AM
  3. Updating Multiple Tables
    By gazzieh in forum Queries
    Replies: 0
    Last Post: 03-17-2010, 05:59 AM
  4. Updating information in the tables
    By jamilian in forum Database Design
    Replies: 1
    Last Post: 02-17-2010, 08:46 AM
  5. Updating two tables using SQL Insert Into
    By glazzaro in forum Programming
    Replies: 0
    Last Post: 05-02-2008, 10:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums