how to trap if mch_rs!qtyh is zero

[jemz]

hello need help for this how can i trap if my mch_rs!qtyh is zero
example H1010 the quantity on hand is 10 then if i input quantity 10 then click the add button
so now my H1010 is zero.then if i input again the H1010 it should prompt that the quantity on hands is already zero...please help me on this how to trap if my quantity on hand is already zero....because i don't know how to trap if my quantity is already zero..hoping for your positive responds....

[vb5prgrmr]

Okay, you use the word "add" but actually the word would be "subtract" from what you are telling us... Now, please consider this fictitious table...

tblInventory
iInvID
vInvName
vInvDesc
iQty

and lets say it contains your H1010 in the vInvName field with a description of A Blue Widget, and a quantity of 10.

Now, when you sell or transfer these ten items from your inventory, you would update this quantity by say using an update statement...

strSQL = "UPDATE tblInventory SET iQty = [tblInventory].[iQty]-" & variablethatholdshowmanyweresold & " WHERE iInv = " & variablethatholdstheiInvID

Now, you question is, is how to know when the values of iQty changes or what it is? Well when you first pull the data from the database, you know it then. It resides within the recordset that you used to retrieve the information so that is where you would check to see if there is sufficient quantity to subtract from the inventory. Then after your update, you would want to Refresh your recordset so that it will be updated with the latest amounts...

Good Luck

[jemz]

hello thanks for the reply...it's in my form 10 i have problem in there i can't trap if my merchandise no. H1010 is already zero it will display on the grid ..
i want H1010 not to be display in the grid if the quantity on hand is already zero or other merchandise no. who's quantity is already zero..please help me..thanks in advance hoping for your positive responds.

[vb5prgrmr]

strSQL = "SELECT * FROM tblWhatever WHERE quantity > 0"

Good Luck

[jemz]

so where i put the select? in the merchandise keydown?...

if my quantity is > 0 so is there a message box i will put

[vb5prgrmr]

Well you know what!!! Instead of me trying to write this for you in the blind, how about you first post the code you have for loading the grid in the first place and we will work from there...

[jemz]

oki thanks for the patience i will write again and post the grid but how can i post the gui i don't know how to show you the gui...can you teach me how to attach the picture of my gui so that you can understand what i mean..hoping for your positive responds...more power to you

[vb5prgrmr]

Don't really need the pic, just the code but if you use the Go Advanced button you should be able to attach a pic...

Good Luck

[jemz]

oki try to look my H1010 the available stock is zero can you help me..how can i trap and put message box if the available stock is already zero and it should not display on the grid...try to look my attachment the H1010 available stock is zero but it will display to the grid..but i want not to be display on the grid after i keydown only the message box will prompt that this merchandise H1010 is already zero can you help me please...thanks in advance hoping for your positive responds...

[vb5prgrmr]

Those pictures don't tell me a thing... We need to see the code to be able to help you...

[jemz]

hi thanks for the patience...here's my code try to look in my sql...i put messagebox it works but the problem is that when i enter merchandise no.which is not in my database it goes to runtime can you help me on this,,..try to input merchandise no.H1010 then try to input merchandise no.which is not in my database...T1010...hoping for your positive responds...

Private Sub txtmchno_KeyDown(KeyCode As Integer, Shift As Integer)
m_mchno = Trim(UCase(txtmchno.Text))
 If KeyCode = 13 Then
   If Trim(txtcno.Text) = "C" Then
    MsgBox "You have not inputed the customer no.", vbCritical
    Call highlight(txtcno)
  Exit Sub
End If
  
If Trim(txtcno.Text) = " " Then
    MsgBox "You have not inputed the customer no.", vbCritical
    Call highlight(txtcno)
    Exit Sub
