Multiple Row Update Fails

[Moore71]

Hi,
I have an application that update multiple rows (between 10-80) records at a time. My challenge is when I click the update button to update records up to 30-50, it skips the first 11-29 records and update the rest.
Please can some tell me why it's like that?
Here's my VBA for update:
"On Error Resume Next

Dim RS As DAO.Recordset
Dim KeK As Integer
KeK = 0

Set RS = Me.RecordsetClone

DoCmd.RefreshRecord

If RS.EOF Then
MsgBox "Please Select at Least 1 Item to complete this Transaction", vbInformation, "JMT Business Solution"
Me.cboItemID.SetFocus
Else
With RS
RS.MoveFirst

Do While Not RS.EOF

RS.Edit

RS("QtyAvail") = RS("QtyAvail") + RS("QtyIn")

RS("UnitCost") = RS("ExtendedPrice")

RS("SupplierAcc") = RS("SupplierAcc") - RS("AmountRecorded")

RS.Update
RS.MoveNext

Loop

RS.Close

End With

Set RS = Nothing
End If"
Thanks a million for quick response
Moore

 

[Ranman256]

You don't update using code.
That's what queries are for.

 

[jdraw]

Further to ranman's comments, please tell us more about the application.

You have some records to update. Where is the data to be used in the update coming from?

In plain English what is being updated with what??--some details and an example please.

 

[Moore71]

No, I have earlier use Update Query, but it will update some and not update the last record, especially when there are many records. Then I thought I have find a way out through recordset update. Although, it was working before now

 

[Minty]

An update query is definitely the way to do this. This sounds like data problems rather than the query not working. Using a recordset for this is like moving water with a sieve.

If you create a select query that pulls in the same data - look for null values, or other data issues. The fact you have tried this two ways with similar failures points to incorrect data or expectations of data.

If you can't see anything you'll have to paste up some data that exhibits the problem and your update query that doesn't work.

 

[Moore71]

Ok, having my code changed to SQL statement, here's the ERROR generated:

Dim StrSQL As String
StrSQL = "UPDATE (tblOutbound INNER JOIN tblClient ON [tblOutbound].[ClientID] = [tblClient].[ClientID]) INNER JOIN tblItem ON [tblOutbound].[ItemID] = [tblItem].[ItemID] SET [tblOutbound].[ClientID] = [forms]![frmPoS]![cboClient], [tblItem].[QtyAvail] = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], [tblItem].[UnitSale] = [tblOutbound].[ExtendedPrice], [tblClient].[ClientAcc] = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded]"
"WHERE [tblOutbound.InvoiceNo]='"& [forms]![frmPoS]![Invoice] & "';"

Please what is the better way to put this ERROR in red
Thanks a million

 

[Moore71]

Ok, having my code changed to SQL statement, here's the ERROR generated:

Dim StrSQL As String
StrSQL = "UPDATE (tblOutbound INNER JOIN tblClient ON [tblOutbound].[ClientID] = [tblClient].[ClientID]) INNER JOIN tblItem ON [tblOutbound].[ItemID] = [tblItem].[ItemID] SET [tblOutbound].[ClientID] = [forms]![frmPoS]![cboClient], [tblItem].[QtyAvail] = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], [tblItem].[UnitSale] = [tblOutbound].[ExtendedPrice], [tblClient].[ClientAcc] = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded]"
"WHERE [tblOutbound.InvoiceNo]='"& [forms]![frmPoS]![Invoice] & "';"

Please what is the better way to put right this ERROR in red
Thanks a million

 

[jdraw]

I think we need to know more about what you are trying to do in plain English. It may be that you have jumped to a solution, but we really don't understand the problem or objective.

As for the SQL, you have a long string.Unfortunately, it contains

[forms]![frmPoS]![cboClient] which should not be part of the string, but a reference to the evaluated value of the form control. This needs to be corrected.

Also you are storing a calculated value(s)
[tblItem].[QtyAvail] = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut] and
[tblClient].[ClientAcc] = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded]

Your WHERE clause is not connected to the first part of the sQL string.
If it was meant to be part of the SQL you need to concatenate (using & ) the 2 parts.

I don't recall seeing 3 tables in an Update query. ( tblItem, tblClient and tblOutbound)

I think this needs to go back to a clear understanding of the issue/opportunity and tables and relationships.

Good luck.

 

[Moore71]

Thank you,
so now what is the best way to reframe the query?
I actually copied the SQL statement from my QBE design I was using which included the calculations

 

[jdraw]

As I mentioned

I think this needs to go back to a clear understanding of the issue/opportunity and tables and relationships.