Table Update from a form using VBA

ghz 7months ago ⋅ 96 views

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. tblItems

The form I am using allows the user to enter the Item number, which is stored in the table, in a text box (tbItemID). frmItemAmend 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.

Expected information

Answers

It seems like you're trying to find a record in the tblItems table based on the Item_ID field using the value entered in the tbItemID text box. However, the issue you're encountering could be due to the way you're opening the recordset and finding the record.

Here's how you can modify your code to properly find the record based on the Item_ID:

Private Sub btnFind_Click()

    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    ' Build SQL query to find the record
    strSQL = "SELECT * FROM tblItems WHERE Item_ID = '" & Me.tbItemID & "'"
    
    ' Open recordset based on the query
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    ' Check if record found
    If Not rs.EOF Then
        ' Move to the first record (just in case)
        rs.MoveFirst
        
        ' Display record information on the form
        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
    Else
        ' No record found, display a message or handle it accordingly
        MsgBox "Record not found!", vbExclamation
    End If
    
    ' Close the recordset
    rs.Close
    Set rs = Nothing
    
End Sub

In this code:

  • We're building an SQL query to find the record in the tblItems table where the Item_ID matches the value entered in the tbItemID text box.
  • We're then opening a recordset based on this query.
  • If a record is found, we're displaying its information on the form. Otherwise, we're showing a message indicating that the record was not found.
  • Finally, we're closing the recordset.
#vba