Loop only updating on last rec

[livvie]

Below is code I am using to insert data into a table an dthen update the est_id.
It works fine for the last record in the loop but the others in the loop are not being updated by the bottom piece of code.
Anybody know what I am missing


Do While Not rstmat.EOF
Debug.Print rstmat!est_id
Debug.Print rstmat.RecordCount
With rstmat
' myestid = !est_id
mymat = !mat_desc
.MoveNext
End With
With rstmat3
.AddNew
' !est_id = myestid
!mat_desc = mymat
.Update
.MoveNext
End With

Set frmEst = Me!fsubEstDets.Form
Dim rstmat2 As ADODB.Recordset
'Open a connection
Set cnn = New ADODB.Connection

str = "Provider=SQLOLEDB;Data Source=SBS;" & _
"Initial Catalog=Test ;User Id=SQLACCESS;Password=suer;"
cnn.Open str


Set rstmat2 = New ADODB.Recordset
strsql1 = "SQLACCESS.tblctmat "
rstmat2.Open strsql1, cnn, adOpenKeyset, adLockOptimistic, adCmdTable

Set rstest = New ADODB.Recordset
strsql = "SQLACCESS.tblctest"
rstest.Open strsql, cnn, adOpenKeyset, adLockOptimistic, adCmdTable

With rstest
.MoveLast
myestid = !est_id
End With

With rstmat2
.MoveLast
mynewid = !Matid
End With
Debug.Print mynewid

Dim sql2 As String

sql2 = " UPDATE SQLACCESS.tblctmat Set est_id = " & myestid & _
" WHERE matid = " & mynewid

Debug.Print est_id
Debug.Print "MYNEWID"; mynewid
'DoCmd.RunCommand acCmdSaveRecord
rstmat2.Close
Loop

 

[Adrianna]

Below is code I am using to insert data into a table an dthen update the est_id.
It works fine for the last record in the loop but the others in the loop are not being updated by the bottom piece of code.
Anybody know what I am missing


With rstest
.MoveLast
myestid = !est_id
End With

With rstmat2
.MoveLast
mynewid = !Matid
End With
Debug.Print mynewid

Dim sql2 As String

sql2 = " UPDATE SQLACCESS.tblctmat Set est_id = " & myestid & _
" WHERE matid = " & mynewid

Why are you only using .movelast on the recordsetfor you assignement of estid and newid values? If you only want to change a single record, then you shouldn't need a recordset.

Also, you have an sql2 string declare and do nothing with it?

Oops...forgot to add that it might be helpful if you step through it in the debugger to see what is actually happenning. That usually helps me!

 

[livvie]

Why are you only using .movelast on the recordsetfor you assignement of estid and newid values? If you only want to change a single record, then you shouldn't need a recordset.

Also, you have an sql2 string declare and do nothing with it?

Oops...forgot to add that it might be helpful if you step through it in the debugger to see what is actually happenning. That usually helps me!

I use .movelast to find the record I want to change (the last one added). I am getting the record from tblctmat , adding it to the same table and then changing it's id (effectively copying it) but this is all done together but I need to do this for all records in the recordset from tblctmat. (it's an estimate (tblctest) with multiple lines (tblctmat)) Sorry I forgot the line DoCmd.RunSQL (sql2) from the bottom.
I have gone thru it with debugger and Immediate and it looks like it is updating it there unless it is overwriting it again when it re -enters rstmat.
Sorry I know this is a bit confusing


PS I have jsut tidied up the code and it is running fine now, something must have been out of place like the DoCmd.RunSQL (sql2)