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.