No current record after undo

[Ivan]

Hello,

I have two tables like Orders and Order_Detail with primary key orderId on
the first and primary key pair (orderId, itemId) on the second table.
Parallely I have a main form for Orders and a subform for Order_Details,
where both forms are connected with orderId key. Although I have both tables
linked on MS SQL Server and there exist a foreign key relationship, with the
intention to avoid the standard SQL Server message for violation of foreign
key constraint I check alone if the itemId is double for the same orderId in
my BeforreUpdate event code of the input combo box of the itemId field and
in such case I demand the undo. The problem is that I get the "No current
record" error and I don't know how to avoid it?

My code:

Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "Order_Details", "[itemid] = '" & Me.itemId & "' AND
[orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Me.Undo
Else
' some code
Cancel = False
End If
End Sub

Any help?

Ivan

 

[Dirk Goldgar]

Hello,

I have two tables like Orders and Order_Detail with primary key orderId on
the first and primary key pair (orderId, itemId) on the second table.
Parallely I have a main form for Orders and a subform for Order_Details,
where both forms are connected with orderId key. Although I have both
tables linked on MS SQL Server and there exist a foreign key relationship,
with the intention to avoid the standard SQL Server message for violation
of foreign key constraint I check alone if the itemId is double for the
same orderId in my BeforreUpdate event code of the input combo box of the
itemId field and in such case I demand the undo. The problem is that I get
the "No current record" error and I don't know how to avoid it?

My code:

Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "Order_Details", "[itemid] = '" & Me.itemId & "' AND
[orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Me.Undo
Else
' some code
Cancel = False
End If
End Sub

Any help?

I would expect you to cancel the update, using

Me.Undo

Cancel = True

if you don't want the record to be saved.

 

[Ivan]

Thanks Dirk

I have tried before with some variants but have allways got unsatisfactory
results:

1. If I have in the code only 'Cancel = True' (and not Me.Undo) then I get
the 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."

2. If I have in code both 'Cancel = True' and 'Me.Undo' in whatever (both)
sort then I get the message:

"No current record"

As I understand the situation, the program loses the control of the current
record after undoing. Because the second message ('No current record') is
comming from the form and I can't catch the error in the BeforUpdate
subroutine, I have put the code for error catching in the subform
'Form_Error' event code but without any success. I didn't catch any error
but I did get the message about no current record.

Any new sugestion?

Ivan

Hello,

I have two tables like Orders and Order_Detail with primary key orderId
on the first and primary key pair (orderId, itemId) on the second table.
Parallely I have a main form for Orders and a subform for Order_Details,
where both forms are connected with orderId key. Although I have both
tables linked on MS SQL Server and there exist a foreign key
relationship, with the intention to avoid the standard SQL Server message
for violation of foreign key constraint I check alone if the itemId is
double for the same orderId in my BeforeUpdate event code of the input
combo box of the itemId field and in such case I demand the undo. The
problem is that I get the "No current record" error and I don't know how
to avoid it?

My code:

Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "Order_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Me.Undo
Else
' some code
Cancel = False
End If
End Sub

Any help?

I would expect you to cancel the update, using

Me.Undo

Cancel = True

if you don't want the record to be saved.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Ivan]

On the web I found (http://www.thescripts.com/forum/thread205218.html) that
the error number for "No current record"
error should be 3021 (in Access 2002 and 2003, probably in Access 2007 too),
but I don't know where I should put the code for error handling. For test I
have put it in all significant subroutine code (on form and on subform), but
I didn't trap the error!?

Ivan

Thanks Dirk

I have tried before with some variants but have allways got unsatisfactory
results:

1. If I have in the code only 'Cancel = True' (and not Me.Undo) then I get
the 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."

2. If I have in code both 'Cancel = True' and 'Me.Undo' in whatever
(both) sort then I get the message:

"No current record"

As I understand the situation, the program loses the control of the
current record after undoing. Because the second message ('No current
record') is comming from the form and I can't catch the error in the
BeforUpdate subroutine, I have put the code for error catching in the
subform 'Form_Error' event code but without any success. I didn't catch
any error but I did get the message about no current record.

Any new sugestion?

Ivan

Hello,

I have two tables like Orders and Order_Detail with primary key orderId
on the first and primary key pair (orderId, itemId) on the second table.
Parallely I have a main form for Orders and a subform for Order_Details,
where both forms are connected with orderId key. Although I have both
tables linked on MS SQL Server and there exist a foreign key
relationship, with the intention to avoid the standard SQL Server
message for violation of foreign key constraint I check alone if the
itemId is double for the same orderId in my BeforeUpdate event code of
the input combo box of the itemId field and in such case I demand the
undo. The problem is that I get the "No current record" error and I
don't know how to avoid it?

My code:

Private Sub itemid_BeforeUpdate(Cancel As Integer)
If DCount("[itemId]", "Order_Details", "[itemid] = '" & Me.itemId & "'
AND [orderId] = " & Me.orderId) > 0 Then
MsgBox "There is a double itemId!", vbOKOnly, "Notice"
Me.Undo
Else
' some code
Cancel = False
End If
End Sub

Any help?

I would expect you to cancel the update, using

Me.Undo

Cancel = True

if you don't want the record to be saved.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)