How can this be "no current record"?

  • Thread starter Thread starter Kelii
  • Start date Start date
K

Kelii

So, this is probably the simplest form in my database and it
intermitently kicks out an error "No current record." The help screen
is close to worthless, and hours of searching our newsgroup has only
made me more confused.

The error occurs when I close my form. However, the error does not
occur regularly, it only occurs when I navigate from one record to the
next, using my next record command button. This is to say, if I open
my form, then close it ... no error occurs. Furthermore, the error
does not occur consistently on any particular record. For example, if
I simply navigate to the 3rd record, then the form will close with no
error. However, if I navigate to the last record, back to the first
record, then to the third record, the form will close with the error.
Finally, the error is not trapped in any of my error handlers (which I
have dutifully included on every single sub and function). I have
tried trapping the error in a Form_Unload event, however, the error
occurs before this event. Furthermore, I included Debug.Print
Me.CurrentRecord in the Form_Unload event and I'm getting the proper
record printed, even when the No current record error is generated.

My question: does anyone have a sense of where to start debugging this
problem, or in a perfect world how to fix this?

My suspicion is that the error is being driven by the code in my
Form_Load sub, in particular the RecordsetClone and Bookmark pieces,
which admittedly I am hazy on understanding. The code is included
here:

Private Sub Form_Load()
On Error GoTo Error_Handler

'If this form loads with open args, then the item has already been
'entered and the unit of measure data should not be enabled
'Convoluted load procedure allows navigation buttons to work
If Len(Me.OpenArgs) > 0 Then
'Disable the unit of measure fields
With Me.Item_Unit_of_Measure
.Enabled = False
End With
'Go to proper record and allow navigation buttons to work
With Me.RecordsetClone
.FindFirst "Item_Description_ID = '" &
FindFirstFixup(Me.OpenArgs) & "'"
If .NoMatch Then
MsgBox "Item Not Found", vbOKOnly, "Item Lookup"
Else
Me.Bookmark = .Bookmark
End If
End With
End If

'Update subform windows
DateAnalyze

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub

The code in my navigation buttons may also be contributing to the
confusion.
Private Sub cmdFirstRecord_Click()
On Error GoTo Error_Handler

DoCmd.GoToRecord , , acFirst

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub

Private Sub cmdPreviousRecord_Click()
On Error GoTo Error_Handler

DoCmd.GoToRecord , , acPrevious

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub

Private Sub cmdNextRecord_Click()
On Error GoTo Error_Handler

DoCmd.GoToRecord , , acNext

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub

Private Sub cmdLastRecord_Click()
On Error GoTo Error_Handler

DoCmd.GoToRecord , , acLast

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub
 
To ensure that that your form loads quickly, it does not actually load all
the records into memory before the form's Open and Load events complete. If
you watch the form, you may see a second delay (even several seconds if
there are lots of records) before it shows the number of records before the
Nagivation Buttons (left end of horizontal scrollbar at the bottom of the
form.)

Since the records are not all loaded yet, the attempt to FindFirst in
Form_Load may not succeed. You can force it to load all records with a
MoveLast. This will slow down the loading of the form, but the FindFirst
will then succeed.

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast 'Force all records to load.
.FindFirst "Item_Description_ID = '" & _
FindFirstFixup(Me.OpenArgs) & "'"
If .NoMatch Then
MsgBox "Item Not Found", vbOKOnly, "Item Lookup"
Else
Me.Bookmark = .Bookmark
End If
End If
End With
 
Allen,

First off, thank you for your response. Second, thank you for your
website, it has proven to be very valuable for me.

As far as my No Current Record error is concerned, I put your
suggested code in, and I'm still getting the error.

A point of clarification: I'm using custom navigation buttons, not the
standard access buttons at the bottom of the form (see code in prior
post). That said, I enabled the navigation buttons property on my
form, and indeed all the appropriate records are loaded (i.e., the
total records are shown at the correct value) before I close the form
and receive the error.

The form has 3 subforms in a Tab Control; I doubt this is a
contributor to the issue, but hey you never know. 2 of the subforms
are pivot charts, and the 3rd is a simple Datasheet.

The record source for the form is fairly straightfoward, with 1 hard
coded criteria:
SELECT tblItemDetails.Item_Description_Number, tblItemDetails.Class,
tblItemDetails.Item_Description_ID,
tblItemDetails.Item_Unit_of_Measure, tblItemDetails.Item_Location,
tblItemDetails.Item_Type, tblItemDetails.Item_Category,
tblItemDetails.Item_SKU, tblItemDetails.Active_Status,
tblItemDetails.Item_Par, tblItemDetails.Item_EOQ,
tblItemDetails.Sub_Assembly_Total_Yield, tblItemDetails.Memo
FROM tblItemDetails
WHERE (((tblItemDetails.Class)="Item"))
ORDER BY tblItemDetails.Item_Description_ID;

tblItemDetails.Item_SKU is boolean; everything else is single / double
or string.

There is some straightforward code in the Form_OnCurrent and
Form_BeforeUpdate events, that may be contributing to the problem, but
alas I doubt it.
Private Sub Form_Current()
On Error GoTo Error_Handler

'If the form is a new record, then enable UOM fields
If Me.NewRecord = True Then
Me.Item_Unit_of_Measure.Enabled = True
Else
Me.Item_Unit_of_Measure.Enabled = False
End If

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Handler

