级联组合框导致下一条记录中的空字段 [英] cascading combo box causing empty fields in next record

问题描述

我在层叠组合框时遇到问题.组合框一切正常，并且正确填充了值.

I'm having problems with a cascading combo box. Everything works fine with the combo boxes and the values get populated correctly.

Private Sub cmbAdjComp_AfterUpdate()
Me.cboAdjOff.RowSource = "SELECT AdjusterCompanyOffice.ID, 
AdjusterCompanyOffice.Address1, 
AdjusterCompanyOffice.Address2, 
AdjusterCompanyOffice.Address3, 
AdjusterCompanyOffice.Address4, 
AdjusterCompanyOffice.Address5 FROM" & _
" AdjusterCompanyOffice WHERE 
AdjusterCompanyOffice.AdjCompID = " & Me.cmbAdjComp.Column(1) & _
" ORDER BY AdjusterCompanyOffice.Address1"
Me.cboAdjOff = Me.cboAdjOff.ItemData(0)
End Sub

辅助组合框具有行源查询:

The secondary combo box has a row source query:

SELECT AdjusterCompanyOffice.ID, AdjusterCompanyOffice.Address1,    
AdjusterCompanyOffice.Address2, AdjusterCompanyOffice.Address3,
 AdjusterCompanyOffice.Address4, AdjusterCompanyOffice.Address5 FROM 
 AdjusterCompanyOffice ORDER BY AdjusterCompanyOffice.Address1;

两个组合框都具有相同的控制源.

Both comboboxes have the same controlsource.

在记录之间移动时，一切正常且繁琐，并且框显示每个记录的正确字段.

Everything works fine and dandy moving between records and the boxes show the correct fields for each record.

当我使用第一个组合框，然后在第二个组合框中选择适当的选项时，在特定记录上一切正常.

When i use the first combo box, and then select the appropriate option in the second combo box, everything works great on the specific record.

但是，当我移至下一条记录时，第二个组合框中的值都为空.如果我关闭表单并重新打开它，并避免使用级联组合框，则当我在记录之间移动时，所有值都是正确的.

However when I move to the next record, the values in the second combo box are all empty. If i close the form and reopen it, and avoid using the cascading combo boxes all the values are all correct when i move between records.

以某种方式使用级联组合框会与辅助组合框的行源产生冲突.

Somehow using the cascading combo boxes creates a conflict with the row source of the secondary combo box.

希望很清楚！一直在四处寻找答案，但找不到任何东西.

Hope that is clear! Have been rummaging around for an answer but cant find anything.

任何帮助将不胜感激.

谢谢

Noel

推荐答案

导航到下一条记录时组合框为空白的原因是，您将NotInList设置为TRUE(这是您想要的)，但是当您到达记录时，行源已被筛选为不包括存储在组合框绑定到的字段中的值.因此，它为空白-值存在，但由于不在列表中，因此无法显示.

The reason why the combo box is blank when you navigate to the next record is because you have NotInList set to TRUE (which is what you want), but when you arrive on the record, the rowsource has been filtered to not include the value stored in the field the combo box is bound to. Thus, it's blank -- the value is there, but it can't be displayed, since it's not in the list.

要解决此问题，您需要清除第二个组合框上的过滤器.为此，在窗体的OnCurrent事件中，将已过滤的组合框的行源设置为未过滤:

To fix this, you need to clear the filter on the second combo box. To do that, in the OnCurrent event of your form, set the rowsource of the filtered combo box to be unfiltered:

  Me!cboAdjOff.RowSource = "SELECT AdjusterCompanyOffice.ID, AdjusterCompanyOffice.Address1, AdjusterCompanyOffice.Address2, AdjusterCompanyOffice.Address3, AdjusterCompanyOffice.Address4, AdjusterCompanyOffice.Address5 FROM AdjusterCompanyOffice ORDER BY AdjusterCompanyOffice.Address1"

我通常通过在表单模块的顶部创建两个常量来处理此问题，一个常量用于SELECT语句，一个常量用于ORDER BY:

I usually handle this by creating two constants at the top of the form's module, one for the SELECT statement and one for the ORDER BY:

  cstrRecordsourceSelect = "SELECT AdjusterCompanyOffice.ID, AdjusterCompanyOffice.Address1, AdjusterCompanyOffice.Address2, AdjusterCompanyOffice.Address3, AdjusterCompanyOffice.Address4, AdjusterCompanyOffice.Address5 FROM AdjusterCompanyOffice"
  cstrRecordsourceOrderBy = "ORDER BY AdjusterCompanyOffice.Address1"

然后它更容易处理.在OnCurrent中，它看起来像这样:

Then it's much easier to handle. In the OnCurrent it looks like this:

  Me!cboAdjOff.RowSource = cstrRecordsourceSelect & " " & cstrRecordsourceSelect 

...并在第一个组合框的AfterUpdate中:

...and in the AfterUpdate of your first combo box:

  Me!cboAdjOff.RowSource = cstrRecordsourceSelect & _
    "WHERE AdjusterCompanyOffice.AdjCompID = " & Me!cmbAdjComp.Column(1) & _
    " " & cstrRecordsourceSelect

这使得代码更易于阅读，并且如果需要的话，还可以更轻松地更改行源，因为您只需要编辑常量即可.

This makes the code easier to read, and it also makes it easier to alter the rowsource if you need to, since you have to edit only the constant.

这篇关于级联组合框导致下一条记录中的空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！