End If

 If Trim(txtmchno.Text) = "" Then
 
    MsgBox "You have not inputed the merchandise no.", vbCritical
    Call highlight(txtmchno)
    Exit Sub
 End If
 
 r = grd.Rows - 2
 
 For ctr = 1 To r Step 1
   x_mchno = Trim(grd.TextMatrix(ctr, 1))
 
 If m_mchno = x_mchno Then
   MsgBox "Sorry..duplicate entry" + "  " + m_mchno + "  " + "you already have purchase "
    txtmchno.Text = ""
   Call highlight(txtmchno)
   
   Exit Sub
  End If
 Next ctr

     
     sql = "select * from merchandise_table where mch_no = '" & m_mchno & "'"
     Set mch_rs = con.Execute(sql)
     
       If mch_rs!mch_qtyh <= 0 Then
         MsgBox "sorry this stock is already zero", vbCritical
         Exit Sub
         End If
         
     
     If mch_rs.BOF = True And mch_rs.EOF = True Then
      MsgBox "Sorry mchnno" + " " + m_mchno + " " + "not found in merchandise_table"
      Call highlight(txtmchno)
     Else
       lblqtyh.Caption = mch_rs!mch_qtyh
        Display
        Call highlight(txtqty)
        txtcno.Locked = True
        End If
        mch_rs.Close
     End If
End Sub

[jemz]

thanks for the patience try to look in my sql i put messgebox and it works but the problem is that when i input merchandise no.which is not in my database it will goes to runtime..can you help me on this try to input..H1010 after that try to input merchandise no. which is not in my database T1010..this will goes to runtime..hoping for your positive responds...

Private Sub txtmchno_KeyDown(KeyCode As Integer, Shift As Integer)
m_mchno = Trim(UCase(txtmchno.Text))
 If KeyCode = 13 Then
   If Trim(txtcno.Text) = "C" Then
    MsgBox "You have not inputed the customer no.", vbCritical
    Call highlight(txtcno)
  Exit Sub
End If
  
If Trim(txtcno.Text) = " " Then
    MsgBox "You have not inputed the customer no.", vbCritical
    Call highlight(txtcno)
    Exit Sub
End If

 If Trim(txtmchno.Text) = "" Then
 
    MsgBox "You have not inputed the merchandise no.", vbCritical
    Call highlight(txtmchno)
    Exit Sub
 End If
 
 r = grd.Rows - 2
 
 For ctr = 1 To r Step 1
   x_mchno = Trim(grd.TextMatrix(ctr, 1))
 
 If m_mchno = x_mchno Then
   MsgBox "Sorry..duplicate entry" + "  " + m_mchno + "  " + "you already have purchase "
    txtmchno.Text = ""
   Call highlight(txtmchno)
   
   Exit Sub
  End If
 Next ctr

     
     sql = "select * from merchandise_table where mch_no = '" & m_mchno & "'"
     Set mch_rs = con.Execute(sql)
     
       If mch_rs!mch_qtyh <= 0 Then
         MsgBox "sorry this stock is already  zero", vbCritical
         Exit Sub
         End If
         
     
     If mch_rs.BOF = True And mch_rs.EOF = True Then
      MsgBox "Sorry mchnno" + " " + m_mchno + " " + "not found in merchandise_table"
      Call highlight(txtmchno)
     Else
       lblqtyh.Caption = mch_rs!mch_qtyh
        Display
        Call highlight(txtqty)
        txtcno.Locked = True
        End If
        mch_rs.Close
     End If
End Sub

[vb5prgrmr]

Okay, I looked over your Form12, as it was the startup object, and it seems to work for me...

In that, if I put a part number in that does not exist, it gives the the correct error, and if I use a good part number, it displays a quantity of 1 in the grid and 7 in stock. I also changed F1010 to a quantity of zero and recieved what I believe to be the correct error message but I would say you need to add a little more information to that msgbox.

So at this point, everything seems to work okay but you say you are getting a runtime error... What is the error number and err.description?

Note: To make it easy to retrieve this information, you should put error handlers in all of your subs and functions... Something like this...

Private Sub Form_Load()

On Error GoTo Form_LoadError

'your code goes here

Exit Sub
Form_LoadError:

MsgBox "Form12 Form_Load " & Err.Number & ":" & Err.Description

End Sub

Good Luck

[jemz]

hello thanks for the reply...it's not in my form12 i have problem the code that i have shown to you is the form 10 i only shown the merchandise keydown the customer's keydown is no problem..form 10 is the customer's order..there i have problem in ordering merchandise in merchandise keydown...if i input merchandise which is not in database i get runtime...try to check my form 10 please...hoping for your positive responds...

[vb5prgrmr]

No error here...

Add error handling as I posted above...

