Where to put error handler code for "No current record" error?

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









0
Ivan
12/6/2007 2:40:53 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1582 Views

Similar Articles

[PageSpeed] 59

Ivan,

itemID is a combobox. Combobox's have an event called On Not In List. Use On
Not In List to handle any items not found in the list. You must also set
"Limit to List" to Yes. I can't think of the vb off the top of my head for
this event, but if you did a search on it, you'll get a lot of useful samples
I'm sure.

HTH

Dave


Ivan wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1

0
David
12/6/2007 8:33:37 PM
Reply:

Similar Artilces:

Where to put error handler code for "No current record" error?
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 'tblOr...

No Current Record error
I have a form with a subform on it. When I open the form with the acFormAdd option (to add a new record) I get the error "No Current Record" on the following: Me.RecordsetClone.MoveLast which is in the load event of a subform on the form. The statements Me.RecordsetClone.MoveLast Me.RecordsetClone.MoveFirst are in my subform so the "Item n of n Items" label box will show the correct number of items. How can I avoid this error message? Robert When you open a form in add mode, it does not load any records. Consequently, the instruction to move to the ...

No Current Record Error
When I run a microsoft scheduled task to run a macro daily to send an emailed report as a PDF I am receving the "No Current Record Error" in Access 2007. What is odd, is that the macro will run successfully if ran manually from Access but will not run with the scheduled task. Also, it is running successfully about 40% of the time, leading me to believe that there are certain changing data elements that are driving the error. Any information on this error is greatly appreciated. I can also "cancel" through the error message and the report will send out bu...

No Current Record error
I am running an integration using 2 sources and the SQL Optimizer as the destination. I keep getting errors that say "No Current Record". Can anyone tell me what is causing this error and/or how to fix it? Thanks, The integration runs perfectly when I use the same 2 sources and the general journal as the destination. The problem comes when I switch the destination to use GL Transaction under SQL Opt 7.5. Other than that, everything is set up right as far as I can tell. Any ideas why that "No current record" error occurs? ...

No Current Record (its driving me crazy!)
I've tried to research other posts on the this topic but can't find one that meets my situation. I have a form and subform. The subform is a continuous form. Its properties are set to AllowAdditions = False AllowEdits = True AllowDelete = True After a user enters the parent information there is a button to click that gets the children records. The button that runs this is actually on the subform itself. When clicked, an append query appends 5 child records for this parent and requries the subform. The user now sees five child records that are partly filled in and they can enter ...

Error 3021 "No current record" on opening report
I have an application in which I have re-designed the query which is the record source of a report. The query has exactly the same fields as before, and it returns about 10 records from the existing data. However, the report will no longer open; instead I get error 3021 "No current record". This happens both from the command button on a form (there is no Where parameter in the DoCmd.OpenReport statement), and when attempting to open the report directly from the database window. Any suggestions as to what the problem could be? I've done a search of the groups, but foun...

No Current Record on form close
I think I know what's causing this but not how to fix it. I have a subform tied to my main form and when I close the main form when the subform has no records, I get a "No Current Record" error (no error number, just the text). When I close my form and the subform has associated records, everything is fine. How can I bypass or prevent this dialog box from appearing when I close a main form with no related records in the subform? Thanks! When you close a form with a SubForm, the MainForm closes 1st and then the SubForm will close. Do you have some code in the OnClose or ...

Problems with Error 3021
Using VB6 - SP5 MS Access (MDB) data file. I'm having problems with Error 3021 "No Current Record" error. I have reviewed the code - every .Update statement is preceded by either a ..AddNew or a .Edit. In an attempt to find where this is occurring, I have every instance of data file access (seek/read - edit - addnew - update) surrounded with a "On Error GoTo" structure which will log the error to an event log that is already part of the program. But the program has crashed several times (with Error 3021) since that trapping was added. I just read KB10649...

runtime error 3021 No current record
Hello, I got this runtime error only when I run the db at home computer. The error happened when created new record and stopped at line "doCmd.domenuitem acformbar, aceditmenu, 6, , acmenuver70". This line of the code is under cancel button. Can anyone help please? Thank you in advance! If this is a command button that is meant to undo any changes to the current record in your bound form, you could try replacing the code with this: If Me.Dirty Then Me.Undo If that's not what you are doing, let us know. -- Allen Browne - Microsoft MVP. Perth, Western Australia ...

No Current Record
When I delete a record from my from which is a Tabular format I get this error message "No Current Record" Thanks for any help...Bob "Bob" <xxx@xx.xx> wrote in message news:f1b6sl$u3v$1@lust.ihug.co.nz... > > > When I delete a record from my from which is a Tabular format I get this > error message "No Current Record" > Thanks for any help...Bob > > Actually only happens when you try to delete the last row and then the blank row has focus..Thanks Bob There is a bug in Access 2002 SP3 that generates this record wrongly. Just ...

No Current Record
Good Afternoon, -- KMJ >! Message posted via http://www.accessmonster.com Hello I have a problem with a DAO Recordset. i keep receiving a "No Current Record" error. I know WHY i am getting this, it is because the SQL statement returns nothing. i want to account for this event. i want the text field on the form to display zero. this seems like it should not be this hard here is the code: For i = 0 to 10 LocationID = MyArray(i,0) sqlCode = "a sql statement" Set WRC_RS = mydb.OpenRecordset(sqlCode) WRC_RS.MoveFir...

No current record after undo
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 ite...

ListView insert
I have a ListView that I am using to edit and insert rows into a database table. I am displaying records for a specific person and existing records edit and update fine. Also, if a person already has records in that table, the ListView DropDownList population and insert works fine. The problem is that when a person has no records in the table (ListView is empty) the DropDownLists are not getting populated. I assume maybe because the DDL populating is being done in the ItemDataBound event? If so, where is the best place to populate the dropdowns for insert? Below is some of ...

Access 2007 "no current record" on split form
Hi, I am using VBA in Access 2007 to have a split form with a searchable text box. On new record entry the code will search for an existing entry in the recordset clone and if it exists will undo the control's changes and then set the form's bookmark to the found record's bookmark. This all work fine if the value is entered on the form half of the split form but if it is entered on the datasheet portion then I am given a "No current record" message box or a "Property not found" message box. It still finds the record but shows these message boxes after ...