Table Update from a form using VBA

Question

I am quite new to Access and am stuck at trying to update a record in the tblItems table, as if there was a typo in the description.

The form I am using allows the user to enter the Item number, which is stored in the table, in a text box (tbItemID).

The user then hits the Find button, and select the correct record in the tblItems table. The code I have then adds the relevant labels and text boxes, but the only data that is retrieved is that of the first record in the table. I have tried many offerings from this site and Youtube but none appear to work. Below is the code I have tried.

Private Sub btnFind_Click()

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblItems", dbOpenDynaset)

    rs.FindFirst "Item_ID=" & tbItemID

    rs.Edit
    
    tbItemID.Enabled = False
    
    lblDesc.Visible = True
    tbDesc.Visible = True
    tbDesc.Value = rs!Description
    
    lblUOM.Visible = True
    cbUOM.Visible = True
    cbUOM.Value = rs!UOM
    cbUOM.Enabled = False
    
    lblCost.Visible = True
    tbCost.Visible = True
    tbCost.Value = rs!Cost
    
    rs.Update
    rs.Close
    Set rs = Nothing
    
End Sub

Thanks for your help.

I have tried many different macros from the internet. My expectation is that the record from the Items Table is entered into the fields on the form and the user amends what is needed, and with a save button the new information is updated to the table.

Answer 1

Code is not "adding" labels and textboxes, it just sets properties of existing controls.

All records are retrieved but FindFirst does not work because Item_ID is a text field and text field requires apostrophe or quote delimiters around parameter (date/time field requires # character):
rs.FindFirst "Item_ID='" & tbItemID & "'"
or
rs.FindFirst "Item_ID=""" & tbItemID & """"
If data could contain an apostrophe (as in O'Rourke), use the latter.

It would be more efficient to retrieve only the one record of interest and eliminate FindFirst.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblItems WHERE Item_ID='" & tblItemID & "'", dbOpenDynaset)

If Not rs.EOF Then
...
End If

Could reduce code by using a BOUND form and BOUND controls. Instead of opening a recordset, set form RecordSource property. Labels associated with data controls don't need separate code to set Visible property, they will go with the associated control. Edits in BOUND controls are passed to table without additional code. Record edits are committed when: 1) table/query/form is closed or 2) when moving to another record or 3) run code to save. It's good practice (improves efficiency) to prefix controls with form/report object reference. Me is shorthand for the object code is behind. Use With wrapper and don't have to repeat prefix for each control.

Private Sub btnFind_Click()
With Me
    .RecordSource = "SELECT * FROM tblItems WHERE Item_ID='" & .tblItemID & "'"
    .tbDesc.Visible = True
    .cbUOM.Visible = True
    .cbUOM.Enabled = False
    .tbCost.Visible = True
End With    
End Sub

With this approach, controls used to enter search criteria should be UNBOUND and placed in form header. Can have additional controls bound to the fields. Set them Enabled No or Locked Yes and TabStop No if you want to prevent edit.

To open blank BOUND form, set its RecordSource in design with a statement that won't retrieve any records: SELECT * FROM tblItems WHERE Item_ID = 0;. Then use code to populate form with desired record(s).