Help!

[ryan311]

how can i delete the record in my Sold To table if the quantity is = 0
heres my code in my command button

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\Inventory System\PharmacyInventory.mdb;Jet OLEDB:System Database=system.mdw;", "admin", ""
Set rs = New ADODB.Recordset
cn.Execute "INSERT INTO CustomerReturn (cno, pcode, quantity, unit, pdescription, ornumber, [date]) VALUES ('" + Combo1.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "',#" + Text6.Text + "#)"
rs.Open "select * from Product where pcode='" & Text1.Text & "'", cn, adOpenKeyset, adLockPessimistic
If rs.RecordCount > 0 Then
rs.Fields![pstock] = Val(rs.Fields![pstock]) + Val(Text2.Text)
rs.Update
End If
MsgBox "Return Successfully", vbInformation
Set rs1 = New ADODB.Recordset
rs1.Open "Select * from Soldto where cno='" & Combo1.Text & "'", cn, adOpenKeyset, adLockPessimistic
If rs1.RecordCount > 0 Then
rs1.Fields![quantity] = Val(rs1.Fields![quantity]) - Val(Text2.Text)
rs1.Update
Call grd_Data_Loader
End If
If Val(rs1!quantity) = 0 Then
rs1.Delete
rs1.Requery
End If
Combo1.Text = ""
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
Call grd_Data_Loader

please help me thanks a lot!

[aktharshaik]

R u getting any error from the procedure mentioned by you?
If yes then what is the error.
or may be try this
Open the query in Dynamic mode

rs1.Open "Select * from Soldto where cno='" & Combo1.Text & "'", cn, adOpenDynamic, adLockOptimistic

[ryan311]

theres no error sir, my problem is only w/ that i want to delete the certain record if the value of my quantity in soldto table is equals to zero how?

[aktharshaik]

I think that the data type of your quantity field is text. you have to typecast it to number and then check for the records where quantity = 0 and delete them.

the following query will delete all records where quantity is = 0

cn.Execute " DELETE from Soldto where val(quantity)=0"

or if fora a particular cno then u can write

cn.Execute " DELETE from Soldto where val(quantity)=0 and cno = '" & Combo1.Text & "'"

[ryan311]

youve got it but my mshflexgrid did not update :(

[aktharshaik]

call grd_Data_Loader again after deleting the record

[ryan311]

take a look in my customer return table sir that is my problem!

i want to automatic delete if the quantity is zero

[aktharshaik]

In the grd_Data_Loader() after opening the connection put this statement.

Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\PharmacyInventory.mdb;Jet OLEDB:System Database=system.mdw;", "admin", ""

    cn.Execute " DELETE FROM CustomerReturn WHERE VAL(quantity) = 0 "

[ryan311]

wow your so great thanks, also i have 1 problem hehehehehe, sori because its my first time in sql in visual basic

how can i disabled my text12.text if the record of the quantity is null or zero?

Private Sub Combo1_Click()
Set rs2 = New ADODB.Recordset
rs2.Open "select * from Product where pcode='" & Combo1.Text & "'", cn, adOpenKeyset, adLockPessimistic
Set MSHFlexGrid1.DataSource = rs2
If rs2.RecordCount > 0 Then
 Text17.Text = Date
 Text4.Text = rs2!price
End If

End Sub

that is my code in my combo text.

[aktharshaik]

Private Sub Combo1_Click()
    Set rs2 = New ADODB.Recordset
    rs2.Open "select * from Product where pcode='" & Combo1.Text & "'", cn, adOpenKeyset, adLockPessimistic
    Set MSHFlexGrid1.DataSource = rs2
    If rs2.RecordCount > 0 Then
        Text17.Text = Date
        Text4.Text = rs2!price
        Text12.Enabled = VAL(rs2!pstock) > 0
    End If
End Sub

[ryan311]

invalid use of null that is my error i mean is if the value of the pstock in my product table is 0 then the message box will come out "You Cannot Buy This One"

how can i?

[aktharshaik]

Private Sub Combo1_Click()
    Set rs2 = New ADODB.Recordset
    rs2.Open "select * from Product where pcode='" & Combo1.Text & "'", cn, adOpenKeyset, adLockPessimistic
    Set MSHFlexGrid1.DataSource = rs2
    If rs2.RecordCount > 0 Then
        Text17.Text = Date
        Text4.Text = rs2!price
        If IsNull(rs2!pstock) = True Then
            Text12.Enabled = False
            MsgBox "You Cannot Buy This One" 
            Exit sub
        ElseIf VAL(rs2!pstock) <= 0 Then
            Text12.Enabled = False
            MsgBox "You Cannot Buy This One" 
            Exit Sub
        Else
            Text12.Enabled = True
        End If
    End If
End Sub

[ryan311]

thnx its work 1 question for example the enough quantity left in my database is 1 if he/she input more than 1 quantity then the messagebox should affair "Out Of Stack" heres my code in my command

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\Inventory System\PharmacyInventory.mdb;Jet OLEDB:System Database=system.mdw;", "admin", ""
Set rs = New ADODB.Recordset
cn.Execute "INSERT INTO CustomerReturn (cno, pcode, quantity, unit, pdescription, ornumber, [date]) VALUES ('" + Combo1.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "',#" + Text6.Text + "#)"
Set rs2 = New ADODB.Recordset
rs2.Open "select * from SoldTo where cno='" & Combo1.Text & "'", cn, adOpenDynamic, adLockPessimistic
If rs2.RecordCount < 0 Then
rs2.Fields![quantity] = Val(rs2.Fields![quantity]) - Val(Text2.Text)
rs2.Update
End If
Set rs1 = New ADODB.Recordset
rs1.Open "select * from Product where pcode='" & Text1.Text & "'", cn, adOpenDynamic, adLockOptimistic
If rs1.RecordCount < 0 Then
rs1.Fields![pstock] = Val(rs1.Fields![pstock]) + Val(Text2.Text)
rs1.Update
End If
Combo1.Text = ""
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
MsgBox "Return it Successfully", vbInformation
Call grd_Data_Loader

[ryan311]

and 1 problem also my other mshflexgrid did not update in my customer return and delivery how can i update that?

[ryan311]

sir help me in my customer return and delivery the mshflexgrid did not update still the same how can i update them? if i want to update my product table in sold to i want to update it all same in customer return and delivery how?