-
Oct 6th, 2017, 02:33 PM
#1
Thread Starter
Hyperactive Member
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
-
Oct 8th, 2017, 02:56 AM
#2
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.
-
Oct 8th, 2017, 07:28 AM
#3
Thread Starter
Hyperactive Member
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
-
Oct 8th, 2017, 04:33 PM
#4
Thread Starter
Hyperactive Member
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
-
Oct 9th, 2017, 09:57 AM
#5
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
-
Oct 9th, 2017, 12:07 PM
#6
Thread Starter
Hyperactive Member
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.
-
Oct 10th, 2017, 04:20 AM
#7
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
-
Oct 10th, 2017, 10:18 AM
#8
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|