Good Luck

[jemz]

it's says the either BOF. or EOF etc... that is the error in my form10...

try to input first the customer C0001 then merchandise no. H1010 then
try input quantity exactly the same in the available stock.example the available stock is 7 .then input the quantity of merchandise 7 then click add ...after is added input again H1010 so there will be message box"Sorry this stock is already zero"...but the problem is that when you input another merchandise like this which is not in my database T2222 here i get runtime that's is my problem it's says the either BOF. or EOF etc... hoping for your positive responds...

[vb5prgrmr]

I get your message box that says "sorry mchnno 123 not found in merchandise_table" but let me follow what you do...

Okay, when I input h1010 again, I get the message box that says "Sorry..duplicate entry H1010 you already have purchase"

Okay, now I click add and display clears, I start over with c0001 and type in h1010 and it displays record in grid with quantity of zero with no error or no notice. So I enter 1 and get the message box that says insufficient stock... So go back and enter 123 and get a message box of item not found in stock...

Add Error handling!!!! and change the option to break on unhandled errors...

Good Luck

[jemz]

thanks for the patience but have you tried to enter merchadise number that does not in my database...it get runtime on it if you enter merchandise no. which is not in my database...hoping for your positive repsonds...

[jemz]

hello sir please try this my form10... this is where i get runtime...try input this one sir c0001 for customer no. and this for merchandise no. H1010 so if the the available stock is zero so prompt msgbox "sorry this stock is already zero"...but my H1010 there sir have available stock example 7 please input quantity 7 also ,then please click add button..after that input again customer no. c0001 and merchandise no.H1010 so this will prompt now msgbox"sorry this stock is already zero"...and after it prompt try to input the merchandise no. like this which is not in my database. T5555 so this will get runtime....sir kindly change the form10 that i attache before to this newly attach form10. thanks in advance hoping for your positive responds...

[AndreRet]

The way I understand this (I think), you need to trap an error raised for stock you want to sell, but there is no stock on hand...

When populating your datagrid, first select the correct data to display -

strSQL = "SELECT * FROM tblWhatever WHERE quantity > 0"

mchrs.Open strsql

Set YourDatGridName.DataSource = mchrs

'This will load the grid with ONLY stock on hand more than 0

If a user enters an amount larger than that on hand, error trap by something like -

If text1.Text > mchrs!qtyh Then
     'Text1.Text is the quantity you want to sell
     Msgbox "You do not have enough stock on hand. You 
     only have " & mchrs!qtyh & " on hand.", vbOkOnly 
            Else
    'Your code here to sell the quantity and update database with the quantity left over
End If

I hope this has shed some light on the subject. Otherwise post all your code here and we will try to help out where we can.

[vb5prgrmr]

Okay, I finally reproduced your error (yeah! :)) and when this happens to you, click on debug. VB will then take you to the offending line...

If mch_rs!mch_qtyh <= 0 Then

but just four (4) lines below that line you have the error check needed....

If mch_rs.BOF = True And mch_rs.EOF = True Then

So, change the bof/eof check into an if end if in by itself and then put your <=0 check after that...

Good Luck

[jemz]

thaks for the patience...sir, so i am going to change this my

If mch_rs.BOF = True And mch_rs.EOF = True Then

to

If mch_rs!mch_qtyh <= 0 Then

sir what about if i input merchandise which is not in my database how can i prompt message msgbox"Sorry merchandise no. is not found" .if i change the

If mch_rs.BOF = True And mch_rs.EOF = True Then

..hoping for your positive responds...

[vb5prgrmr]

Okay, before you check for a quantity, check for records first...

So when users enters something and searches, your process would be something like this psuedo code...

strSQL = "SELECT * FROM tablename WHERE fieldname = '" & user_input & "'"
open rs
If rs.recordcount <> 0 and rs.bof = false and rs.eof = false then
  'okay we have records
  if rs.fields("quantityfieldname").Value <= 0 Then
    'okay, no stock available so notify user
  else
    'stock available
  end if
else
  'no records so notify user
end if

So once again, first check to see if you have returned any records, then check to see if you have a value to work with.

Also, what I originally said was this...
>So, change the bof/eof check into an if end if in by itself and then put your <=0 check after that...

