Searching a record using KeyPress event textbox

For the previous articles we already had learned how to Create a connection and Inserting a record of a product in tbl_Products, One thing we must consider is that we don't like to insert a the same product with different quantities, in short we dont like a duplication of records.
For this code i will not give you the full code for preventing a duplication of record instead i will give you a code of searching a specific record.

Lets begin


for the existing form that we created on the module of inserting a product Double click the textbox named txtProduct, then the interface will change to code view on the upper right click the combobox and select keypress



--click on the image to enlarge


Then after that a function  will appear as like this


keypress function traces the letters numbers or character that you will input, keyascii is a integer that refer to the number value of the character you've pressed please refer to this link to see the value of the keys you want to trace http://www.asciitable.com/

now we want to trace if the user will press the enter key on the keyboard a integer value for the enter key is 13 so our code would be

If KeyAscii = 13 Then
' code for searching   
   

End If

for the next step is to create a code for searching
Private Sub txtProduct_KeyPress(KeyAscii As Integer)

    If KeyAscii = 13 Then
 'this if condition will check if the enter key on your keyboard is pressed
        Dim rstsearch As New ADODB.Recordset
'declaration of rstsearch as recordset for searching a record

        rstsearch.Open "Select * from tbl_Products where fld_ProductName  = '" & Me.txtProduct.Text & "'", con, 3, 3  
   'we open rstsearch for the following sql statements Select * from tbl_Products where     fld_ProductName 'the 'where clause we mean the we are searching for a specific record that will equal to txtProduct
      
        If rstsearch.EOF = False Then
         'this if condition checks if the product that we are searching is existing if it is existing the code below 'will execute

            Me.txtPrice.Text = rstsearch.Fields("fld_Price")
           ' assigns the corresponding value of fld_price to txtprice
            Me.txtStocks.Text = rstsearch.Fields("fld_stocks")
   ' assigns the corresponding value of fld_stocks to txtstocks
       Else
                 ' if the record does not exist then a messagebox will appear saying that the record does not exist
                  MsgBox "record does not exist"
   
       End If
    
End Sub


- if you think logically if you want to filter on inserting a product  or limit duplication you need to search for the specific product if it existing so it would be the combination of the search and insertion product

-- happy coding