Please I need More Info on this my VBA code

[Moore71]

Hi please can someone help me out on this code?

 Dim Db As Database
 Dim RS As Recordset
    
 Set Db = CurrentDb
   Set RS = Db.OpenRecordset("SalesQRY", dbOpenDynaset, dbSeeChanges)
   
 With RS
 ' Modify data in local recordset.
 Do While RS.EOF
 .Edit
 If [U][B][COLOR=Red]RS[/COLOR][/B][/U] <> Forms!frmPoS!Invoice Then
    '!royalty = !royalty - 4
 Else
    RS!QtyAvail = RS!QtyAvail - RS!QtyOut
    RS!UnitSale = RS!ExtendedPrice
    RS!ClientAcc = RS!ClientAcc + RS!AmountRecorded
 End If
 .Update
 .MoveNext
 Loop

I am having issue refering this table on my form. It keeps giving me complain about the RS I mark red and underlined.
Please I need way out


Thanks in advance

 

[arnelgp]

those the recordset has Invoice also, try:

If RS!Invoice <> Forms!frmPoS!Invoice

 

[isladogs]

Also change the Do While line to e.g. Do Until RS.EOF
There may need to be other changes as well

 

[theDBguy]

Hi. What are you trying to check? Just in case it helps, can you also post the SQL statement for SalesQRY? Thanks.

 

[Moore71]

Well here is the SQL statement which is also the row source for my form:


SELECT tblOutbound.ItemID, tblItem.ItemName, tblItem.QtyAvail, tblItem.UnitSale, tblOutbound.ExtendedPrice, tblOutbound.QtyOut, tblOutbound.DateOut, tblOutbound.ClientID, tblClient.ClientAcc, tblOutbound.InvoiceNo, (([QtyOut]*[ExtendedPrice])-[DiscountCalculated])-([QtyOut]*[UnitCost]) AS Margin, tblOutbound.Recordby, tblOutbound.[Inv#], tblItem.UnitCost, tblItem.Package, tblOutbound.DiscountAllowed, tblOutbound.DiscountCalculated, tblOutbound.AmountRecorded, tblItem.ItemCode, tblItem.OutboundMark, tblOutbound.OutboundID, tblClient.[Purchase%], tblOutbound.DeleteYN, tblSubCategory.SubCategoryName
FROM tblSubCategory INNER JOIN (tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID) ON tblItem.ItemID = tblOutbound.ItemID) ON tblSubCategory.SubCategoryID = tblItem.SubCategoryID;

 

[theDBguy]

Well here is the SQL statement which is also the row source for my form:

Ah, the plot thickens. You seem to be trying to update the same data source as the one you're viewing, which could run into write conflicts. As for my other question, what exactly were you trying to accomplish? Let's say you're viewing record #10 out of 100 records on the form, are you trying to update all the records in the table based on the data on record #10? What happens when you move on to other records? This might be easier to accomplish using an UPDATE query rather than VBA recordset loop.

 

[arnelgp]

you have two invoice number there:

InvoiceNo and Inv#