Update stock data

[silversurf]

Hello,
I am trying to build a pos and inventory system in vb 6.0 with MS-Access database. I do not have much experties in vb 6. Ok here is the question now, I have a frmcashsale in my system which have a MSHFlexgrid called grid, I have managed the codes to update the grid with invoice no, invoice date, product code, price, net amount and remarks columns. Also there is a sub total, Total and cash change txt boxes those are working fine. I have a cmdConfirm button in the from. What I now want is , after I complete the sales, I will click the cmdConfirm button and the following two things will happen :
1) Details of the sales will be updated in tblsales table (I think I can manage that) but the tough part is to calculate the profit. I want the price ( i mean sale price) will be deducted from the buy price (which is not stored in tblsales, stored in tblstock) and profit will be stored in tblsales for each row of MSHFlexgrid.
2) Stock will be updated as per the product code of the tblstock. There is a field in tblstock named on_hand which will deducted as per the sold no. of pcs of that product for each row of MSHFlexgrid..
I am using Ms-Access as database and VB 6.0 for programming. I will try to attach an image of my frmCashSales that will clear the matter very much I think.

Any types of help will be very much appreciated.

Silversurf

[silversurf]

I have the following code to update the stock data (the on_hand field) in my database

Dim xcode As String
Dim xpcs As String
Dim lrow As Integer
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
con.Open
With grid
For lrow = .FixedRows To .Rows - 1
.TextMatrix(lrow, 3) = xcode
.TextMatrix(lrow, 5) = xpcs
Next lrow
With rs
.Open ("Select on_hand from tblstock where code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
.Fields!on_hand = Val(.Fields(on_hand)) - Val(xpcs)
.Update
End With
End With
End Sub

and its not working for me, so what should be the solution to maintain the stock level ?

[AndreRet]

Ok, lets see if I can get my head around this.... :)

You have 2 tables - tblsales, tblstock
a call has to be made to tblstock to see how much of a unit is available. if available, sell the unit and update tblsales...

get price from tblstock, how many units sold, get total for THAT SPECIFIC sale?

update tblstock to deduct the sold units, update tblsales with the units bought AND a total? which total, ALL units or just that particular item?

[AndreRet]

No answer on my questions yet?

[silversurf]

sorry for the delay.
Yes I have two tables-tblstock-have all the stock details, tblsales-will have all the details of my sales.
"a call has to be made to tblstock to see how much of a unit is available. if available, sell the unit and update tblsales"
yes you are right.
"get price from tblstock, how many units sold, get total for THAT SPECIFIC sale?"
here I like to add some. not only the total also the profit from this sale. I mean :
Profit = ((sale price) x no. of units sold - ((buy price) x no. units sold )
here buy price (bprice) is in tblstock and no. of units sold along with sale price (sprice) is in tblsales. Profit will also be stored in tblsales. This sales price and profit will be store accordingly by the product code
"update tblstock to deduct the sold units, update tblsales with the units bought AND a total? which total, ALL units or just that particular item?" update tblsales with the units sold. just that particular item with the unique product code.
I am trying to give an example :

Suppose the database have a product in tblstock in 5 pcs with code "67AENKalJKS12" sale price(sprice) $1,990.00 and buy price(bprice) is $1,400.00.
in the Cash sales from, a sales invoice no. will serially generate when the from is loaded (I am trying to code this thing). User will put "67AENKalJKS12" in the code box and press enter, sale price will be automatically shown in the sales price txt box. NO. of pcs (suppose 1) should be given and there is a add button which will add the item in a mshflexgrid (grid). after adding all the item, there is another button name "confirm sale" which will clicked to perform the final sale. 
from the tblstock code "67AENKalJKS12' will be deducted by 1 and the field on_hand will show 4. 
in tblsales code "67AENKalJKS12" will be shown with the no. of pcs. sold is 1, sales price is $1,990.00 and profit will be $590.00" (if there is no discount). 
if 2 pcs sold, from the tblstock code "67AENKalJKS12' will be deducted by 2 and the field on_hand will show 2. the code "67AENKalJKS12" will be shown with the no. of pcs. sold is 2, sale price is ($ 1,990.00 x 2) and profit will be ($590.00 x 2)"

i have try to clear all the things. Waiting for your replay.
Again I am sorry for the delay.

[AndreRet]

lol, All fine. Ok, give me a few minutes to give some sample code... quite easy actually. :)

