Results 1 to 10 of 10

Thread: Subtract the number of the saved product

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    464

    Subtract the number of the saved product

    Hello VBForums
    Hello evey one
    Please Gentelmans
    If you can help to resolve this problem
    With this code to save the invoice.. works very well .. this data saved in Table " INVOICE "
    I have another Table named ( ITEMS )
    I have in this table ( ITEMS ) some fields :Code-Product-Price-Quantity
    For example i have in table ITEMS in field Quantity ( I put it Numerique )
    Peach 500
    Apple 150
    I want when i save this invoice in table INVOICE ..example Peach 150 ..Apple 100
    In DataBase in fiels ITEMS will be apperas
    Peach 350
    Apple 50
    I want to subtract the number of product saved in invoice
    Thank you in advance for help
    Cordially
    MADA
    Last edited by MADA BLACK; Sep 26th, 2017 at 06:25 AM.

  2. #2
    gibra
    Guest

    Re: Subtract the number of the saved product

    Your unformatted/unidented code is unreadable.
    Please format/indent the code. Thank.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Subtract the number of the saved product

    I want to subtract the number of product saved in invoice
    so open the table items in a recordset and deduct the values or use an update query
    possibly a linked field would work if set up in the database
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    464

    Re: Subtract the number of the saved product

    Hiii gibra
    Thank you very much for help
    I have in module like this :
    Code:
    Public DX As New ADODB.Connection
    Public RX As New ADODB.Recordset
    Dim RsNewNo As New ADODB.Recordset
    Public ck As Boolean
    Public Sub ConnectionData()
    If DX.State = 1 Then DX.Close
    DX.CursorLocation = adUseClient
    DX.Provider = "Microsoft.Jet.OLEDB.4.0;"
    DX.Open App.Path & "\MADA.mdb"
    End Sub
    And in Form Load :
    Code:
    Private Sub Form_Load()
    ConnectionData
    End Sub
    Cordially
    MADA

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    464

    Re: Subtract the number of the saved product

    Hiii westconn1
    Thank you very much for help
    How to make this request please
    I tried to put this but it does not work :
    In Form Load :
    Code:
    'Set DB2 = OpenDatabase("MADA.mdb")
    'Set RS2 = DB2.OpenRecordset("ITEMS")
    And in Command save i include this line in my first code up but not result :
    Code:
    RX!Code = .TextMatrix(s, 0)
    RX!ItmName = .TextMatrix(s, 1)
    RX!Quantity = .TextMatrix(s, 2)
    RX!Prix = .TextMatrix(s, 3)
    RX!Somme = .TextMatrix(s, 4)
    '''I add this line
    DoEvents
    Set RS2 = DB2.OpenRecordset("Select * From ITEMS Where Code=" & Val(Trim$(MSHFlexGrid1.TextMatrix(i, 0))))
    If RS2.RecordCount > 0 Then
    DB1.Execute "UPDATE ITEMS Set Quantity=" & RS2![Quantity] - Val(Trim$(MSHFlexGrid1.TextMatrix(i, 2))) & " Where Code=" & _
    Val(Trim$(MSHFlexGrid1.TextMatrix(i, 0)))
    DoEvents
    End If
    RX.Update
    Next
    Cordially
    MADA

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Subtract the number of the saved product

    your database connection looks like it is DX, open another recordset on the same connection, maybe 2 connections to same database cause an issue

    In Form Load :
    why put in form load?
    i do not see an obvious problem with your code, but i do not know what should work from your application
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Subtract the number of the saved product

    MADA

    This is somewhat OT, but what gibra was talking about was this ..

    Code:
    Private Sub Command3_Click()
        Dim SQLs As String
        Dim s As Integer
        If Text1.Text = "" Then
            MsgBox "Write number of invoice", vbCritical, "Number invoice"
            Exit Sub
        End If
        With MSHFlexGrid1
            SQLs = "Select * From INVOICE where InvoiceNumero=" & (Text1.Text) & ""
            If RX.State = adStateOpen Then RX.Close
            RX.Open SQLs, DX, adOpenKeyset, adLockPessimistic
            If RX.RecordCount = 0 Then
                Dim XN As Integer
                Dim j As Integer
                Dim JL As Integer
                Dim sSQL As String
                .Col = 1
                .Row = 1
                For XN = .Rows - 1 To 1 Step -1
                    sSQL = ""
                    For j = 1 To .Cols - 1
                        sSQL = sSQL & (.TextMatrix(XN, j))
                    Next
                    If (sSQL) = "" And .Rows > 2 Then
                        .RemoveItem XN
                    Else
                        Exit For
                        Exit Sub
                    End If
                    If .Rows <= 1 Then Exit For
    .. taken from the first few lines of your OP.

    I see that you subsequently began using the "code" wrapper, but indents really help.

    Spoo

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Subtract the number of the saved product

    Hi Mada,

    you are really geting tied up in knots here.
    To create your Invoice consider this...

    your create(add) the lines in your Flexgrid and have to subtract from DB table
    you delete a Line in your Flexgrid and have to add again to DB table
    you change the qty. and have to alter the DB table.. plus or minus to the DB table
    the whole Invoice is canceled


    break this up a bit.. the first step
    here a sample for Add

    Code:
    Option Explicit
    
    
    '1 Flexgrid
    '4 Textboxes name= txtRe with Index 0 bis 3
    '1 CommandButton name= cmdAdd
    Private Sub cmdAdd_Click()
       Dim i As Long, j As Long
       Dim Wert As Currency, Gesamt As Currency
          For i = 0 To txtRe.UBound
             If Len(txtRe(i).Text) = 0 Then
                MsgBox "something missing !"
                Exit Sub
             End If
          Next
          With MSFlexGrid1
             i = .Rows - 1
             .AddItem "", i
             .TextMatrix(i, 0) = i
             .TextMatrix(i, 1) = txtRe(0).Text
             .TextMatrix(i, 2) = txtRe(1).Text
             .TextMatrix(i, 3) = txtRe(2).Text
             .TextMatrix(i, 4) = Format(txtRe(3).Text, "0.00")
             Wert = Val(txtRe(2).Text) * CCur(txtRe(3).Text)
             .TextMatrix(i, 5) = Format(Wert, "0.00")
             
             For j = 1 To .Rows - 2
                Gesamt = Gesamt + CCur(.TextMatrix(j, 5))
             Next
             'show the Total
             .TextMatrix(.Rows - 1, 5) = Format(Gesamt, "0.00")
          End With
          'under the Add button here your Sql Statement to subtract
          'clear Textbox for next Input
          For i = 0 To txtRe.UBound
             txtRe(i).Text = ""
          Next
    End Sub
    Private Sub Form_Load()
          FlexInit
    End Sub
    
    Private Sub FlexInit()
       With MSFlexGrid1
          .Rows = 2
          .Clear
          .Cols = 6
          .TextMatrix(0, 0) = "Pos" 'pos. will count 1 to ??
          .TextMatrix(0, 1) = "Ar.No" 'your article number
          .TextMatrix(0, 2) = "Article name"
          .TextMatrix(0, 3) = "pcs." 'how many
          .TextMatrix(0, 4) = "Net." 'price per piece
          .TextMatrix(0, 5) = "Total" 'sum up pcs. * Net.
          .ColWidth(2) = 1800
          .ColAlignment(0) = flexAlignRightCenter
          .ColAlignment(1) = flexAlignRightCenter
          .ColAlignment(2) = flexAlignLeftCenter
          .ColAlignment(3) = flexAlignRightCenter
          .ColAlignment(4) = flexAlignRightCenter
          .ColAlignment(5) = flexAlignRightCenter
       End With
    End Sub
    when you click the Add Button then substract from Database
    once you have this going, the go for the alter qty.(from 10pcs. to 5pcs.)

    then Delete etcc...

    this would be my advice.

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    464

    Re: Subtract the number of the saved product

    Hiii Spooman
    Thank you vey very much
    Thank you for your help and kindness and super tracking of this topic
    Thank you for your super part of code ..now it work vey well
    A last problem to finish this nightmare .. please
    If I want to add a product of the same name the quantity will be in a same line of the MSHFlexgrid :
    Code:
    RX!ItmName = .TextMatrix(s, 1)
    I choose for example Apple 150
    If i choose Apple a second time for example 22
    In MSHFlexgrid Apple quantity will be appears 172 ....
    Thank you very much
    Regards
    MADA

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    464

    Re: Subtract the number of the saved product

    Hiii ChrisE
    Thank you master
    Thank you for help
    I will try to manipulate your code for take advantage of your experience and learn more
    I can not change all the code
    Thank you again .. very happy to meet you in this subject
    Cordially
    MADA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width