MS Access VBA textbox search and submit

Question

I am working on a point of sale program. I have a form with tabs, the main tab is my register part. Right now I have a dependent dropdown box that searches for the product off another table that has all my products listed and after I click add product it automatically adds it to the subform for calculation. I am trying to add a barcode scanner option to this process. I have a textbox that that scanner will submit the product code on it and add it to the subform, just like the dropdowns I have. I having problems looking up the product code on the other table to add it to the subform for calculations. Below is my code

Private Sub txtscan_AfterUpdate()
If (txtScan & vbNullString) = vbNullString Then Exit Sub
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblProducts", dbOpenDynaset)
rst.FindFirst "[ProductCode]='" & Me![txtScan] & "'"
If rst.NoMatch Then
    MsgBox "Sorry, no such record '" & txtScan & "' was found.", _
           vbOKOnly + vbInformation
Else
DoCmd.RunCommand acCmdSaveRecord
Me.sfrmSalesDetail.Form.Requery
Set rst = CurrentDb.OpenRecordset("tblSalesDetail", dbOpenDynaset)
Forms!frmMain.sfrmSalesDetail.Form.txtProductName.SetFocus
Me.cmdAddProduct.SetFocus
With rst
    .AddNew
    .Fields("SaleID") = Me.txtSaleID
    .Fields("ProdCategoryID") = Me.cboProductCategory
    .Fields("ProductID") = Me.cboProductID
    .Fields("ProductCode") = Me.cboProductID.Column(1)
    .Fields("ProductDesc") = Me.cboProductID.Column(2)
    .Fields("ProdColor") = Me.cboProductID.Column(3)
    .Fields("ProdSize") = Me.cboProductID.Column(4)
    .Fields("UPC") = Me.cboProductID.Column(5)
    .Fields("SalesPrice") = Me.cboProductID.Column(6)
    .Fields("QuantitySold") = 1
    .Fields("ItemExt") = 1 * Me.cboProductID.Column(6)
    .Fields("ItemTotal") = 1 * Me.cboProductID.Column(6)
    .Update
End With

Me.sfrmSalesDetail.Form.Requery
rst.Close
Set rst = Nothing

Me.sfrmItemsSaleAmount_NonInsurance.Requery
cmdCalculateOrderTotal_Click
DoCmd.RunCommand acCmdSaveRecord

' Me.Recordset.Bookmark = rs.Bookmark End If rst.Close txtScan = Null End Sub

What is the error? What is not working? Which part of the code do you have issue with? The rs.FindFirst / rs.NoMatch part? — Parfait

Parfait Parfait · Accepted Answer · 2016-06-29T20:11:40

Consider using DLookUp or DCount of the Domain aggregate family of functions to do the lookup. No need to open a recordset. Hence, change the If logic of conditional to use DLookup. Below Nz() is used in case a null answer is returned.

If Nz(DLookUp("ProductCode", "tblProducts", "ProductCode='" & Me![txtScan] & "'")) = "" Then
    MsgBox "Sorry, no such record '" & txtScan & "' was found.", _
           vbOKOnly + vbInformation
Else
...

Alternatively, with DCount:

If Nz(DCount("*", "tblProducts", "ProductCode='" & Me![txtScan] & "'")) = 0 Then
    MsgBox "Sorry, no such record '" & txtScan & "' was found.", _
           vbOKOnly + vbInformation
Else
...

MS Access VBA textbox search and submit

1 Answers