Setting the value of an Access combobox with VBA comparison

Question

I'm trying to figure out where I went wrong with this.

I have two tables Request and Parent. Request can only have one related Parent record, but Parent can have many related Request records. So I have the Request table containing the foreign key to Parent.

I have an unbound combobox that pulls it's data from the Parent table using a query (contains company name and ID bound to column 0 and 1, with column 1 being hidden so the user doesn't see the numeric ID). It's unbound because the form's recordset has a lot of complex joins, making anything on that form unable to be updated. So I created an "On Change" event on the combo box to fill in the foreign key using a querydef SQL update query:

Private Sub Combo217_Change()
Dim ComboID As String
Dim ReqID As Long
Dim dbs As DAO.Database
Dim qdfUpdateParentExisting As DAO.QueryDef

ReqID = Me.RequestID.Value
ComboID = Me.Combo217.Column(1)

Set dbs = CurrentDb
Set qdfUpdateParentExisting = dbs.QueryDefs("UpdateReqExistingParent")
qdfUpdateParentExisting.Parameters("VBParent").Value = ComboID
qdfUpdateParentExisting.Parameters("VBReqID").Value = ReqID
qdfUpdateParentExisting.Execute
qdfUpdateParentExisting.Close
DoCmd.Save acForm, "DT2"
Me.Requery

End Sub

This works just fine, but once you exit the form and re-enter it, the value in the combo box is blank and I would like this to contain the same value that was selected.

I've been trying to do an "On load event" with the following code but it's not working

Dim ParID
ParID = Me.ParentID.Value
Me.Combo217.Column(1).Value = ParID

Any input on getting this to work would be fantastic!

dbmitch dbmitch · Accepted Answer · 2016-09-06T19:34:44

Because it's tied to specific column you can loop thru to set the value based on the matching ID

EDIT - Add Row index to Column Value

Dim i as Integer

With Combo217
   For i = 0 To .ListCount - 1
      If .Column(1, i).Value = ParID Then
         .Value = .ItemData(i)
         Exit For
      End If
   Next
End With

Setting the value of an Access combobox with VBA comparison

1 Answers