-
Sep 23rd, 2017, 02:37 AM
#1
Thread Starter
Hyperactive Member
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.
-
Sep 23rd, 2017, 03:00 AM
#2
Re: Subtract the number of the saved product
Your unformatted/unidented code is unreadable.
Please format/indent the code. Thank.
-
Sep 23rd, 2017, 04:44 AM
#3
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
-
Sep 23rd, 2017, 05:39 AM
#4
Thread Starter
Hyperactive Member
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
-
Sep 23rd, 2017, 05:47 AM
#5
Thread Starter
Hyperactive Member
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
-
Sep 23rd, 2017, 08:33 AM
#6
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
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
-
Sep 23rd, 2017, 09:40 AM
#7
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
-
Sep 23rd, 2017, 10:05 AM
#8
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.
-
Sep 23rd, 2017, 10:29 AM
#9
Thread Starter
Hyperactive Member
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
-
Sep 23rd, 2017, 10:33 AM
#10
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|