CODEWITHSUNDEEP
vbams-access
kitkor
kitkor

Reputation: 15

Edit a field in record(s) selected from a listbox with a value from a combobox

I'm working on a way to keep track of the workflow of some paper forms in my office. So far, I have a form with a listbox that will display all incomplete paperwork. In order to keep track of where the physical paperwork moved, I would like to highlight the record(s) in the list and assign them to an employee -- editing the field "QCByName" in tblForms from its current value (most often blank) to the value selected in a combobox that's pulling the options from tblStaff.

Form with listbox and combobox selection:

enter image description here

I'm having trouble with the VBA code for the command button, though. I have a decent working example from an older database someone else designed but haven't been successful in adapting the code to my similar situation. This post seemed helpful, but I can't get it to work.

If I try to assign one row in the list box to someone, I get a run-time error:

3075: syntax error (missing operator) in query expression 'ID=15T362'

Where 15T362 is the FormNumber row I selected. The listbox is unbound, I think, but the primary key and first (hidden) field is the ID.

Here's the current code. Any ideas what I'm doing wrong? Many thanks.

Private Sub cmdAssigntoQC_Click()
    Dim strSQL As String
    Dim tblForms As Recordset

If IsNull(Combo5) Then
    MsgBox "No Changes Made"

Else
    strSQL = "SELECT * FROM [tblForms] WHERE ID=" & List0
    Set tblForms = CurrentDb.OpenRecordset(strSQL)
    tblForms.Edit
    tblForms![QCByName] = Me.Combo5
    tblForms.Update
    tblForms.Close

Set tblForms = Nothing

Forms!frmOutstanding.L0Req
End If
End Sub

Upvotes: 1

Views: 1000

Answers (1)

June7
June7

Reputation: 21370

To pull value from selected listbox item, have to use Column property and reference column by its index, index begins with 0: Me.List0.Column(0)

Using Me. qualifier is not required but it will trigger intellisense tips.

Upvotes: 2

Related Questions