[silversurf]

I think you are very busy this days.

[AndreRet]

Eventually... :)

The code below will give you a good idea what to do, just edit the code to suit your needs...

Private Sub GetPrices()

Dim sprice As String, bprice As String, xcode As String, xProfit As String, sTotalOnHand As String, sTotalSold As String, sDiscount As String

sprice = txtSPrice.Text ''change to whatever txtbox name... sample here is 1990
bprice = txtBPrice.Text ''change to whatever txtboxname... sample here is 1400
sTotalSold = txtSold.Text
sDiscount = txtDiscount.Text
xcode = "67AENKalJKS12"

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
con.Open

rs.Open ("SELECT on_hand FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic

sTotalOnHand = rs!on_hand

If sTotalSold > sTotalOnHand Then
    MsgBox "You do not have enough stock on hand! There is only " & sTotalOnHand & " units in stock."

    Exit Sub
        Else
    rs!on_hand = Val(sTotalOnHand) - Val(sTotalSold)

    rs.Update
    rs.Close
End If

rs.Open ("SELECT * FROM tblsales WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic

xProfit = Val(sprice) - Val(bprice)
xProfit = xProfit - sDiscount
xProfit = xProfit * sTotalSold

rs!profit = xProfit ''Change rs!profit to whatever your field name is...

    rs.Update
    rs.Close
    con.Close
End Sub

[silversurf]

thank you thank you very very much for the code, but the following :
`

sprice = txtSPrice.Text ''change to whatever txtbox name... sample here is 1990
bprice = txtBPrice.Text ''change to whatever txtboxname... sample here is 1400
sTotalSold = txtSold.Text
sDiscount = txtDiscount.Text
xcode = "67AENKalJKS12"

I don't have text box, I have a mshflexgrid there, so should I change the text box code to grid code ??

[AndreRet]

Yip, you will be using something like -

sprice = grid.TextMatrix(lrow, 3) ''Just change the row and coloumn values to suit you...

[silversurf]

I have the following code now :

 Private Sub GetPrices()
        Dim xsprice As String
        Dim xbprice As String
        Dim xcode As String
        Dim xProfit1 As String
        Dim xProfit2 As String
        Dim xtotalonhand As String
        Dim xtotalsold As String
        Dim xdiscount As String
        Dim lrow As Integer

        With grid
        For lrow = .FixedRows To .Rows - 1
        .TextMatrix(lrow, 3) = xcode
        .TextMatrix(lrow, 5) = xtotalsold
        .TextMatrix(lrow, 6) = xdiscount
        .TextMatrix(lrow, 7) = xsprice

        Dim rs As New ADODB.Recordset
        Set con = New ADODB.Connection
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseClient
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        con.Open
        rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        rs!on_hand = xtotalonhand
        If xtotalsold > xtotalonhand Then
        MsgBox "You do not have enough stock on hand! There is only " & xtotalonhand & " units in stock."
        Exit Sub
        Else
        rs!on_hand = Val(xtotalonhand) - Val(xtotalsold)
        rs.Update
        rs.Close
        End If
        'rs.Open ("SELECT * FROM tblsales WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        xbprice = rs!bprice
        rs.Open ("SELECT * FROM tblsales"), con, adOpenDynamic, adLockOptimistic
        xProfit1 = Val(xsprice) - Val((xtotalsold * bprice)) - Val(xdiscount)
        xProfit2 = xProfit1 * xtotalsold
        'xProfit = xProfit - sDiscount
        'xProfit = xProfit * sTotalSold
        rs!Profit = xProfit2 ''Change rs!profit to whatever your field name is...
        rs!sprice = xsprice
        rs!squantity = xtotalsold
        rs!code = xcode
        rs!discount = xdiscount
        rs.Update
        rs.Close
        con.Close
        Next lrow
        End With
        End Sub

But I am getting the following error message :
Run-time error '3201':
Either BOF or EOF is true, or the current record ahs been deleted. requested operation requires a current record.
in the following line :

rs!on_hand = xtotalonhand

any help please.

[AndreRet]

tsk tsk.

That is because xcode is = nothing. You did NOT give a value for it...

.TextMatrix(lrow, 3) = xcode

means that you have set a value to a coloumn in your grid and NOT a value to xcode.

xcode = .TextMatrix(lrow, 3)

will now retun the value of the grid row and coloumn selected, then the search will work.

[silversurf]

Thank you again sir for your support, I now have the follwoing code

Private Sub GetPrices()
    Dim xsprice As String
    Dim xbprice As String
    Dim xcode As String
    'Dim xProfit1 As String
    'Dim xProfit2 As String
    Dim xtotalonhand As String
    Dim xtotalsold As String
    Dim xdiscount As String
    Dim lrow As Integer
    Dim xsinvno As String
    Dim xsinvdt As String
    Dim xremark As String
    Dim xsaledt As String

    With Grid
    For lrow = .FixedRows To .Rows - 1
    xsinvno = .TextMatrix(lrow, 1)
    xsinvdt = .TextMatrix(lrow, 2)
    xcode = .TextMatrix(lrow, 3)
    xtotalsold = .TextMatrix(lrow, 5)
    xdiscount = .TextMatrix(lrow, 6)
    xsprice = .TextMatrix(lrow, 7)
    xremark = .TextMatrix(lrow, 8)
    Dim rs As New ADODB.Recordset
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
    con.Open
    rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
    xtotalonhand = rs!on_hand
    rs!on_hand = Val(xtotalonhand) - Val(xtotalsold)
    xbprice = rs!bprice
    'xProfit1 = Val(xsprice) - Val((xtotalsold * bprice)) - Val(xdiscount)
    rs.Open ("SELECT * FROM tblsales WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
    'xProfit2 = xProfit1 * xtotalsold
    'xProfit = xProfit - sDiscount
    'xProfit = xProfit * sTotalSold
    rs!Profit = Val(xsprice) - Val((xtotalsold * bprice)) - Val(xdiscount) ''Change rs!profit to whatever your field name is...
    rs!sinv_no = xsinvno
    rs!sinv_date = xsinvdt
    rs!sprice = xsprice
    rs!squantity = xtotalsold
    rs!code = xcode
    rs!discount = xdiscount
    rs.Update
    rs.Close
    con.Close
    Next lrow
    End With
    Clearform
    End Sub

Everything seems to be alright just the profit is not calculating, the sprice is storing in the field of rs!profit of tblsales. I don't know why. I have tried with xProfit1 and xProfit2 and with few other tricks but can't overcome it. My bprice is in tblstock, sprice is in the form which comes from tblstock and profit will be stored in tblsales under the field "profit". the interesting thing is I change it to "profit" but it automatically change to "Profit". I am clue less.

[AndreRet]

Show me the table structure of both tables (field names)

[silversurf]

ok.
here goes tblstock :

inv_no
inv_date
Company
Catagory
Description
bdate
bprice
Quantity
totalbprice
tprice
code
on_hand
here is tblsales :

Sl
sinv_no
sinv_date
customername
customeradd
Remarks
sdate
sprice
squantity
profit
code
discount

I have another table named tblsinv which will conatain the follwoing fields and which I like to use for salesreturn purpose :
the fields of the tblsinv are :

Sl
sinv_no
sinv_date
customername
customeradd
sinvtotal
sinvdis

tblsinv and tblsales have a relationship through field sinv_no by which in salesreturn I want to sort our in which sales invoice the product was sold.

[AndreRet]

Thanx. I'll rewrite the code and test it as well. Will post here once its done.