Results 1 to 8 of 8

Thread: Bizard number in substract

  1. #1

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

    Bizard number in substract

    Hello VBForums
    Hello every one
    Gentelmans .. please
    This code will make me crazy
    I tried to mix in this code for 3 days .. day and night .. but no good results
    I have a Table RECHANGE when i choose some product
    I choose some products and I record them in the INVOICE table
    For example : In RECHANGE table i have 100 pen
    My problem with this code for save .. if i choose and save in INVOICE table only ONE ( 1 ) product .. i choose for example 60 pen and i save .. I find in the table RECHANGE 40 pen (Perfectly substract)
    BUT .. if i choose two or three or some product for example ( 60 pen .. 15 rulers .. 26 notebook ) and i save them in INVOICE table .. the subtraction in RECHANGE table is not done correctly and gives the bizard number
    This is my code for save invoice in INVOICE table :
    Code:
    Private Sub Command1_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  InvoiceNum=" & (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
    Next
    For XN = 1 To .Rows - 1
    For j = 0 To .Cols - 1
    If (.TextMatrix(XN, j) = "") Then
    For JL = 0 To 4
    .Row = XN
    .Col = JL
    .CellBackColor = vbRed
    DoEvents
    Sleep 11
    Next
    Exit Sub
    End If
    Next
    Next
    For s = 1 To .Rows - 1
    RX.AddNew
    
    RX.Fields("InvoiceNum") = Text1.Text
    RX.Fields("InvoiceDate") = Text2.Text
    RX.Fields("CustomerName") = Combo1.Text
    RX.Fields("InvoiceType") = Combo2.Text
    
    RX!Code = .TextMatrix(s, 0)
    RX!ItemName = .TextMatrix(s, 1)
    RX!Quantity = .TextMatrix(s, 2)
    RX!Price = .TextMatrix(s, 3)
    RX!Sum = .TextMatrix(s, 4)
    
    
    DoEvents
    Set RS = DB.OpenRecordset("Select * From RECHANGE Where Code=" & Val(Trim$(MSHFlexGrid1.TextMatrix(i, 0))))
    If RS.RecordCount > 0 Then
    DB.Execute "UPDATE RECHANGE Set Quantity=" & RS![Quantity] - Val(Trim$(MSHFlexGrid1.TextMatrix(i, 2))) & " Where Code=" & _
    Val(Trim$(MSHFlexGrid1.TextMatrix(i, 0)))
    DoEvents
    End If
    
    RX.Update
    Next
    MsgBox " Invoicesuccessfully saved "
    ClearAll_TextBoxs
    Combo1.SetFocus
    Call Form_Resize
    Else
    MsgBox "Invoice already registered", vbCritical, "Warning"
    End If
    End With
    Command5.Enabled = True    'Command for find invoice by invoice number
    End Sub
    Thank you in advance for help
    Cordially
    MADA

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

    Re: Bizard number in substract

    Hi Mada,

    I told you in this thread you would get yourself tied up in knots.
    http://www.vbforums.com/showthread.p...93#post5217393

    in which table do you store the amount that is in Stock ?
    show the Table structure for example:
    AutoincrementID;Productname;Productprice;AmountOnStock

    sample to get Data from Table Products with Amounts on Stock
    Code:
    Sub DAOFindRecord()
    
    Dim rsArtikel        As Recordset
    
    
    Set rsArtikel = dbNordwind.OpenRecordset("Products", dbOpenTable)
    
       'Find your Product with the Autoincrement ID
      rsArtikel.Index = "PrimaryKey"
    
       'Find the Product details with input from textbox(Text1.Text)
       'press Enter and fill the Textboxes with details
       rsArtikel.Seek "=", Text1.Text
    
    ' get the Information you need from you Table in order to Add or Substract
      Label1.Caption = rsArtikel.Fields("Productname")
      Label2.Caption = rsArtikel.Fields("AmountOnStock")
    
      
       rsArtikel.Close
    
    End Sub
    this is the first step what you need to Add or Substact



    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.

  3. #3

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

    Re: Bizard number in substract

    Hiii Chris
    Thank you very very much for help
    Very nice to meet you again in this thread
    I have not understood so much I will organize my code and I will post them here
    Cordially
    MADA

  4. #4

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

    Re: Bizard number in substract

    Hello Chris
    Hello every one
    Gentelmans
    I have tried a lot but I have not completely successfully solved this problem
    Is what i can post my zip file here ..??
    Cordially
    MADA

  5. #5
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Bizard number in substract

    MADA, what you should do (in my opinion)
    clearly explain what your program is supposed to do
    what i think you want to do, is create invoices for goods you sold ?
    for that you need at least:
    a 'Products' table
    a 'Clients' table
    a 'Invoices' table
    a 'Invoice_Detail' table
    depending on the kind of business, the tax rules,the business rules ...etc
    there can be a lot more tables, what is really needed, only you knows
    (Suppliers table ? ,KindOfProducts table ? ,Tax table ?, etc...)

    so, if you can clearly explain what your program is supposed to do
    and you have a database with at least those 4 tables
    feel free to put that database here (compacted and zipped)

    if i remember well, you use an access db
    if so, do you have Access installed ?
    if so, what version

    before you write a single line of VB6 code
    you have to be able to clearly explain what the program is supposed to do
    and how you want the program to do it
    you need to be 100% sure you have a workable database
    do not put off till tomorrow what you can put off forever

  6. #6

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

    Re: Bizard number in substract

    Hello IkkeEnGij
    Have a good day
    Thank you very much master
    Thank you from my heart for your explanations and your ideas and your advice
    You have enlightened me the way
    In reality everything works very well .. only this problem quey subtract
    1- I open Form1 .. i click Command1 .. the number of invoice appears automatically dans Text1
    I choose Customer from Combo1 ..
    I make Dbl-Click on MSHFlexGrid1 .. then Form2 appers .. i choose product from Form2 by Dbl-Click on Form2
    Form1 appears ..
    I type Number of quantity and i make Key press ..
    Same procedure if i choose other product
    Finally i click on Command 2 for save invoice in Table named ( INVOICE )
    All goes very well
    But when i add this Query for substract product saved in Table INVOICE ..substract from Table RECHANGE ..i have probleme
    Query in Command 2 for save :
    Code:
    ''Query substract
    Dim i As Integer
    For i = 1 To .Rows - 1
    Set RS3 = DB3.OpenRecordset("Select * From RECHANGE Where Code=" & Val(Trim$(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0))), 2)
    If RS3.RecordCount > 0 Then
    DB3.Execute "UPDATE RECHANGE Set Quantity=" & RS3![Quantity] - Val(Trim$(MSHFlexGrid1.TextMatrix(i, 2))) & " Where Code=" & _
    Val(Trim$(MSHFlexGrid1.TextMatrix(i, 0)))
    DoEvents
    End If
    Next
    Thank you in advance for help
    Cordially
    MADA
    Last edited by MADA BLACK; Oct 10th, 2017 at 10:17 AM.

  7. #7
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Bizard number in substract

    Mada, please dont be offended
    but i really think you should google for:
    free invoicing software
    and
    free invoice software in access
    just to see some examples
    do not put off till tomorrow what you can put off forever

  8. #8

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

    Re: Bizard number in substract

    Thank you master
    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