Scan a barcode to Update a value on subform record

[john_gringo]

Hi
I got an Order Table and OrderDetails table.
I have an Order form with a "BarCode" text box and a subform OrderDetails.
I need some VBA code "AfterUpdate" for the BarCode text box to update the field CheckBox in the subform when the product's barcode is equal to the BarCode textbox.
As you might understand I need to scan the product itself and update the record of the Order.

Here is a db attached.
Thanks

 

[Gasman]

Start here?

Search results for query: scan barcode

 

[June7]

AfterUpdate will only trigger with Enter or Tab out of textbox. Does your scanner automatically send Enter with scan? Otherwise, try Change event. Something like:

Dim lngBC As Long
If Not IsNull(Me.BarCode) Then
    lngBC = Me.BarCode
    With Me.SubOrderDetails
        .SetFocus
        DoCmd.FindRecord "Field2=" & lngBC
        If Not .Form.Recordset.NoMatch Then .Form.Field3 = True
    End With
End If
Me.BarCode.SetFocus

Rats! I really don't like Change event, never used it. I hope your scanner will send Enter.

Really should use more meaningful field names.

 

[john_gringo]

yes, my scanner sends an enter.

 

[June7]

Edited previous post probably while you were posting, review again.

 

[john_gringo]

I put this code...

Private Sub BarCode_AfterUpdate()
    Dim IdVal As Integer
    Dim strSQL As String

    strSQL = "SELECT ID, OrderId, BarCode " & _
             "FROM OrderDetails " & _
             "WHERE (((OrderId)=[Forms]![Order]![ID]) AND ((BarCode)=[Forms]![Order]![BarCode]));"

    ' Use DLookup to retrieve the ID value from the query
    IdVal = Nz(DLookup("ID", "OrderDetails", "((OrderId)=" & [Forms]![Order]![ID] & " AND (BarCode)=" & [Forms]![Order]![BarCode] & ")"), 0)

    If IdVal > 0 Then
    
        DoCmd.RunSQL "UPDATE OrderDetails SET [Check] = Yes WHERE (ID=" & IdVal & " AND [Check]=No);"
    Else
        MsgBox "No such Barcode"
    End If
    Me!BarCode = ""
End Sub

It is working but I don't know if it is the correct approach.
Is there a way to have a recordset from the subform?

 

[June7]

Actually, I did more testing and it doesn't work properly - the NoMatch criteria fails.

Yes, can reference subform recordset. I was trying not to use that approach but it may be needed. Will try again.

 

[john_gringo]

Really should use more meaningful field names.

I did... this is just a sample here is the updated.

 

[john_gringo]

Actually, I did more testing and it doesn't work properly - the NoMatch criteria fails.

Yes, can reference subform recordset. I was trying not to use that approach but it may be needed. Will try again.

Can you see the updated one here attached in my previous reply?

 

[June7]

Yes, I can see it but haven't downloaded.

Users do not need to know the autonumber ID fields. Don't even need to see. Should at least lock the textboxes and set TabStop to No.

I have code that works with form RecordsetClone to edit field. Unfortunately, it puts focus on subform and I cannot get focus back to the main form. Annoying.

Then I set all main form controls except BarCode, (but including subform container) TabStop No and main form Cycle to CurrentRecord. Now this works as desired.

Private Sub BarCode_AfterUpdate()
Dim lngBC As Long
If Not IsNull(Me.BarCode) Then
    lngBC = Me.BarCode
    With Me.SubOrderDetails.Form.RecordsetClone
        .FindFirst "Field2=" & lngBC
        If Not .NoMatch Then
            .Edit
            !Field3 = True
            .Update
        End If
    End With
End If
Form_Order.BarCode = Null
End Sub

Should probably have UNBOUND combobox on main form to search for and navigate to order.

 

[john_gringo]

So this is how you get to declare subform data as recordsets.
With Me.SubOrderDetails.Form.RecordsetClone

Works fine ....... Thanks