I
Ivan
Hello,
to brighten the essence of my current problem I woud like to describe the
situation from scratch.
I use Acess 2007.
I have two tables 'tblOrders' and 'tblOrderDetails' linked as ODBC tables
from MS SQL 2000 Server. Both tables have primary keys:
'tblOrders' has 'orderId' primary key and 'tblOrderDetails' has a pair
'orderId' and 'itemId' as primary key. On server there is also a foreign key
constraint defined on key 'orderId' between both tables.
In access I have a main form 'frmOrders' bounded to 'tblOrders' and a
subform 'sfrmOrderDetails' bounded to 'tblOrderDetails'.
Subform 'sfrmOrderDetails' has a Datasheet format and the key field
'itemId' is in a combo box where the row source of this control is a table
'tblItems'.
Now to the problem:
If in the combo box of 'sfrmOrderDetails' I want to input a new 'itemID' I
have to check first if the new selected item is not on the same order yet.
If I wouldn't check that in the code by myself I would certainly get the
message about conflict with forign key from the SQL, what but I don't want
because it will disturb the user (the message is for shure not user
friendly).
So I have tried first with BeforUpdate event code of combo box for 'itemId':
Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "tblOrder_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Cancel = True
End If
End Sub
In such a case I have got the error message: "The value violates the
validaton rule for the field or record. For example, you might have changed
a validation rule without verifying whether the existing data matches the
new validation rule. Klick Undo to restore the previous value, or enter a
new value that meets the validation rule for the file or record."
Because of that I have changed my code to:
Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "tblOrder_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Cancel = True
Me.Undo
End If
End Sub
The result was a new error message: "No current record" . And this annoying
message was there independently of that if I eliminate The 'Cancel = True'
statement or if I put it after or befor the 'Me.Undo' statement.
The user can take notice of 'no current record' and then click again on
problematical combo box or he can type an 'Esc' to take control over the
program but that swim is for shure not in accordance with user friendly
programming standard.
For me such behaviour of the access program is a bug. How to avoid it? With
error handling.
So I wished to catch the "No current record" event with error handling. I
found somewhere that this error should have the error number of 3021, but
all my experiments to put the error handling code into the right subroutine
wereup to now unssuccesful.
My question: in which event of the subform or form can I catch the "No
current record" error after the 'undoing' the input to the new record of the
subform?
Ivan
to brighten the essence of my current problem I woud like to describe the
situation from scratch.
I use Acess 2007.
I have two tables 'tblOrders' and 'tblOrderDetails' linked as ODBC tables
from MS SQL 2000 Server. Both tables have primary keys:
'tblOrders' has 'orderId' primary key and 'tblOrderDetails' has a pair
'orderId' and 'itemId' as primary key. On server there is also a foreign key
constraint defined on key 'orderId' between both tables.
In access I have a main form 'frmOrders' bounded to 'tblOrders' and a
subform 'sfrmOrderDetails' bounded to 'tblOrderDetails'.
Subform 'sfrmOrderDetails' has a Datasheet format and the key field
'itemId' is in a combo box where the row source of this control is a table
'tblItems'.
Now to the problem:
If in the combo box of 'sfrmOrderDetails' I want to input a new 'itemID' I
have to check first if the new selected item is not on the same order yet.
If I wouldn't check that in the code by myself I would certainly get the
message about conflict with forign key from the SQL, what but I don't want
because it will disturb the user (the message is for shure not user
friendly).
So I have tried first with BeforUpdate event code of combo box for 'itemId':
Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "tblOrder_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Cancel = True
End If
End Sub
In such a case I have got the error message: "The value violates the
validaton rule for the field or record. For example, you might have changed
a validation rule without verifying whether the existing data matches the
new validation rule. Klick Undo to restore the previous value, or enter a
new value that meets the validation rule for the file or record."
Because of that I have changed my code to:
Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "tblOrder_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Cancel = True
Me.Undo
End If
End Sub
The result was a new error message: "No current record" . And this annoying
message was there independently of that if I eliminate The 'Cancel = True'
statement or if I put it after or befor the 'Me.Undo' statement.
The user can take notice of 'no current record' and then click again on
problematical combo box or he can type an 'Esc' to take control over the
program but that swim is for shure not in accordance with user friendly
programming standard.
For me such behaviour of the access program is a bug. How to avoid it? With
error handling.
So I wished to catch the "No current record" event with error handling. I
found somewhere that this error should have the error number of 3021, but
all my experiments to put the error handling code into the right subroutine
wereup to now unssuccesful.
My question: in which event of the subform or form can I catch the "No
current record" error after the 'undoing' the input to the new record of the
subform?
Ivan