Updating Query Object in List Box using a Form. (MS-Access)

Question

The Objective is to make a form that has queried all the objects of a table that are defined as 'active'. That query is displayed as a list box on the form. I would like the user who accesses this form to be able to select an object of that query, and then update the table by selecting the action from a second list box, and then clicking the submit button.

Visually:

The on click VBA I assume will look something like this:

Private Sub ActionAdminbtn_Click()
Set Actiontbl = CurrentDb.OpenRecordset("SELECT * FROM [Actiontbl]")
Actiontbl.Edit
Actiontbl![Progress] = Me.AdminActionSelect
Actiontbl.Update
Actiontbl.Close
Me.Refresh
End Sub

This almost works for me, however I'm missing the line of code that selects the object from the list box. Right now, once clicking the action and submitting, I only change the first record in the table. Not the desired one. Any Suggestions on how to modify the selected object?

Edit below to include more: Here's my table I'm trying to edit.

Parfait Parfait · Accepted Answer · 2018-07-05T20:44:38

Assuming your first listbox is bound to the unique key, ID, of your screenshot (hidden on form), consider an update SQL query with WHERE clause. Below demonstrates a parameterized query called using QueryDef object.

SQL (save as a stored query)

PARAMETERS [ProgressParam] TEXT(255), [ActionIDParam] Long;
UPDATE [Actiontbl] SET [Progress] = [ProgressParam]
WHERE ID = [ActionIDParam]

VBA

Private Sub ActionAdminbtn_Click()
    Dim qdef As QueryDef

    Set qdef = CurrentDb.QueryDefs("myUpdateQuery")
    ' BIND PARAMS
    qdef![ProgressParam] = Me.AdminActionSelect
    qdef![ActionIDParam] = Me.FirstListBoxName

    ' EXECUTE ACTION
    qdef.Execute dbFailOnError

    Set qdef = Nothing

    Me.Requery
End Sub

Alternatively, if you want to stay with your Recordset Update approach, open the query conditioned to ActionID item, still assuming listbox's value is bound to hidden ID field.

Private Sub ActionAdminbtn_Click()
   Dim strSQL As String
   Dim Actiontbl As Recordset

   strSQL = "SELECT * FROM [Actiontbl] WHERE ID=" & Me.FirstListBoxName

   Set Actiontbl = CurrentDb.OpenRecordset(strSQL)

   Actiontbl.Edit
   Actiontbl![Progress] = Me.AdminActionSelect
   Actiontbl.Update
   Actiontbl.Close

   Set Actiontbl = Nothing

   Me.Refresh
End Sub

Updating Query Object in List Box using a Form. (MS-Access)

2 Answers