Modify an Existing Record in a DAO Recordset

[emsadoon]

Hi,
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database. Can somebody please guide me.

Private Sub Form_AfterUpdate()
 
    Dim dbs As DAO.Database
 
 
    Dim rstTest As DAO.Recordset
 
    Dim strQuery As String
 
    strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
    "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
    "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
    "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"
 
    Dim db As Database
    Set db = CurrentDb
 
    Set rstTest = db.OpenRecordset(strQuery)
 
    If Not rstTest.EOF Then
        MsgBox "Order: " & rstTest!Order_ID & " has " & rstTest![The Remaining Units] & " units left"
 
    End If
 
    If rstTest![The Remaining Units] = 0 Then
         MsgBox "You cannot add more product"
 
         MsgBox rstTest!OrderStatus
 
         rstTest.Edit
         rstTest!OrderStatus = "Completed"
         rstTest.Update
 
         MsgBox rstTest!OrderStatus
     End If
 
 
End Sub

 

[pbaldy]

It's because the SQL in strQuery results in a read-only query:

http://allenbrowne.com/ser-61.html

 

[emsadoon]

Alright, thanks for let me know the reason why is not updating. But, do you know how I can change a field related to one of the items I am getting from the query.

 

[pbaldy]

I'm not clear on the situation, but one option would be to get the key value from the recordset (or maybe from the form) and use that to execute an update query.

 

[emsadoon]

Ok Thanks.I will create an update query. I also have another question, do you know how I can limit data entry on my form (split design) when

If rstTest![The Remaining Units] = 0 Then

.

 

[pbaldy]

Does toggling the allow additions property work for you?

Me.AllowAdditions = False

 

[emsadoon]

I tried that already. But when the condition meets, for example, when I move the mouse cursor on the previous records(on the spreadsheet view of the form) which I want to Add/Edit, it gives the error :" you cannot go to a specified record". So, Is there any way that I can edit/add to the previous forms, but not add a new form.I think the spreadsheet view of the form is creating this issue.

 

[pbaldy]

You could be right; I've never used the split form. I wouldn't think that property would prevent you going to a different record though.

 

[emsadoon]

I added an update query to my code, but that does not update the order status. Can you please help me to correct the code.

Private Sub Form_AfterUpdate()
 
    Dim dbs As DAO.Database
 
 
    Dim rstTest As DAO.Recordset
 
    Dim strQuery As String
 
        strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
        "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
        "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
        "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"
 
    Dim db As Database
    Set db = CurrentDb
 
         Set rstTest = db.OpenRecordset(strQuery)
 
    If Not rstTest.EOF Then
            MsgBox "Order: " & rstTest!Order_ID & " has " & rstTest![The Remaining Units] & " units left"
 
         If rstTest![The Remaining Units] = 0 Then
 
             MsgBox "You cannot add more product"
             MsgBox rstTest!OrderStatus
 
 
            Dim rstUpdate As DAO.Database
            Dim strQuery2 As String
 
            strQuery2 = "UPDATE OrderingT " + _
            "SET OrderingT.OrderStatus = Completed " + _
            "WHERE (((OrderingT.Order_ID)=[rstTest]![Order_ID])); "
 
            End If
 
    End If
 
 
End Sub

 

[pbaldy]

You created SQL but didn't execute it:

db.Execute strQuery2

By the way, you'll have to concatenate the recordset reference as you did with the form reference earlier.

 

[emsadoon]

I changed the code, but I get this error: "Object variable or With block variable not set"

If Not rstTest.EOF Then
            MsgBox "Order: " & rstTest!Order_ID & " has " & rstTest![The Remaining Units] & " units left"
      
         If rstTest![The Remaining Units] = 0 Then
    
             MsgBox "You cannot add more product"
         
             MsgBox rstTest!Order_ID
        
        
            Dim rstUpdate As DAO.Database
            Dim strQuery2 As String
    
            strQuery2 = "UPDATE OrderingT " + _
            "SET OrderingT.OrderStatus = 'Completed' " + _
            "WHERE (((OrderingT.Order_ID)= " & [rstTest]![Order_ID] & ")); "
            
            dbs.Execute strQuery2
    
            End If
         
    End If

 

[emsadoon]

Sorry, I had dbs.Execute Query2, instead of db.Execute Query2. Now it works. I really appreciate your help pbaldy.

 

[pbaldy]

Happy to help!