how to move to next record using record set, using criteria

[noorani]

Please check this code,
i try to explain what this code is doing,

this code is written for the button in the invoice form,
when the user finish working with the invoice, then he have to press the button so the products can be reduce from the stock table,
my problem is to find the product and the stock name in the stock table with the same stock name and with the same product in the the invoice table,

well when i press this button, i can easily find product and the stock name from the stock table, which match with the current invoice,

now i want to reduce the quantity from the stock table,
i'm succeed in it. but the problem is, if the stock quantity equal to zero, then i should find the next record with the same stock name and product and reduce the remaining invoice quantity from the invoice table, this thing i can not achieve,

my code reduce the Stock quantity from the last matched record and then if the stock quantity equals to zero or less then the invoice quantity, it keeps reducing and in result i get the negative value.

i want to manage my code in this manner that , it should start finding the record from the Last record because i want to use the FIFO " First in First out " method so the product which comes first in the stock should be sold first,
so after finding it, I should reduce the stock quantity by formula like

Stock Quantity=Stock Quantity-Invoice Quantity

if Stock Quantity get eaul to zero then record pointer should move Up and should continue reducing the remaining invoice quantity .

one thing is importan that every record in stock table have the uniqe number and i'm storing this number in the invoice table while reducing the quantity. i'm doing it because in fututre i may need the referece of this sold product. i'm storing these references in three different tables
1) in the Invoice table
2) in the journal table
3) in the balance table but in the balance table only
amount not the product or stock name fields.

To understand the code properly i'm giving some references,

Recordset R is for the stock table

Recordset L is for the Invoice Detail table,

Recordset N is for the Invoice Main table,

RecordSet J is for the Journal table

Table Zad for the balance , after the successful operation amount and some references store in this table.

in my code you can find the title like BLOCK 4 OR BLOK 5,
i think i have to work out some where b/w these BLOKS, may be in need some kind of loop within one which i have in my code..


I ask you very much please help me, i'm really very upset,
i believe that there must be some smart people can help me to overcome this problem, i'll be very gratfull to you..

Thanks in advance..



Private Sub ABC_Click()
Dim title As String
Dim dbs As Database
Dim wsp As Workspace
Dim L As Recordset
Dim N As Recordset
Dim J As Recordset
Dim R As Recordset
Dim T As Recordset
Dim in_tr As Boolean
Dim in_dbs As Boolean
Dim after_per As Boolean
title = "Error Msg."
in_tr = False
in_dbs = False
after_per = False
Me.Refresh
If MsgBox("Invoice is going to process?", vbYesNo) = vbNo Then Exit Sub
Set dbs = CurrentDb
in_dbs = True
Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
in_tr = True

Set N = dbs.OpenRecordset("select * from Invoice_main where Invoice_no=" & Me!Invoice_no, dbOpenDynaset)

If N.RecordCount <= 0 Then
MsgBox (&quot;Can't find this Invoice number&quot; & Me!Invoice_no & &quot;. operation is not possibile.&quot, , title
GoTo err_prih
End If
If N!utv = True Then
MsgBox (&quot;Invoice is already processed. &quot, title
GoTo err_prih
End If

'operation starts

N.edit
N!rash_prih = False
N!utv = True
N.Update
Set L = dbs.OpenRecordset(&quot;select Invoice_detail.*,Products.Hp_ProductName from Invoice_detail inner join Products on Invoice_detail.ProductID1=products.Productid where Invoice_no1=&quot; & N!Invoice_no, dbOpenDynaset)
If L.RecordCount <= 0 Then
MsgBox (&quot;cant's find any product in this invoice . operation terminated.&quot, , title
GoTo err_prih
End If
Set J = dbs.OpenRecordset(&quot;jrn&quot;, dbOpenDynaset, dbAppendOnly)
'Trasfer records to jurnal table and decrease the quantity in the stock table

'BLOCK 4

cycle:
While Not L.EOF
Set R = dbs.OpenRecordset(&quot;select*from reestr where (stocknumber=&quot; & N!Stock_no & &quotand(productnumber=&quot; & L!ProductID1 & &quot&quot;, dbOpenDynaset)
If R.RecordCount <= 0 Then
MsgBox (&quot;can't find this product in the stock table&quot; & L!Hp_ProductName), , title

GoTo err_prih
End If
If R.RecordCount > 0 Then R.MoveLast

' check again if there is any record

If R.RecordCount <= 0 Then
MsgBox (&quot;can't find any record&quot
Exit Sub

Else

' BLOCK 5 I have problem in this steps
' if there is any record then reduce the quantity in stock table for
'the particular record, and if quantity is equal to zero in stock
'table then find the next record with the same stock name and the
'same product and reduce the quantity from it.

R.edit
R!quantity = (R!quantity - L!Quantity_given)
L.edit
L![reestr_number] = R!reestr_number
L![sellinprice] = R!price1
L.Update
If R!quantity = 0 Then
R.movenext
R!quantity = (R!quantity - L!Quantity_given)
L.edit
L![reestr_number] = R!reestr_number
L![sellinprice] = R!price1
L.Update
End If
R.Update

End If


'Add Records to jurnal table

J.AddNew
J![reestr_number] = R!reestr_number
J![jrdate] = N!date_of_invoice
J![Type] = 2
J![productid] = L!ProductID1
J![sellincode] = 0
J![sellinquantity] = 0
J![sellinprice] = 0
J![sellindastav] = 0
J![sellinsum] = 0
J![outcode] = N!Invoice_no
J![outquantity] = L!Quantity_given
J![outprice] = L!UnitPrice1
J![outsum] = ((L!Quantity_given) * (L!UnitPrice1))
J![returncode] = 0
J![rquantity] = 0
J![rprice] = 0
J![rsum] = 0
J![customer] = N!client
J![Stock] = N!Stock_no
J![company] = N!company
J![manager] = N!inemploye
J![rash_prih] = False
J.Update
R.Close
L.MoveNext
Wend

'Add amount to the balace table

dbs.Execute (&quot;INSERT INTO zad (client, date_utv, summa,rash_prih_vaz,rash_prih,selloutno)SELECT Invoice_main.client, Invoice_main.date_of_invoice,(sum_invoice([Invoice_no])) AS summa,2 ,Invoice_main.rash_prih,Invoice_main.Invoice_no FROM Invoice_main WHERE (Invoice_main.rash_prih=False) And (Invoice_main.utv=True) AND (Invoice_main.Invoice_no=&quot; & Me!Invoice_no & &quot&quot
wsp.CommitTrans
in_tr = False
J.Close
N.Close
L.Close
dbs.Close
in_dbs = False
wsp.Close
MsgBox (&quot;operation have successfully completed&quot
Exit Sub

err_prih:
If in_tr = True Then
wsp.Rollback
dbs.Close
in_dbs = False
wsp.Close
in_tr = False
End If
If in_dbs = True Then
dbs.Close
in_dbs = False
End If
End Sub

 

[MikeLoon]

Have you tested your SQL?