Hello,
I am in for a huge headache because one of the users just discovered that the form that we use to update two tables isn't updating one of the tables. I was very careless during testing, so now we have a bunch of updates to trace and put in manually, so I am feeling pretty awful and ashamed. Luckily, they can't pay me any less than they do- it's just not possible, so I am not expecting any severe punishment.
Here is the problem:
the user types in a string into an unbound textbox and clicks an update button which is supposed to run on both tables.
-the first update happens to a table that is not linked to the form and is not open and therefore it's just a simple update sql statement.
-the second update happens to a recordset which is open and displayed on a subform of this same form. So I assume that I first need to close the recordset so I can update the table. I thought that I was accomplishing this by setting the recordsource of the subform to "', then running the update sql, and then resetting the recordset back to the original query. However, this does not work. I still get the message box "you are about to update 0 rows" when I know it's supposed to update 1 row, no more, no less. Would someone be able to look at the code and let me know what I am getting wrong? All the data types are matching, by the way.
Thank you!!
I also tried Me. InvoiceDetails_Sub.SourceObject= "" but it still says "about to update 0 rows."
I am in for a huge headache because one of the users just discovered that the form that we use to update two tables isn't updating one of the tables. I was very careless during testing, so now we have a bunch of updates to trace and put in manually, so I am feeling pretty awful and ashamed. Luckily, they can't pay me any less than they do- it's just not possible, so I am not expecting any severe punishment.
Here is the problem:
the user types in a string into an unbound textbox and clicks an update button which is supposed to run on both tables.
-the first update happens to a table that is not linked to the form and is not open and therefore it's just a simple update sql statement.
-the second update happens to a recordset which is open and displayed on a subform of this same form. So I assume that I first need to close the recordset so I can update the table. I thought that I was accomplishing this by setting the recordsource of the subform to "', then running the update sql, and then resetting the recordset back to the original query. However, this does not work. I still get the message box "you are about to update 0 rows" when I know it's supposed to update 1 row, no more, no less. Would someone be able to look at the code and let me know what I am getting wrong? All the data types are matching, by the way.
Thank you!!
PHP:
'update in Table1
DoCmd.RunSQL "Update Table1 set Inv= '" & Me.InvUpdate & "', " & _
" Comment= '" & Me.UpdateInvComment & "' where Inv like '" & Me.Inv & "' and " & _
"[VN] like '" & Me.VN & "';"
'make the details form invisible and set its source object to nothing, so the details form can be essentially closed
'and therefore the underlying table can be updated
Me. InvoiceDetails_Sub.Form.RecordSource = ""
Me. InvoiceDetails_Sub.Visible = False
'update Table2
DoCmd.RunSQL "Update Table2 set Inv= '" & Me.InvUpdate & "', " & _
" Comments= '" & Me.UpdateInvComment & "' where VN like '" & Me.VN & "' and inv like '" & _
Me.Inv & "';"
'reset the details form sourceobject and make it visible
Me. InvoiceDetails_Sub.Visible = True
Me. InvoiceDetails_Sub.Form.RecordSource = "InvoiceDetails_Sub"I also tried Me. InvoiceDetails_Sub.SourceObject= "" but it still says "about to update 0 rows."
Last edited:
