Saving row data in ADO Recordset

veracity777

New member
Local time
Today, 22:06
Joined
Jul 10, 2003
Messages
8
Hi All,

I need to create a new record/row in a Header table, create one or more orresponding rows in the child table and then return to the header row and save some extra data.

I need to save the header row first to obtain the TransID which is an auto-increment field. This is used in all subsequent matching child records. Once all the child records are saved (these may vary from one to 100s per header row), I need to save a Total value back into the header row.

When I attempt to run this code, Access displays and error and advises that "The row cannot be located for updating. Some values may have changed since it was last saved."

Here is my code...
If curTotal = 0 Then
myTransRS.AddNew
myTransRS!TransDate = Now
myTransRS!EmpID = cEmpID
myTransRS.Update
cTransID = myTransRS!TransID
End If

If cSize <> "none" Then
curItemCost = CalcItemCost(curbutton.Name, cSize)
curTotal = curTotal + curItemCost
nLastItemCost.Value = curItemCost
nTotal.Value = curTotal
Else
CalcNonPLU
End If
myTransRS.MoveFirst
myTransRS.Find "[TransID]='" & cTransID & "'"
myTransRS!TransTotal = curTotal
myTransRS.Update

myTransItemRS.AddNew
myTransItemRS!TransID = 66
myTransItemRS!PLUID = myKeyLabelRS!PLUID
myTransItemRS!PLUAMOUNT = curItemCost
myTransItemRS!PLUDescrip = myKeyLabelRS!KeyLabel
myTransItemRS!PLUGroup = myKeyLabelRS!PLUGroup
myTransItemRS.Update

It's probably something simple (I am new to Access), but any help appreciated.

cheers
 
OK, I found a work-around for this. It is not elegant, and seems to me to be a "cowboy" fix, but it works. I would appreciate a comment from anyone who can explain why this is necessary.

Immediately before the line of code , myTransRS.MoveFirst , I added myTransRS.Requery.

This now saves the extra data, but I do not understand why this is necessary.

cheers
 
The header record had not been committed at the time you tried to update it. Requerying the recordset had the effect of committing the insert. Read up on transactions, commit and rollback to get a better understanding of what was going on.
 

Users who are viewing this thread

Back
Top Bottom