由组合框控制的文本框中的#Error，但仅当在组合中输入特定值时才会出现

I have a text box controlling a combo box in turn controlling 4 text boxes.

我有一个控制组合框的文本框,控制4个文本框。

The first text box is filled by a user or append query with a four digit store number.

第一个文本框由用户填充或附加四位数的商店编号。

The combo box is populated with the following query.

使用以下查询填充组合框。

SELECT Vendors.IP
FROM Vendors
WHERE (((Vendors.Store)=[Forms].[Project Details].[Store]));

That combo box then populates the text boxes.

然后该组合框填充文本框。

=DLookUp("IPPhone","Vendors","IP='" & [Forms]![Project Details]![cboIP] & "'")

"IPPhone" is actually 4 separate values (1 for each text box) but they are redacted for simplicity. This dlookup works perfectly well for most Vendors.

“IPPhone”实际上是4个单独的值(每个文本框1个),但为简单起见,它们是经过编辑的。这个dlookup非常适合大多数供应商。

But there is one which does not seem weird at all, call him "Mr McTrouble". When he is selected from the drop down all 4 text boxes show "#ERROR". He's the only one of about 400 vendors to cause this. I've tried deleting him using the forms I created and re-entering him. Tried doing so in the back end table itself, no luck. With him those boxes always show #ERROR.

但有一个看起来并不奇怪,称他为“McTrouble先生”。当从下拉列表中选择他时,所有4个文本框都显示“#ERROR”。他是导致这种情况的大约400家供应商中唯一一家。我尝试使用我创建的表单删除他并重新输入他。尝试在后端表本身这样做,没有运气。和他在一起的那些盒子总是显示#ERROR。

The combo box is a vendor name drawn from a table with the following fields:

组合框是从包含以下字段的表中提取的供应商名称:

Store    Vendor    IP            IPPHONE
1111    000001    John Johnson   111-111-1111
2222    000002    Mike           111-111-1112
2222    000003    Frankie Frank  111-111-1113
3333    000004    Joe Bob        111-111-1114
4444    000005    Smith Smith    111-111-1115
5555    000006    Mr McTrouble   111-111-1116

Date types on the table are:

表上的日期类型是:

Number  Number    Text            Text

The other fields here then populate the text boxes.

然后,此处的其他字段将填充文本框。

What am I missing here?

我在这里想念的是什么?

Is there an easier way to do this?

有更简单的方法吗?

1 个解决方案

#1

There is probably a single quote in the Vendor's name, like O'Donnell or O'Malley. The code that builds your sql clause also uses single quotes, resulting in an improperly formatted string looking like this: IP='O'Malley'

供应商名称中可能只有一个引用,如O'Donnell或O'Malley。构建sql子句的代码也使用单引号,导致格式不正确的字符串如下所示:IP ='O'Malley'

Try:

=DLookUp("IPPhone","Vendors","IP=""" & [Forms]![Project Details]![cboIP] & """")

I replaced each single quote with two double quotes. In VBA, this is the weird way double-quotes are escaped.

我用两个双引号替换了每个单引号。在VBA中,这是双引号转义的奇怪方式。

A better solution, assuming Vendor is a Primary Key for Vendor.

假设供应商是供应商的主要密钥,则是更好的解决方案。

combo RowSource: SELECT Vendors.Vendor,Vendors.IP [... the rest of your SQL]

组合RowSource:SELECT Vendors.Vendor,Vendors.IP [...其余的SQL]

combo ColumnCount: 2

组合ColumnCount:2

combo ColumWidths: 0;1.5 (the zero hides the first column in the combo)

组合ColumWidths:0; 1.5(零隐藏组合中的第一列)

=DLookUp("IPPhone","Vendors","Vendor=" & [Forms]![Project Details]![cboIP])

= DLookUp(“IPPhone”,“供应商”,“供应商=”和[表格]![项目详情]![cboIP])

Primary Keys should be used to lookup values, if available.

如果可用,主键应用于查找值。

#1