“插入”创造问题 [英] "Insert into" creating issues
问题描述
我有一个数据输入表单，每次打开表单时都会打开默认值。要求是用户可以编辑表单中的现有数据。当表单关闭时，它不应更新现有记录，而是插入新记录。
在Form-Beforeupdate函数中，我首先检查记录是否已存在（主键存在）。然后我写了一个插入到从表单中选取所有值并插入后端表的SQL。
当我打开表单并输入新的主键值时，我不断收到错误说我正在尝试插入重复记录。有趣的是，它在表格中插入新记录。
我的逻辑如下：
Hi,
I have a data entry form which opens default values each time the form is open. Requirement is that users can edit existing data in the form. When the form is closed, it shouldnt update the existing record but rather insert a new record.
On the Form-Beforeupdate function, I first check if the record already exists (Primary keys exist). Then I wrote a "Insert into" SQL that picks all values from the form and inserts into the backend table.
When I open the form and enter new Primary key values, I keep getting an error saying I''m trying to insert duplicate records. Funny part is, it inserts the new record in the table.
The logic I have is below:
推荐答案
我有一个数据输入表格打开默认值每次打开表单时都会显示值。要求是用户可以编辑表单中的现有数据。当表单关闭时，它不应更新现有记录，而是插入新记录。
在Form-Beforeupdate函数中，我首先检查记录是否已存在（主键存在）。然后我写了一个插入到从表单中选取所有值并插入后端表的SQL。
当我打开表单并输入新的主键值时，我不断收到错误说我正在尝试插入重复记录。有趣的是，它在表格中插入新记录。
我的逻辑如下：
Hi,
I have a data entry form which opens default values each time the form is open. Requirement is that users can edit existing data in the form. When the form is closed, it shouldnt update the existing record but rather insert a new record.
On the Form-Beforeupdate function, I first check if the record already exists (Primary keys exist). Then I wrote a "Insert into" SQL that picks all values from the form and inserts into the backend table.
When I open the form and enter new Primary key values, I keep getting an error saying I''m trying to insert duplicate records. Funny part is, it inserts the new record in the table.
The logic I have is below:
1）在循环之后你有一个额外的结束。
2）标志是未定义的。
3）如果您只是想检查是否存在ID，请使用：
1) You have an extra End If after your loop.
2) Flag is undefined.
3) If you''re just trying to check if an ID exists, just use:
对不起。我只发布了代码的快照。那个额外的End if是一个If stmt的一部分我没有附在我的帖子中。国旗也在早些时候初始化。这些问题没有问题。
我根据用户是否按下是来尝试DCOUNT，如下所示。弹出式屏幕中的按钮
如果lResponse = vbYes则
取消=错误
如果DCount（ResourceName， ; tblProject"，" ResourceName ="& Me.ResourceName.Value）> 0和DCount（Dateval，tblProject，Dateval ="& Me.Dateval.Value）> 0然后
DoCmd.RunSQL" INSERT INTO tblProject（ProjectName，...）VALUES（Forms！frmProjectEntry！ProjectName，...）"
End If
结束如果
我一直收到错误说运行时错误2001您已取消之前的操作。当我按下Debug时，它会将我带到VBA中的DCount行。我在DCount的语法中做错了吗？我有一个复合主键 - ResourceName和Dateval。
谢谢！
Hi,
I''m sorry. I had posted only a snapshot of my code. That extra End if was part of an If stmt I had not enclosed in my post. Flag was also initialized earlier. No issues with those.
I tried DCOUNT as follows, based on if a user presses "Yes" button in a popup screen
If lResponse = vbYes Then
Cancel = False
If DCount("ResourceName", "tblProject", "ResourceName = " & Me.ResourceName.Value) > 0 And DCount("Dateval", "tblProject", "Dateval = " & Me.Dateval.Value) > 0 Then
DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,...) VALUES (Forms!frmProjectEntry!ProjectName,...)"
End If
End If
I keep getting an error saying "Run Time error 2001" You have cancelled the previous operation". When I press Debug, it takes me to the DCount line in my VBA. Am I doing something wrong in the syntax of DCount? I have a composite primary Key - ResourceName and Dateval.
Thanks!
---------------------------------- -------------------------------------------------- ---------------
我认为我在上一篇文章中的逻辑不正确。如果DCOUNT是> 0，它不应该插入一行。
除此之外，我不能在这里使用DCOUNT，因为我的主键是复合键，因此每个键的DCOUNT绝对可以是> 0，但复合键数必须为= 0才能插入新行。
我觉得问题首先是因为我试图插入一行当我关闭表单时，Access form_onClose默认事件也试图插入此行，从而产生冲突。我可以覆盖Form_Close事件以不插入或更新我的记录吗？
这篇关于“插入”创造问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