'Check that Item Description is complete before saving
If Me.Item_Description_ID.Value = "" Or
IsNull(Me.Item_Description_ID) Then
MsgBox "Please enter a valid name for this item.", vbOKOnly,
"Invalid Name"
Me.Item_Description_ID.SetFocus
Cancel = True
Exit Sub
End If

'Check that Unit Of Measure is complete before saving
If Me.Item_Unit_of_Measure.Value = "" Or
IsNull(Me.Item_Unit_of_Measure) Then
MsgBox "Please enter a valid unit of measure for this item.",
vbOKOnly, "Invalid Unit"
Me.Item_Unit_of_Measure.SetFocus
Cancel = True
Exit Sub
End If

'Check that Class (not shown) is set to Item (not SA) before
saving
'Prevents saving new records without class and generating an error
If IsNull(Me.Class.Value) Then
Me.Class.Value = "Item"
End If

'Check that Sub_Assembly_Total_Yield (not shown) is set to
'1 before saving
If _
IsNull(Me.Sub_Assembly_Total_Yield) Or _
Me.Sub_Assembly_Total_Yield = "" Or _
Me.Sub_Assembly_Total_Yield = 0 Then
Me.Sub_Assembly_Total_Yield.Value = 1
End If

'Check that Category, Type and Location are populated
'if not, then set to "<none>"
If Me.Item_Category.Value = "" Or IsNull(Me.Item_Category) Then
Me.Item_Category = "<none>"
End If
If Me.Item_Type.Value = "" Or IsNull(Me.Item_Type) Then
Me.Item_Type = "<none>"
End If
If Me.Item_Location.Value = "" Or IsNull(Me.Item_Location) Then
Me.Item_Location = "<none>"
End If

'Check for save form changes dialog
CheckForSave

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select

End Sub

If I just knew what event was generating the error, then I could at
least get started debugging the form; however try as I might, I cannot
find which event is the culprit. Given that the error isn't trapped in
any of my Error_Handler code, it would seem that none of my code is
directly associated with the error.

Anyhow, any further thoughts you might have would be very much
appreciated.

Best,

Kelii
 
Ok, so after writing the above post, I thought...why not delete the
subforms and see if I still get the errors. So I did, and guess
what ... no errors.

Now, some of the records on the main form have no data in the subforms
(i.e., no sales, inventory, or recipes have been associated with the
record). Would this be the offending issue? If so, what would be an
appropriate work around?

Just to be clear, the subforms recordsources show no records when
viewed in the query editor.

Thanks again,

Kelii
 
Allen,

Thanks for the links. Enjoy your time away, hope you are going
somewhere that is either interesting or fun.

I'll post again once I solve the problem.

Best,

Kelii
 
Well, I think that I have isolated the problem.

Short recap, the form kicks out a No Current Record Error upon close.
The form has 3 subforms, 2 are pivot chart views, the 3rd is a
datasheet view. If the pivot chart has no data (i.e., no records in
the underlying query) then the error occurs. If there is data in the
underlying query, then the error does not occur. If there is data in
one pivot chart but not the other, then I only get one No Current
Record Error, not two (as is the case if both pivot charts have no
data).

An interesting point: neither pivot chart utilizes the Link Child
Fields, Link Master Fields property. However, the datasheet does use
these properties. The datasheet does not appear to ever generate the
error. Yes, I will try using these properties on the pivot chart to
see if it works.

Will update this post shortly.

Kelii
 
Alright, well that didn't do a damn thing to help.

So following on the previous post - I changed the pivot charts to use
the Link Child / Link Master Fields properties.

The underlying query for each of the pivot charts is relatively
straightforward with just one criteria linked to the master table re:
dates to display.

If anyone has even the slightest clue as to what to do to work around
or fix this error, I would sincerely appreciate the help.

Best,

Kelii
 
This post is just in case someone stumbles across this error under the
same scenario.

Ok, so I didn't find a solution, but I found a damn good workaround
thanks to the Access 2002 Developer's Handbook (Sybex: Litwin, Getz,
and Gunderloy).

So, I didn't even know this, but you can build an error trap around a
form error ... go figure. I know, you guys out there have done it a
million times, but alas I am stupid :D.

Briefly, the error is trapped in the Form_Error event, where it
occurs. You handle the error by ignoring it, since it my case, the
error is really only an inconvenience, it has no data integrity /
business rule ramifications.

Here is the code (courtesy, mostly, from the bright guys above):
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Error handler for form
'Here it is being used primarily for error 3021,
'No Current Record, which is generated "fairly" regularly
'by the subform when no data exists in the pivot chart

Const adhcErrNoError = 0
Const adhcErrFieldNull = 3162
Const adhcErrDuplicateKey = 3022
Const adhcErrNoCurrentRecord = 3021

Select Case DataErr
Case adhcErrNoError 'No error
Case adhcErrFieldNull 'Field can't contain a null value
MsgBox "You have left one of the required fields blank. "
& vbCr & _
vbCr & "If you would like to skip this item, enter
zero. " & vbCr & _
vbCr & "If you would like to delete this item then " &
vbCr & _
"enter zero first then select the item and delete
it.", vbInformation
Case adhcErrDuplicateKey 'Duplicate value in index
MsgBox "You have entered a duplicate item, please select "
& _
"the item from the list.", vbInformation
Case adhcErrNoCurrentRecord 'No real error here, subform has
no data
'so don't do
anything
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Stop
End Select
Response = acDataErrContinue
End Sub

Thanks again to Allen Browne for his help.

Kelii
 
Back
Top