How to use a combo box to fill mutltiple field in a subform in MS-Access?

0
votes

I have a sub form in Access that allows a user to select records using check boxes. In addition to this sub form there is a combo box with a list of names.

Is it possible to select a few records using the check boxes and assign a name to them with a press of a button?

The button is called [assignButton] , the sub form is called [list subform] and the combo box is called [personCombo] .

This seems like a simple task, but I am unfamiliar with how the VBA for the button triggering this kind of action would be written?

Here is an example of my form

All records are bound to an existing table called "tbl_jobs" this table has the same amount of rows and columns as the subform in the screenshot

1
vbams-accesssubform
Would be helpful if you could add some code snippets or examples of the forms – RK1
Can you share your tables/queries structure on which your sub form is based? – jbud
Please take a look at the update I posted – user11148708
That is essentially what my form looks like the "Available People" combo box is coming from just a table called "tbl_people" – user11148708
Please be more specific. "Assign a name" is not specific enough. Do you mean for each of the selected records to have the Person_Name field populated with the named from the combobox? – C Perkins

1 Answers

0
votes

Disclaimer: The following code is only intended to illustrate how to update the subform rows, so it includes no error handling or other common process code. Also notice that the SQL statement has no additional criteria in the WHERE clause, primarily because the question lacks the subform's actual RecordSource query and/or information about how the subform table is prepared and/or filtered for each time the form is loaded.

Private Sub AssignPerson_Click()
  Dim qry as QueryDef
  Set qry = CurrentDb.CreateQueryDef("", _
    "UPDATE tbl_jobs SET Person_Name = [ParamName]" & _
    " WHERE [Select] = True")
  qry.Parameters("ParamName") = PersonCombo.Value
  qry.Execute

  Me.list_subform.Requery 'Ensure subform displays updated rows
End Sub

Really this question is borderline "Too broad" for Stackoverflow. There are multiple ways to accomplish what you want, and your problem is one of design, coding and understanding. But in the end, this model code is rather simple and satisfies the basic requirements so I feel it not worth nitpicking.