perhaps I should have explained it better as I hope I have this time...

Good Luck

[jemz]

okey sir thanks for the patience and for the reply...i will write again if i have some doubt..thank you sir more power to you..
can i ask sir can i delete my attachment?and how can i delete this sir.
hoping for your positive responds

[vb5prgrmr]

Not sure if you can delete it... Post that or search first in the daniweb community feedback forum and if you don't find an answer, then post in that forum...

Good Luck

[jemz]

okey sir thank you for the reply.sir can you help me on the sql statement how to use the insert into Table.....can you show to me on my form2 the add button i want to use the insert statement but i don't know how to use it..kindly show me sir how to use it.hoping for your positive responds.

[vb5prgrmr]

Okay, there are three different insert into statements. One of them will create a new table from and existing table, another will insert records from another table into an existing table, and the third will simply put information into a table, I believe this is the one you want. The other two you can create through the access designer...

strSQL = "INSERT INTO tablename(stringfieldname, numericfieldname, datefieldname) VALUES('" & stringvariable & "', " & numericvariable & ", #" & datevariable & "#)"

Note: in the future, you will find that access is the only database that requires the date to be surrounded by the pound symbol (#).

Good Luck

[jemz]

sir i am confuse with the code but here's the form i want to change it to "insert into"

sir this is my code in my cmdadd..in my sql sir i want to change it to "insert into",can you show me sir how... hoping for your positive responds...

Option Explicit

Public con As ADODB.Connection
Public cmd As ADODB.Command
Public mch_rs As ADODB.Recordset
Public sql As String
Public m_mchno As String, i As Integer



Private Sub cmdadd_Click()


If Len(Trim(txtmchno.Text)) <> 5 Then
   MsgBox "The field is empty or you have invalid input... please input 5 characters only..", vbInformation, "Please input merchandise number"
   Call highlight(txtmchno)
   Exit Sub
   End If
   

If Trim(txtname.Text) < "A" Then
    MsgBox "The field is empty or you have invalid input", vbInformation, "Please input merchandise name"
   Call highlight(txtname)
   Exit Sub
   End If
   
   
If Trim(LCase(txtumsr.Text)) <> "pcs" And Trim(LCase(txtumsr.Text)) <> "ream" Then
    MsgBox "Input only!!!  ream  or  pcs", vbInformation, "Please input unit measure"
   Call highlight(txtumsr)
   Exit Sub
   End If
   
   
If Trim(UCase(txtumsr.Text)) <> "PCS" And Trim(UCase(txtumsr.Text)) <> "REAM" Then
    MsgBox "Input only!!!  ream  or  pcs", vbInformation, "Please input unit measure"
   Call highlight(txtumsr)
   Exit Sub
   End If
      
   
   

If Val(txtuprice.Text) <= 0 Then
    MsgBox "The field is empty or you have invalid input...", vbInformation, "Please input unit price"
    Call highlight(txtuprice)
    Exit Sub
    End If
    
 
 
 If Val(txtqtyh.Text) <= 0 Then
         MsgBox "The field is empty or you have invalid input...", vbInformation, "Please input quantity on hand"
         Call highlight(txtqtyh)
         Exit Sub
         End If
 

  

sql = "select * from merchandise_table where mch_no ='" & m_mchno & "'"
     
Set mch_rs = con.Execute(sql)


If mch_rs.BOF = True And mch_rs.EOF = True Then

 If mch_rs.State = adStateOpen Then
  mch_rs.Close
  End If
  
   check_table
   
Else

  MsgBox "Duplicate entry...Try another one!!!", vbCritical, "Warning...Duplicate!!!"
  clear1
  
  Exit Sub
  End If
  




End Sub

Private Sub cmdcancel_Click()
clear1
End Sub

Private Sub cmdexit_Click()
Unload Me
End Sub

Private Sub Form_Activate()
txtmchno.SetFocus
End Sub

Private Sub Form_Load()

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set mch_rs = New ADODB.Recordset

With con
  
   .ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\project.mdb"
   .Open
   
End With




End Sub

Private Sub txtmchno_Click()
clear1
End Sub

Private Sub txtmchno_KeyDown(KeyCode As Integer, Shift As Integer)


m_mchno = Trim(txtmchno.Text)

If KeyCode = 13 Then
  
 
  If Len(Trim(txtmchno.Text)) <> 5 Then
   MsgBox "The field is empty or you have invalid input... please input 5 characters only..", vbInformation, "Invalid Input...Input again merchandise no."
   Call highlight(txtmchno)
   Exit Sub
   End If
   
 
 
     
     sql = "select * from merchandise_table where mch_no = '" & m_mchno & "'"
     
     
     Set mch_rs = con.Execute(sql)
     
     
     If mch_rs.BOF = True And mch_rs.EOF = True Then
       txtname.SetFocus
     Else
       
        display
        End If
        
      End If
      

End Sub



Public Sub display()


txtname.Text = mch_rs!mch_name
txtumsr.Text = mch_rs!mch_umsr
txtuprice.Text = mch_rs!mch_uprice
txtqtyh.Text = mch_rs!mch_qtyh


End Sub


Public Sub check_table()

With cmd
 .ActiveConnection = con
 .CommandText = "merchandise_table"
 .CommandType = adCmdTable
 
End With

 
With mch_rs

  .CursorLocation = adUseClient
  .CursorType = adOpenDynamic
  .LockType = adLockPessimistic
  .Open cmd
  
End With


With mch_rs
.AddNew
!mch_no = Trim(UCase(m_mchno))
!mch_name = Trim(StrConv(txtname.Text, vbProperCase))
!mch_umsr = Trim(LCase(txtumsr.Text))
!mch_qtyh = Val(txtqtyh.Text)
!mch_uprice = CDbl(txtuprice.Text)
!mch_rstatus = "1"
.Update
.Close

End With
MsgBox "Record has been added to the merchandise_table  ...", vbInformation, "Congratz!!!"
clear1
End Sub



Public Function highlight(txt As TextBox)
    With txt
        .SelStart = 0
        .SelLength = Len(.Text)
        .SetFocus
    End With
End Function



Public Sub clear1()
txtmchno.Text = ""
txtname.Text = ""
txtumsr.Text = ""
txtuprice.Text = ""
txtqtyh.Text = ""
Call highlight(txtmchno)
End Sub

Private Sub txtmchno_KeyPress(KeyAscii As Integer)
Call mchnotrap(KeyAscii)
End Sub



Public Sub mchnotrap(KeyAscii As Integer)

 
If KeyAscii >= 33 And KeyAscii <= 44 Or KeyAscii = 45 Or KeyAscii = 46 Or KeyAscii = 47 Or KeyAscii = 63 Or KeyAscii = 64 Then
  
  KeyAscii = 0
  MsgBox "Invalid input only numbers and letters are allowed", vbInformation, "Invalid Input...Input Again merchandise number"
  Exit Sub
  End If
  
  
  
If KeyAscii >= 58 And KeyAscii <= 62 Or KeyAscii >= 91 And KeyAscii <= 96 Then
   KeyAscii = 0
   MsgBox "Invalid input only numbers and letters are allowed", vbInformation, "Invalid Input...Input Again merchandise number"
   Exit Sub
   End If
  
  
  
 If KeyAscii >= 123 And KeyAscii <= 127 Then
   KeyAscii = 0
   MsgBox "Invalid input only numbers and letters are allowed ", vbInformation, "Invalid Input...Input Again merchandise number"
   Exit Sub
   End If

[vb5prgrmr]

Okay, around line 69 above, you would build your insert string and use either your command or connection object to execute the insert statement. So what you do is this...

strSQL = "INSERT INTO tablename(list fields here seperated by commas) values(now in the same order as the field list and seperated by commas you list the values you want to insert for each file and as noted above wrap strings with the single ticks number are not wrapped by those single ticks and date are wrapped by pound signs)
adoCn00.Execute strSQL

Now that is the best way to do an insert...

However you can do the rs.addnew/rs.field("fieldname").value = text1.text/rs.update starting at the same line number but this takes longer and can cause problems with multiuser systems depending upon the cursor type...

Good Luck

[jemz]

sir thanks for the patience..sir i am confuse with the values..i want that the user will only decide what to put in the textfield...can you make me the values sir can you give me example....please try my form2 and please let me see the insert into statement..thank you so much in advance hoping for your positive responds.