Using .AddNew with ADO but cannot update the new record immediately

[rileyjm]

I'm using .AddNew to add 3 new records to my table (which it does perfectly). After I add these 3 records, I want to run an update on one field using DoCmd.RunSQL. When I run this though, I get '0 records updated'. It will not allow me to update these records right after I've added them. I'm using ADO vs. DAO so everything I've read has told me I should be able to work with the newly added record right away. What am I doing wrong? I've included my code below. Thanks in advance!

rst.Open "[tbl_RIF-App Info]", cnn, adOpenKeyset, adLockOptimistic

With rst
FirstApp = "Yes"
.AddNew
![DM #] = DM
![Estimate_Type] = "Sizing"
.AddNew
![DM #] = DM
![Estimate_Type] = "Planning"

.AddNew
![DM #] = DM
![Estimate_Type] = "Commit"



If ((Application = "BDS-ST") Or _
(doc.FormFields("DropDown7").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp1").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp2").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp3").Result = "BDS-ST")) Then
![BDS-ST] = 0

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [BDS-ST] = 0 WHERE [DM #]=" & DM

Else: ![BDS-ST] = Null

End If

.Update
.Close
End With

 

[DCrake]

Do you not need to issue a .Update after each .AddNew?

 

[Banana]

DCrake is correct. I believe this is one of well-known issues with ADO - Update is nominally optional but never has been. Thus, it's necessary to issue an explicit Update before leaving the record in the question in any case.

 

[rileyjm]

Ok, I tried that but it still didn't update the 3 new records. How frustrating!

Here's my revised code:

rst.Open "[tbl_RIF-App Info]", cnn, adOpenKeyset, adLockOptimistic

With rst
FirstApp = "Yes"
.AddNew
![DM #] = DM
![Estimate_Type] = "Sizing"
.Update

.AddNew
![DM #] = DM
![Estimate_Type] = "Planning"
.Update

.AddNew
![DM #] = DM
![Estimate_Type] = "Commit"
.Update



If ((Application = "BDS-ST") Or _
(doc.FormFields("DropDown7").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp1").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp2").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp3").Result = "BDS-ST")) Then
![BDS-ST] = 0

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [BDS-ST] = 0 WHERE [DM #]=" & DM

Else: ![BDS-ST] = Null

End If

.Update
.Close
End With

 

[DCrake]

You now have an extra .Update before your End With

Have you stepped through the code yet?

 

[rileyjm]

Thanks for noticing that extra .Update. I took it out but same result. Yes, I stepped though it and it doesn't find a match in the table based on my WHERE clause. So I tested it further by hard coding the previous record number in the table instead of the value of & DM for the current record in the WHERE clause. It then finds the previous 3 records I added and runs the update. So for some reason, it can't work with the 3 brand new records which is what I want.

 

[DCrake]

Even though you have added them I think they actually need commiting. Try adding

.MoveLast
.MoveFirst

Just noticed

If ((Application = "BDS-ST") Or _
(doc.FormFields("DropDown7").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp1").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp2").Result = "BDS-ST") Or _
(doc.FormFields("RBITApp3").Result = "BDS-ST")) Then
![BDS-ST] = 0

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [BDS-ST] = 0 WHERE [DM #]=" & DM

Else: ![BDS-ST] = Null

End If

You can't use If .... OR ....OR...

 

[rileyjm]

I tried that (revised code below) but it netted the same result with 0 rows updated. Like you said, somehow it's not committing these 3 new rows to the table so it can't find them to run the update. I tried the .MoveLast / .MoveFirst once and then added after each .AddNew but it gave the same result.

rst.Open "[tbl_RIF-App Info]", cnn, adOpenKeyset, adLockOptimistic

With rst
FirstApp = "Yes"
.AddNew
![DM #] = DM
![Estimate_Type] = "Sizing"
.Update

.MoveLast
.MoveFirst

.AddNew
![DM #] = DM
![Estimate_Type] = "Planning"
.Update

.MoveLast
.MoveFirst

.AddNew
![DM #] = DM
![Estimate_Type] = "Commit"
.Update

.MoveLast
.MoveFirst



If (Application = "BDS-ST") Then

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [BDS-ST] = 0 WHERE [DM #]=" & DM

Else: ![BDS-ST] = Null

End If