通过VBA比较设置Access组合框的值 [英] Setting the value of an Access combobox with VBA comparison

问题描述

我正在尝试找出我在哪里出错了.

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

我有两个表Request和Parent. Request只能具有一个相关的Parent记录，但是Parent可以具有许多相关的Request记录.所以我有Request表，其中包含Parent的外键.

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.

我有一个未绑定的组合框，可使用查询从Parent表中提取其数据(包含绑定到第0列和第1列的公司名称和ID，第1列被隐藏，因此用户看不到数字ID) .它是不受限制的，因为表单的记录集具有许多复杂的联接，使得该表单上的任何内容都无法更新.因此，我在组合框上创建了一个"On Change"事件，以使用querydef SQL更新查询来填充外键:

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!

推荐答案

由于它与特定列绑定，因此可以循环以根据匹配ID设置值

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

这篇关于通过VBA比较设置Access组合框的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！