如何处理绑定表单中的主键冲突而不会导致和ODBC错误。 [英] How to handle Primary Key violations in a bound form without causing and ODBC error.
问题描述
仅供参考：
此消息是为了MS Access社区的利益。我发现
这个问题已经遭到很多人的讨论，但几乎没有任何地方
这里有一个完整的解决方案。所以我想我应该通过发布我们的调查结果向社区回馈
。
感谢大家的帮助，直到现在发布问题和
他们的解决方案。
~Abhijit
http://www.ececs.uc.edu/~joshiabh/Ab...ML_General.htm
问题：1。如何处理绑定表单中的主键违规
而不会导致和ODBC错误。
2.无法在表单的OnOpen属性上捕获特定的ODBC错误
参与研究的成员：Joe，Jason，Loren，Abhijit
特别感谢Joe的研究和智能实施
这些想法！
结果：
1.如何处理绑定表单中的主键违规而不会导致
导致ODBC错误。
最好的方法我们发现以绑定形式处理主键违规
没有导致和ODBC错误是为了防止用户插入或
将无效数据更改为字段。
在我们的例子中，我们将状态和区域作为复合主要
键。
我们可以停止使用输入状态
和区域的重复组合的方式如下：
Private Sub Form_BeforeUpdate_GOOD（取消为整数）
''如果Me.txtFeatureCosts.OldValue<> Me.txtFeatureCosts.Value然后
如果Me.comboStates.OldValue<> Me.comboStates.Value或_
Me.comboZones.OldValue<> Me.comboZones.Value或_
Me.NewRecord然后
如果不是IsNull（DLookup（" zone"，" table_name"，" State ="" ;"&
comboStates&""" AND Zone ="""& comboZones&""""））然后
MsgBox此状态区域组合已存在。 &
vbNewLine& 请尝试不同的州/区域组合并再次尝试
。，vbCritical + vbOKOnly，重复状态/检测到区域
取消= True
结束如果
结束如果
结束子
或另一种方法是创建一个记录集克隆表格所依据的查询或表格
并查找用户是否正在尝试对主键进行任何更改
。
Private Sub Form_BeforeUpdate（取消为整数）
Dim rc As Recordset
设置rc = Me.RecordsetClone
rc.Bookmark = Me.Bookmark
如果rc.Fields（Me.comboStates.ControlSource）<> Me.comboStates.Value或
_
rc.Fields（Me.comboZones.ControlSource）<> Me.comboZones.Value或_
Me.NewRecord然后
''如果Me.NewRecord那么
如果不是IsNull（DLookup（"） zone"，" table_name"，" State ="""&
comboStates&""" AND Zone ="""& comboZones& ;""""））然后
MsgBox此状态区域组合已经存在。 &
vbNewLine& 请尝试不同的州/区域组合并再次尝试
。，vbCritical + vbOKOnly，重复状态/检测到区域
取消= True
结束如果
结束如果
设置rc =没什么
结束子
2。捕获ODBC错误：
此解决方案由Microsoft为Access 2000提供。乔改为
以适应Access 97.
Access 97 has RecordsetClone Propery而Access 2000具有
Recordset.Clone Propery。我们希望在以下文章中对代码进行更改。
http://support.microsoft.com/default。 ..NoWebContent = 1
基本上，链接
表的主键违规导致的ODBC错误无法直接捕获。我们必须在本地记录集上模拟
违规并捕获已经导致的错误
并给出自定义的错误消息。但是为此，我们必须使用
制作整个表格的完整记录集克隆或查询表格所基于的
。这是一个低效且有时不切实际的解决方案因为整个表在客户端复制，
这是不可取的。我在这里发布解决方案。如果你有
有任何问题请告诉我。
公共函数SaveRecODBC（SRO_form As Form）As Boolean
''**** ********************************************* ***** *********
''功能：SaveRecODBC
''
''目的：根据a更新表格链接ODBC表
''并捕获任何ODBC错误。
''
''参数：SRO_Form，引用表单。
''
''
''返回：如果成功则返回True或如果发生错误则返回False。
' ******************* **************
On Error GoTo SaveRecODBCErr
Dim fld As Field，ctl As Control
Dim errStored As Error
Dim rc As DAO.Recordset
''检查记录是否有变化。
如果SRO_form.Dirty那么
设置rc = SRO_form.RecordsetClone
如果SRO_for m.NewRecord然后
rc.AddNew
每个ctl在SRO_form.Controls
''检查它是否是控制类型
''有一个ControlSource。
如果ctl.ControlType = acTextBox或_
ctl.ControlType = acComboBox或_
ctl.ControlType = acListBox或_
ctl.ControlType = acCheckBox然后
''验证ControlSource中是否存在值。
如果ctl.Properties（" ControlSource"）<> ""然后
''循环遍历
''RecordsetClone中的字段集合。如果您找到与ControlSource匹配的字段名称
''，请更新
''字段。如果没有，请跳过该字段。这是计算控件所必需的
'。
每个fld在rc.Fields中
''查找字段并验证
''它不是空的。
''如果它是空的，不要添加它。
如果fld.Name = ctl.Properties（" ControlSource"）_
And Not IsNull（ctl）然后
fld.Value = ctl
''退出For循环
''如果你有匹配。
退出
结束如果
下一个fld
结束如果''结束如果ctl.Properties（" ControlSource"）
结束如果''结束如果ctl.controltype
下一步ctl
rc.Update
Else
''这是不是新记录。
''设置书签以将
''RecordsetClone中的记录与表单中的记录同步。
rc.Bookmark = SRO_form.Bookmark
rc.Edit
每个ctl在SRO_for m.Controls
''检查它是否是控制类型
''有一个ControlSource。
如果ctl.ControlType = acTextBox或_
ctl.ControlType = acComboBox或_
ctl.ControlType = acListBox或_
ctl.ControlType = acCheckBox然后
''验证
''ControlSource中是否存在值。
如果ctl.Properties（" ControlSource"）< ;> ""然后
''循环遍历
''RecordsetClone中的字段集合。如果您找到与ControlSource匹配的字段名称
''，请更新
''字段。如果没有，请跳过该字段。这是计算控制所必需的
'。
每个fld在rc.Fields
''找到该字段并确保
''值已更改。如果它没有更改，请不要执行更新。
如果fld.Name = ctl.Properties（" ControlSource"）_
和fld.Value<> ctl和_
Not IsNull（fld.Value<> ctl）然后
fld.Value = ctl
' '如果你有比赛，退出For循环。
退出
结束如果
下一个fld
结束如果''结束如果ctl.Properties（" ControlSource"）
下一步ctl
rc.Update
结束如果''结束如果SRO_form.NewRecord
结束如果''结束如果SRO_form.Dirty
''如果函数已经成功执行到此时那么
''将其值设置为True并退出。
SaveRecODBC = True
Exit_SaveRecODBCErr：
退出函数
SaveRecODBCErr：
''函数因ODBC错误而失败。
''下面是一些已知错误编号的列表。
' '如果你没有在这个列表中收到错误，
''添加它Select Case语句出错。
每个errStored在DBEngine.Errors中
选择案例errStored.Number
案例3146
MsgBox"无操作 - 标准ODBC - 调用失败错误。
案例2627
MsgBox"主键中重复值导致的错误。
MsgBox您试图输入重复值 &安培; _
在主键中。
案例3621
MsgBox"无操作 - 标准ODBC命令中止错误。
案例547
MsgBox"外键约束错误。
MsgBox你违反了外键约束。
Case Else
MsgBox errStored.Description
MsgBox errStored.Number
''Select Case
''语句中没有说明错误。
错误转到0
恢复
结束选择
下一个错误
昏暗MyError为错误
MsgBox Errors.count& "发现的错误
对于DBEngine.Errors中的每个MyError
使用MyError
MsgBox .Number& "发现的错误 &安培; 。描述
结束
下一个MyError
SaveRecODBC = False
恢复Exit_SaveRecODBCErr
结束功能
哎呀，这个帖子相当长......你不觉得吗？
我的意思是，如果你打算花一些时间来发布一个如何停止主键违规的例子，为什么组合框上有如此多的示例代码呢？ />
为什么不为ONE字段发布一个漂亮的干净示例？比如，说一个公司名称
字段？
（更容易理解......然后比一堆组合框更清洁）。
此外，您通常会更好地使用
实际控件的更新来代替表格。
原因你为什么要在更新之前使用控件
事件（代替更新事件之前的表格）是控件
事件不会触发
除非价值改变了！因此，在数据输入过程中，您不必使用oldvalue运行所有代码检查。就像你一样。此外，即使对于一般数据输入更好，你现在也不必运行所有代码
每次更新记录！因此，如果字段被更改，则会保存代码以告知
（即：更新前事件未运行!!）
此外，您还没有每次更新
记录时都需要运行检查代码..但是只有当这些控件更新时才会运行。
所以，不需要全部运行每次更新记录时的代码???）
此外，额外的好处是用户立即获得反馈，而不是填写一些b / b
巨大的形式.......只有在所有
之后得到一个令人讨厌的消息数据已经输入到表格中。
所以，现在我们为用户获得更少的代码，更少的系统资源和更好的UI！
至于捕获odbc错误？哼，正如你所指出的那样，如果你在更新事件发生之前在控件中进行了简单的检查，那么错误就不会发生。
此外，你可以捕获odbc错误。只需使用表单错误即可活动。
你可以去：
Private Sub Form_Error（DataErr As Integer，Response As Integer）
MsgBox你不能更新数据
响应= acDataErrContinue
结束子
所以，我算一下... 2行代码来捕获错误信息？
我真的不确定使用如此大的大块的智慧当上面的
就好了吗？
我在这里错过了一些观点吗？
看，我不是在努力帮助这里的人们下雨（那就是美元）。但是，当我查看你发布的金额代码时......好像很生气......真的很想帮助任何人!!!
因此，作为旁注......表格出错。确实陷阱odbc，以及为什么
你没有使用这种方法？
-
Albert D. Kallal（访问MVP）
加拿大艾伯塔省埃德蒙顿
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Abhi < JA ******* @ yahoo.com>在消息中写道
新闻：80 ************************* @ posting.google.co m ...
啊，看看所有的Access Morons
啊，看看所有的Access Morons
所有的Access Morons
它们都来自哪里？
所有Access Morons
它们都属于哪里？
啊，看看所有的Access Morons
啊，看看所有的Access Morons
嘿Poet先生，你为什么要唱你的葬礼歌你的大脑？如果你舔我的后面，我可能会告诉你Access的白痴来自哪里？
来自。到那时你可以卖掉你的屁股并购买一些大脑。无论如何我
对你的狗屎态度不感兴趣。如果你发表回复，我不会读b
。但至少你我会让你更浪费一些
!!哈哈，失败者!!
~a
***通过开发者指数发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
FYI:
This message is for the benefit of MS Access Community. I found that
this prblem has been encounterd by many but there is hardly any place
where a complete solution is posted. So I thought I should give back
to the community by posting our findings.
Thanks you all for all your help till now by posting problems and
their solutions.
~Abhijit
http://www.ececs.uc.edu/~joshiabh/Ab...ML_General.htm
Problem: 1. How to handle Primary Key violations in a bound form
without causing and ODBC error.
2. Cannot Trap Specific ODBC Errors on OnOpen Property of a Form
Members involved in the research: Joe, Jason, Loren, Abhijit
Special Thanks to Joe for researching and intelligent implementations
of these ideas!
Findings:
1. How to handle Primary Key violations in a bound form without
causing and ODBC error.
The best way we found to handle Primary Key violations in a bound form
without causing and ODBC error is to prevent user from inserting or
changing invalid data into the fields.
In our case for example, we have state and zone as composite primary
key.
The way we can stop use from entering a duplicate combination of state
and zone is as follows:
Private Sub Form_BeforeUpdate_GOOD(Cancel As Integer)
''If Me.txtFeatureCosts.OldValue <> Me.txtFeatureCosts.Value Then
If Me.comboStates.OldValue <> Me.comboStates.Value Or _
Me.comboZones.OldValue <> Me.comboZones.Value Or _
Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
End Sub
OR another method is to create a recordsetclone of the query or table
on which the form is based and find if user is trying to make any
changes to the primary keys.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rc As Recordset
Set rc = Me.RecordsetClone
rc.Bookmark = Me.Bookmark
If rc.Fields(Me.comboStates.ControlSource) <> Me.comboStates.Value Or
_
rc.Fields(Me.comboZones.ControlSource) <> Me.comboZones.Value Or _
Me.NewRecord Then
''If Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
Set rc = Nothing
End Sub
2. Catching the ODBC error:
This solution is priovided by Microsoft for Access 2000. Joe changed
it to suit Access 97.
Access 97 has RecordsetClone Propery whereas Access 2000 has
Recordset.Clone Propery. We hhad to make that change in the code in
the following article.
http://support.microsoft.com/default...NoWebContent=1
Basically the ODBC errors caused by Primary Key violations with linked
tables are not directly possible to catch. We have to simulate that
violation on a local recordset and catch the error thathas been caused
and give out the customised error message. But for this we have to
make a complete recordsetClone of the whole table or Query on which
the form is based on. This is an inefficient and at times impractical
solution because the whole table is replicated on the client side,
which is not desirable. I am posting the solution here. If you have
any questions please let me know.
Public Function SaveRecODBC(SRO_form As Form) As Boolean
''************************************************* **************
''Function: SaveRecODBC
''
''Purpose: Updates a form based on a linked ODBC table
'' and traps any ODBC errors.
''
''Arguments: SRO_Form, which refers to the form.
''
''
''Returns: True if successful or False if an error occurs.
''************************************************* **************
On Error GoTo SaveRecODBCErr
Dim fld As Field, ctl As Control
Dim errStored As Error
Dim rc As DAO.Recordset
'' Check to see if the record has changed.
If SRO_form.Dirty Then
Set rc = SRO_form.RecordsetClone
If SRO_form.NewRecord Then
rc.AddNew
For Each ctl In SRO_form.Controls
'' Check to see if it is the type of control
'' that has a ControlSource.
If ctl.ControlType = acTextBox Or _
ctl.ControlType = acComboBox Or _
ctl.ControlType = acListBox Or _
ctl.ControlType = acCheckBox Then
'' Verify that a value exists in the ControlSource.
If ctl.Properties("ControlSource") <> "" Then
'' Loop through the fields collection in the
'' RecordsetClone. If you find a field name
'' that matches the ControlSource, update the
'' field. If not, skip the field. This is
'' necessary to account for calculated controls.
For Each fld In rc.Fields
'' Find the field and verify
'' that it is not Null.
'' If it is Null, don''t add it.
If fld.Name = ctl.Properties("ControlSource") _
And Not IsNull(ctl) Then
fld.Value = ctl
'' Exit the For loop
'' if you have a match.
Exit For
End If
Next fld
End If '' End If ctl.Properties("ControlSource")
End If '' End If ctl.controltype
Next ctl
rc.Update
Else
'' This is not a new record.
'' Set the bookmark to synchronize the record in the
'' RecordsetClone with the record in the form.
rc.Bookmark = SRO_form.Bookmark
rc.Edit
For Each ctl In SRO_form.Controls
'' Check to see if it is the type of control
'' that has a ControlSource.
If ctl.ControlType = acTextBox Or _
ctl.ControlType = acComboBox Or _
ctl.ControlType = acListBox Or _
ctl.ControlType = acCheckBox Then
'' Verify that a value exists in the
'' ControlSource.
If ctl.Properties("ControlSource") <> "" Then
'' Loop through the fields collection in the
'' RecordsetClone. If you find a field name
'' that matches the ControlSource, update the
'' field. If not, skip the field. This is
'' necessary to account for calcualted controls.
For Each fld In rc.Fields
'' Find the field and make sure that the
'' value has changed. If it has not
'' changed, do not perform the update.
If fld.Name = ctl.Properties("ControlSource") _
And fld.Value <> ctl And _
Not IsNull(fld.Value <> ctl) Then
fld.Value = ctl
'' Exit the For loop if you have a match.
Exit For
End If
Next fld
End If '' End If ctl.Properties("ControlSource")
End If '' End If ctl.controltype
Next ctl
rc.Update
End If '' End If SRO_form.NewRecord
End If '' End If SRO_form.Dirty
'' If function has executed successfully to this point then
'' set its value to True and exit.
SaveRecODBC = True
Exit_SaveRecODBCErr:
Exit Function
SaveRecODBCErr:
'' The function failed because of an ODBC error.
'' Below are a list of some of the known error numbers.
'' If you are not receiving an error in this list,
'' add that error to the Select Case statement.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 3146
MsgBox " No action -- standard ODBC--Call failed error."
Case 2627
MsgBox " Error caused by duplicate value in primary key."
MsgBox "You tried to enter a duplicate value " & _
"in the Primary Key."
Case 3621
MsgBox " No action -- standard ODBC command aborted error."
Case 547
MsgBox " Foreign key constraint error."
MsgBox "You violated a foreign key constraint."
Case Else
MsgBox errStored.Description
MsgBox errStored.Number
'' An error not accounted for in the Select Case
'' statement.
On Error GoTo 0
Resume
End Select
Next errStored
Dim MyError As Error
MsgBox Errors.count & " Of Errors Found "
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " Of Errors Found " & .Description
End With
Next MyError
SaveRecODBC = False
Resume Exit_SaveRecODBCErr
End Function
Gee, this posting is rather long...don''t you think?
I mean, if you are going to spend the time to post an example of how to stop
a primary key violation, why so much example code on combo box stuff?
Why not post a nice clean example for ONE field? Like, say a company name
field?
(easier to understand...and much cleaner then a bunch of combo box stuff).
Further, you are often MUCH better off to use the before update of the
actual control in place of the form.
The reason why you would want use the controls before update
event (in place of the forms before update event) is that the controls
event DOES NOT fire
unless the value is changed!. Thus, during data entry, you don''t have to
run all your code checks with "oldvalue" as you have. Further, even
better for general data entry, you now don''t have to run all that code
EACH TIME a record is updated! So, there is code saved to tell
if the field was changed (ie: the before update event does not run!!)
And, further, you don''t need to run the check code each time a
record is updated..but ONLY when those controls are updated.
So, no need to run all that code each time a record is updated???)
Further, the added benefit is that user gets feedback right away as opposed
to filling out some huge form.......ONLY to get a nasty message AFTER all
the data has been entered in the form.
So, now we got less code, less system resources, and better UI for the user!
As for trapping the odbc error?. Hum, as you point out, that error don''t
happen if you put in a simple check in the controls before update event.
Further, you CAN trap odbc errors. Just use the forms "error" event.
You can go:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "You can''t update data"
Response = acDataErrContinue
End Sub
So, I count what...2 lines of code to trap the error message?
I am not really sure of the wisdom to use such a large hunk of code when the
above is just fine?
Am I missing some point here?
Look, I not trying to rain on your efforts to help people here (that is
wonderful). However, when I look at the amount code you posted...it seems
you are rather angry..then really trying to help anyone!!!
So, as a side note...the forms "on error" does trap odbc, and any reason why
you did not use that approach?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
"Abhi" <ja*******@yahoo.com> wrote in message
news:80*************************@posting.google.co m...
Ah, look at all the Access Morons
Ah, look at all the Access Morons
All the Access Morons
Where do they all come from?
All the Access Morons
Where do they all belong?
Ah, look at all the Access Morons
Ah, look at all the Access Morons
hey Mr Poet, why are you singing your funeral song of your brain? If you
lick my behind I will probably tell you where do Access morons come
from. By that time you can sell your ass and buy some brains. Anyway I
am not interested in your shit attitude. If you post a reply, I will not
be reading it. But atleast you I will make you waste some more energey
!! haha, Loser!!
~a
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
这篇关于如何处理绑定表单中的主键冲突而不会导致和ODBC错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
