VBA Code for Recordset to pull information from Table to Form

Question

I am working on pulling information from a table into a Check In & Out form. We will be cataloging helmets using barcodes. With the way I have the form set up, scanning a barcode will automatically display the information for that helmet. Each helmet will then be timestamped when it is shipped out and again when it is returned. Below is the code I have so far.

Private Sub Search_Button_Click()
    
    Dim rst As DAO.Recordset
    Dim strsql As String
    
    strsql = "Select\* From Helmets Where UPC Code = " & _
             txt.ScanUPCCode.Value & ""
    
    Set rst = CurrentDb.OpenRecordset(strsql)
    
    If rst.EOF Then
        MsgBox "No data found"
        txt.ScanUPCCode.Value = Nothing
        txt.HelmetID = Nothing
        txt.School = Nothing
    Else
        txt.HelmetID.Vaule = rst.Fields("HelmetIDNumber")
        txt.School.Vaule = rst.Fields("School")
    End If
    
    rst.Close
    
    Set rst = Nothing
    
End Sub

Before posting code, at least make sure it compiles - for example txt.HelmetID.Vaule is clearly a typo. After you've fixed those issues, run your code and tell us exactly what the error is, and on which line of your code. FYI you do not clear a text box by assigning Nothing to it - that is for object references. Likely you want something like txt.School = "" — Tim Williams
– Tim Williams, Commented Nov 25 at 21:53
Also if the UPC value is not numeric then you need quotes - strsql = "Select * From Helmets Where UPC Code = '" & txt.ScanUPCCode.Value & "'" — Tim Williams
– Tim Williams, Commented Nov 25 at 21:54
Value is default property for data controls, don't even need to type it - as you did not for 2 controls. Be consistent. Aside from typos already mentioned, why is there a backslash in the SQL string? — June7
– June7, Commented Nov 25 at 22:47

June7 · Accepted Answer · 2025-11-27 23:39:06Z

This is very much along the lines of checking OUT/IN book at library. Might want to review MS Lending Library template.

Since HelmetID, UPC, School are associated data, really should not be saving UPC and School into OUT/IN log, just HelmetID (unless this association is subject to change and you need to save history). Scan UPC into a combobox and most (maybe all) of this code can be eliminated. Combobox would have a RowSource that includes HelmetID, UPC, School fields. UPC would display but value would be ID.

If you want to stick with posted code, considering two assumptions:

txt is a prefix for textbox names not followed by a period
UPC code field is a text data type

clean up code like:

Private Sub Search_Button_Click()
    
Dim rst As DAO.Recordset
Dim strsql As String

With Me    
    strsql = "Select * From Helmets Where UPC Code = '" & _
             .txtScanUPCCode.Value & "'"
    
    Set rst = CurrentDb.OpenRecordset(strsql)
    
    If rst.EOF Then
        MsgBox "No data found"
        .txtScanUPCCode.Value = Null
        .txtHelmetID.Value = Null
        .txtSchool.Value = Null
    Else
        .txtHelmetID.Value = rst.Fields("HelmetIDNumber")
        .txtSchool.Value = rst.Fields("School")
    End If
    
    rst.Close
    
    Set rst = Nothing
End With    
End Sub

Collectives™ on Stack Overflow

VBA Code for Recordset to pull information from Table to Form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related