Help With Error Handler

[Evagrius]

Hi Everyone,

I need a bit of help with a simple error handles. I have this code in a dbl click event of a listbox. The error occurs when there is no records to match the filter.

On Error GoTo MyErrHandler
 Rem FILTER BY SELECTION
    Me.Filter = "[StockPrice] = 'UnderV' And " & "[" & MyField & "]" & MyStr
    Me.FilterOn = True
    Me.OrderBy = True
    Exit Sub
 
MyErrHandler:
MsgBox Err.Number: Exit Sub

I also have this code in the on current event of a form

Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
Rem SHOW RECORD NUMBER IN TXTBOX
Me.txtNavigation = "You are at record " & Me.CurrentRecord & " of " & lngCount

When I step through the code in the list box, the error actually occurs in the code located in the on current event of the form. I am trying to trap the error in the listbox dbl click event . . but it's not catching it there? I moved the error handler to on current event, but once I trap the error there, the code continue from where it left off in the dbl click event and I get a second error. I would be grateful for any help!

 

[Kryst51]

What is the error you are getting?

 

[Evagrius]

Hi - the error is for no records found . ."no current record" NO. 3021

 

[vbaInet]

What is the exact error message?

 

[Kryst51]

I suppose you need some kind of test for no records...

I don't know how to do that exactly. But here is a link that might help you.

And the pertinent part:

3. Move with no records

Using any of the Move methods (MoveFirst, MoveLast, MoveNext, or MovePrevious) causes an error if the recordset has no records.
Solution:

Test before using any of the Move methods. Either of these approaches works:
If Not (rs.BOF And rs.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true. If rs.RecordCount <> 0 Then '100% reliable in DAO, but some ADO recordsets return -1 as the count.

 

[Evagrius]

Hi - the exact message is "No Current Record" Runtime 3021. The error is correct because once I apply the filter, there is no record to match the criteria. I would like to either capture the error - or is there a way to determine if a certain filter returns 0 records?

 

[vbaInet]

Hi - the exact message is "No Current Record" Runtime 3021. The error is correct because once I apply the filter, there is no record to match the criteria. I would like to either capture the error - or is there a way to determine if a certain filter returns 0 records?

I was quoting your original message before you editted it.

rst.MoveFirst is causing the error. If there are no records you can't move first. You need to test using BOF and/or EOF, or using RecordCount.

 

[Evagrius]

Thanks Guys - forgive me for being dense.

The below code seems to be working partially. Once the msgbox is invoked, my form remains at an empty record, how do I force it to revert to record 1? I also get the msgbox twice, once for "FilterOn" = True and once for "OrderBy" = True. Thank you again for your help.

Private Sub Form_Current()

Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
If rst.RecordCount <> 0 Then
    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
Rem SHOW RECORD NUMBER IN TXTBOX
Me.txtNavigation = "You are at record " & Me.CurrentRecord & " of " & lngCount
Else
MsgBox "No Records found"
End If
End Sub

 

[vbaInet]

I just noticed that Kryst had already showed you a link of what I said in my last post

You MoveLast before you MoveFirst.

 

[Kryst51]

I just noticed that Kryst had already showed you a link of what I said in my last post

Not a problem, better more help than not enough eh?

 

[Evagrius]

Sorry Fellas - The link did help, but I am still struggling with the last post. Any advice?

 

[vbaInet]

Right, is there any reason why you don't want to use =Count(*) in the footer of your form?

Why are you performing a record count in the On Current event? That doesn't make sense unless you were counting the records in the subform will it be worthwhile. Perform the count in the Load event of your form.

 

[Evagrius]

Hi vbaInet. Are you refering to this?

        lngCount = .RecordCount

I don't mind changing the recordcount, but ould changing that resolve the issue? Thanks . .

 

[vbaInet]

I'm talking about the whole code.

 

[Evagrius]

I understand vbaInet - I am trying to learn fast - this is the only method I could think of based on what I know - but hopefully as I learn I will be more efficient. I'll keep trying to figure this one out. Thanks agian for you help! Thanks as well Kryst51!

 

[vbaInet]

If you re-read my post #12 you will find that I mentioned an alternative method to what you're trying to do.

 

[ChrisO]

I think you will find that there is far too much needles code in the On Current event.

You could try this and see how it goes: -

Private Sub Form_Current()

    Me.txtNavigation = "You are at record " & Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
    
End Sub

If that works without error, and it does for me, then we can look at the filtering code if need be.

Chris.

 

[Evagrius]

Hi ChrisO - that worked perfect - I just didn't know it could be that simple! THANK YOU!!

I also used this to revert the form back to record 1 after the filter shows a blank record. Please let me know if this can be done better! Thanks!

    Me.Filter = MyString    
    Me.FilterOn = True
    Rem CHECK TO SEE IF ANY RECORDS FOUND
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    
    If rst.RecordCount = 0 Then
        DoCmd.RunCommand acCmdRemoveAllFilters
    Else
    Me.OrderBy = True
    End If

 

[ChrisO]

Before we move on it might be an idea to explain the methodology used in the On Current event.

You have two distinct needs.

The first is to display the current record number of the total number of records; that is being handled by the On Current event.

The second is to change the records in the recordset clone; that is being handled by the list box code.

The filtering affects the change; the On Current event displays the effect.

One should have nothing to do with the other.

It is not the job of the On Current event to make a change (and it had better not else you will end up with a circular argument) or to display the message boxes or to revert back to some other recordset. Its job is to simply display the current status.

To demonstrate, let’s comment your original code: -

Private Sub Form_Current()
    Dim rst      As DAO.Recordset   [color=green]' Needlessly create rst and a dependency on a DAO reference.[/color]
    Dim lngCount As Long            [color=green]' Needlessly create a Long variable.[/color]
    
    Set rst = Me.RecordsetClone     [color=green]' A needles transfer of equivalence.[/color]
    
    If rst.RecordCount <> 0 Then    [color=green]' Why not just use the RecordCount of the RecordsetClone?[/color]
        With rst
            .MoveFirst              [color=green]' Why move first on a new recordset?[/color]
            .MoveLast
            lngCount = .RecordCount [color=green]' A needles transfer of equivalence.[/color]
        End With
        
        Rem SHOW RECORD NUMBER IN TXTBOX
        Me.txtNavigation = "You are at record " & Me.CurrentRecord & " of " & lngCount
    Else
        MsgBox "No Records found"   [color=green]' Why involve the user for input?[/color]
    End If
    
End Sub

When we strip out the junk we end up with this: -

Private Sub Form_Current()

    Me.txtNavigation = "You are at record " & Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount

End Sub

And please don’t think it’s only you who needlessly creates a recordset based on the recordset clone; you are in very good company when you do that sort of thing. But it doesn’t matter who does it, the recordset of a recordset clone is just a copy of the recordset clone so why work on a copy when we can work on the real thing?

All it does is force a needles dependency on having DAO referenced and that reference may not be the correct version of DAO for the job.

-------------------

So as to your last question, I’ll let you try and strip out the junk. Just bear in mind that none of that code should go in the On Current event. Just let the On Current event do the job it is currently doing.

Try it and post back if you run into problems.

Chris.

 

[Evagrius]

ChrisO - thank you very much for the guidance. I will be working on this now and I will try to adhere to all your suggestions. Thank you for taking the time to explain the code also - I've been going through your explanations and they are very helpful!!