访问自动将 Null 分配给非 Variant——在哪里?
在 Access 2007 中,我有两个相关的表,其中一个的(代理)PK 是另一个的 FK:
table Organization (
org_id int PK,
other_fields whatever,
contact_id int FK
)
table contact (
contact_id int PK,
contact_nm text(255),
other_fields whatever
)
显然不是完整的表定义......
因此我构建了反映这一点的表单 - 有一个 frmOrganization,带有一个子表单sbf联系方式。它们通过 contact_id 关联,就像表一样。 sbfContact 上对 contact_id 表中的所有字段都有控制,除了 contact_id(当然,它存在于记录源中 (SELECT * FROM contact;))。两种形式都没有以任何方式引用另一种形式的代码,并且根本没有宏。
当我尝试使用子表单输入新联系人时,出现问题。当我在绑定到 contact_nm 的控件中键入一个字符时,Access 告诉我“您试图将 Null 值分配给不是 Variant 数据类型的变量。”我什么?在哪里?我没有将任何东西分配给任何变量——是吗?如果我对该消息回答“确定”,我刚刚输入的字符将出现在控件中一切都按照我的预期进行。
这是怎么回事,更重要的是,我需要做什么才能让它停止?
使用更多信息进行编辑
好吧,我希望能够简化,但我不认为它有帮助......我真正拥有的是四个不同的实体,每个实体都需要自己的联系数据
Customer
Site
Manufacturer
Engineer
:(行间距很重要——客户将至少有一个站点,也许更多,但站点可以在没有客户的情况下存在(除非我将“我们”作为客户),另外两个是完全独立的。
我观察到最初的问题是与“主”实体之一一起工作的,并认为它在其他实体中也是并行的。我认为 CodeSlave 已经向我指出了问题的正确根源——恐怕我唯一的办法是不允许通过这个子表单进行添加,而是构建一个独立的“创建联系人”表单。不过,欢迎其他建议......
In Access 2007, I have two tables related such that the (surrogate) PK of one is an FK in the other:
table Organization (
org_id int PK,
other_fields whatever,
contact_id int FK
)
table contact (
contact_id int PK,
contact_nm text(255),
other_fields whatever
)
Obviously not complete table definitions....
So I build forms that reflect this--there's a frmOrganization, with a subform sbfContact. They're related by contact_id, just as the tables are. There are controls on sbfContact for all fields in table contact except contact_id (though it is, of course, present in the Record Source (SELECT * FROM contact;)). Neither form has any code that references the other in any way, and no macros at all.
My problem occurs when I try to use the subform to enter a new contact. As soon as I type a character into the control bound to contact_nm, Access tells me that "You tried to assign the Null value to a variable that is not a Variant data type." I what? Where? I'm not assigning anything to any variable--am I? If I answer "OK" to the message, the character I just typed appears in the control & everything proceeds as I expect.
What's going on here, and--more importantly--what do I need to do to make it stop?
Edit with more info
Okay, I was hoping to simplify, but I don't s'pose it helped.... What I really have is four different entities that each requires its own Contact data:
Customer
Site
Manufacturer
Engineer
(The line spacing is significant--a Customer will have at least one Site, maybe more, but Sites can exist without Customers (unless I carry "us" as a Customer). The other two are totally independent).
I observed the original problem working with one of the "master" entites, and figured that it would be parallel in the others. I think that CodeSlave has pointed me to the correct source of the problem--I'm afraid that my only recourse is to not allow additions through this subform, instead building a freestanding "Create Contact" form. Other suggestions are welcome, though....
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
基本上,Access 不知道如何将您的联系人链接到组织,它假设您已经知道 Contact_ID 是什么,因为它来自组织表单,但当您在子表单上输入联系人时,它仍然为 Null。
我想你想把这里的事情翻转过来。
您是否有
与多个组织的联系人
或有多个联系人的组织
。如果是
Contact with multiple Orgs
,请将“Contact”设为主表单,将“Org”设为子表单。如果
组织有多个联系人
,请为联系人提供 Ord_ID 的 FK,并从组织中删除 Contact_ID-
根据评论进行跟进。
我处理用于多个实体的相同联系人的方式,我不会在子表单上创建联系人条目。相反,我会在完全不同的表单上创建评论,然后使用下拉列表选择联系人(并仅在组织的表单上显示其余的联系信息)。即使您的组织表单上必须有一个“创建联系人”按钮才能临时创建创建表单,您也会获得更多收益。
Basically Access has no idea how to link your Contact to the Org, it's assuming that you already know what the Contact_ID is because it's coming from the Org form, but it's still Null when you enter the Contact on the subform.
I think you want to flip things around here.
Have you got a
Contact with multiple Orgs
or anOrg with multiple Contacts
.In the case of a
Contact with multiple Orgs
, make the Contact the master form, and the Org the sub form.In the case of an
Org with multiple Contacts
, give the Contact a FK to the Ord_ID and drop the Contact_ID from the Org--
Follow up based upon the comment.
The way I would deal with the same contact being used for multiple entities, I wouldn't make the contact entry on a sub form. Instead I would create the comments on a completely different form, and then select the contacts with a pull down list (and just display the rest of the contact info on the Org's form). Even if you have to have a "create contact" button on your Org form to pup up a create form temporarily, you'll get more bang for your buck.