Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Recordset query - Visual Basic

Hi,

I have a simple form that returns a product's description and cost based on a barcode being scanned.

This for works fine when the barcode exists in the Inventory table but if the barcode doesn't exit the code is supposed to run an error trap that displays a message box.

Currently it just jumps down to the next line. The rsEnq.NoMatch doesn't seem to be catching the 'NoMatch'

Code below. Any help greatly appreciated.
Dim mydb As Database, rsEnq As Recordset
    Dim sqlBarCode As String
       
    sqlBarCode = "SELECT DISTINCTROW tblInventory.BarCode, tblInventory.Description, tblInventory.SalePrice "
    sqlBarCode = sqlBarCode & "FROM tblInventory "
    sqlBarCode = sqlBarCode & "WHERE (tblInventory.BarCode ='" & Me.txtBarCode1.Text & "');"
    
    ' Create database.
    Set mydb = DBEngine.Workspaces(0).Databases(0)

    ' Create dynaset.
    Set rsEnq = mydb.OpenRecordset(sqlBarCode, DB_OPEN_SNAPSHOT)

    If rsEnq.NoMatch Then
        MsgBox ("BarCode was not recognised. Please write down your Name and the product taken in the book provided."), 0 + 64, "Error"
        GoTo endpgm
    End If
            
    ' Populate text box controls.
    On Error Resume Next
    Me![txtDesc1].Value = rsEnq.Fields("[Description]").Value
    Me![txtCost1].Value = rsEnq.Fields("[SalePrice]").Value
    
    mydb.Close
    
endpgm:

Me.txtBarCode2.Locked = False
Me.txtBarCode2.SetFocus

Open in new window

0
itmtsn
Asked:
itmtsn
  • 2
1 Solution
 
GrahamSkanRetiredCommented:
In DAO, NoMatch is set when a Seek or a Find doesn't find a matching record.

Test the recordset so:

   If rsEnq.EOF or RS.BOF Then
        MsgBox ("BarCode was not recognised. Please write down your Name and the product taken in the book provided."), 0 + 64, "Error"
        GoTo endpgm
    End If

   
0
 
itmtsnAuthor Commented:
The RS.BOF gave me a run time 424 error but just using rsEnq.EOF worked perfectly.

Thanks very much :)
0
 
GrahamSkanRetiredCommented:
Thanks.
That was a typo.  I intended to put

If rsEnq.EOF or rsEnq.BOF Then

as is recommended, but in practice I find that the .EOF test on its own works fine.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now