Stopping end of recordset error

[fraser_lindsay]

Hi,

How do I stop the user getting teh end of recordset error when they try to navigate before the first or beyond teh last records on a form?

I have looked for "supress end of recordset error" and "loop at end of recordset " plus various other combinations but I keep getting things a tad too complicated for my needs.

I just want the macro I have on the previous/next buttons to loop to the first record again and stop the error appearing.

I tried adding 'on error' 'Run Macro' then run a macro which goes to the first record in my embedded macro but it doesn't seems to work or rather I'm probably not setting it correctly.

 

[KenHigg]

So you have a basic form bound to a table? Then instead of the user being able to add a new record when they try to go past the last one you want them to stop at the last record and as an option maybe be taken to the first record?

 

[fraser_lindsay]

Ken,

Yes, a number of 'fairly' basic forms. Some have subforms.

Currently when the user presses previous or next in either direction within the recordset and gets to either end an error is triggered. I'd rather it just loops round so that the user doesn't think it's broken.

I have buttons with macro's embedded to go to the previous, next and new records.

 

[KenHigg]

One simple solution may be to set the forms "Allow Additions' property to 'No' - ?

 

[fraser_lindsay]

Ken, thanks, but I would still need the ability of users to be able to enter new records it's just that I don't want the error when they are pressing next or previous and get the end of the recordset.

 

[Mike375]

There is an easy way to do this with a pair of macros and an unbound textbox.

Firstly, at the end of the records is easy. In your macro that has GoToNextRecord add another action line for GoToFirstRecord. In the condition column put [FieldNameWhatever] Is Null. Pick a field like the ID or LastName etc that alway has entry. That will cause it to go straightto the first record

It is the first record which is the problem.

In an unbound textbox put =[CurrentRecord]

That will show a 1 when you are in the first record. If combined with =Count([WhateverFieldName]) it will also allow you to duplicate the navigation button's 19 of 456 records etc.

So your macro for GoToPreviousRecord has the textbox with =[CurrentRecord] for the condition column. That will also allow you to use a message box, open a form with a message etc.

If you also want to use =[CurrentRecord] for the last record then make another unbound textbox for =Count([WhateverFieldName]). When you are on the last record the value in both textboxes is the same.

For some reason the code version of GoToPreviousRecord, that is, DoCmd.GoToRecord , "", acPrevious has never worked for me, the screen locks up.

 

[Mike375]

PS

I forgot. For the record of record

="Record" & " " & [Text120] & " " & "of" & " " & [Text116] & " " & "Records"

Text120 has =CurrentRecord and Text116 had =Count

 

[fraser_lindsay]

Mike,

Excellent stuff, the next button now loops perfectly.

Also, the new record selector box was a useful bonus, thanks for that.

However, I'm still having problems with the previous button. I have added the name of my text box (the one holding =[CurrentRecord]) into the criteria against gotorecord - previous, but it still spews out the ugly error.

However, I followed Pat's suggestion and found a post from ghudson with some code for custom buttons. I copied this to my button VBA code:

If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance from this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
    ElseIf Me.CurrentRecord > 1 Then
        Beep
        DoCmd.GoToRecord , , acPrevious
    End If

The custom error box that he has doesn't appear for me, so I didn't mess with it. I don't need it anyway. There is no error message, the button just won't go past the first record which is exactly what I want. Plus with your suggestion for the new record count box the user can now see that they are at record 1 of x.


Thanks

 

[Mike375]

As I mentioned above I have problems with code for GoToPrevious.

Make it a macro.

Assume =CurrentRecord is Text1

First macro action line is GoToRecord and Previous

In the condition column have

[Text1] > 1

If you want a message then best to use a form you make your self. One reason being that a MsgBox will open when the navigation buttons are showing record 2(if you decide to show them). The other reason to use a normal form is you put a Close Form action (for the form itself) and it is put on the OnTimer Event. Maybe for 750 to a 1000, that is 3/4s to 1 second. So the form opens with the message and then automatically closes itself.

So on the second line of the macro put

OpenForm action and the condition is [Text]=1

Also, I prefer to use the full reference when a couple of forms will be active. In macros you use the [] like in

[Forms]![FormName]![Text1]

If you don't like using macro then just for this case, put your hands over your eyes while making it

 

[Mike375]

PS

ElseIf Me.CurrentRecord

You need to refer to the textbox that has =CurrentRecord

 

[fraser_lindsay]

Gents,

Thanks again for all of your help, very much appreciated. I've been experimenting with all of your options. I have a mismash of code and macros now, so thank you.

Mike, I like your suggestion about the pop up and self closing form and I'm messing with that just now.

I have five forms with custom navigation buttons on a button bar at the top. Four out five have worked perfectly. The thorn in my side at present in the one form that refuses to work and I can't see why.

It will trigger the error form on the previous button, but for some reason when you go past the end record it tries to insert a new record, triggers the form but then fires the ugly macro error routine.

The other forms loop round on next and just stop at previous, which is fine. I have no idea why this form refuses to play ball.

 

[Mike375]

Fraser,

The attached has just a macro in it

The textbox in the condition column has =[CurrentRecord] as its data source so change tha to the name of your textbox.

For now, delete the section action line which is opening the form.

Initially you just want the thing to work. Just stick the macro on a stand alone label, behind a text box or whatever, fast and simple.

That simple macro will stop it trying to go back past record 1.

but for some reason when you go past the end record it tries to insert a new record

Do you nay code or a macro that is insering data on the OnCurrent event.

 

[fraser_lindsay]

Mike,

Thanks for that. My macro was setup as you explained. Previous is working perfectly, thanks.


Next works on the others, but not this form. You are correct though, this particular form does have code in the OnCurrent event. It basically checks the status of a text box and then makes some controls visible and others hidden according to the value.

Here it is:

Private Sub Form_Current()

If Me.Risk2 = "High" Then
    Me.imgRed.visible = True
    Else: Me.imgRed.visible = False

End If

If Me.Risk2 = "High" Then
    Me.lblHighRisk.visible = True
    Else: Me.lblHighRisk.visible = False
    
    End If

If Me.Risk2 = "Moderate" Then
    Me.imgOrange.visible = True
    Else: Me.imgOrange.visible = False

End If


If Me.Risk2 = "Moderate" Then
    Me.lblModerateRisk.visible = True
    Else: Me.lblModerateRisk.visible = False
    
    End If


If Me.Risk2 = "Low" Then
    Me.imgGreen.visible = True
    Else: Me.imgGreen.visible = False

End If


If Me.Risk2 = "Low" Then
    Me.lblLowRisk.visible = True
    Else: Me.lblLowRisk.visible = False
    
    End If
'This text will copy the value in the named control to another named control
Me.txtRisk = Me.Risk2.Value

'This code copies the value from one control to another control
Me.txtSumJSA_HS = [tblHazard Subform].[Form]![txtHS_Total]

End Sub

So that's probably what's causing it then? How do I correct that while maintaining the code?

Do you want me to upload a copy (it's 2007 format?)

 

[Mike375]

I don't think that should cause a problem as there is nothing there that would cause a record to start. But maybe something different in 2007.

First thing to do is to remove the OnCurrent code and see if that fixes the problem with Next.

Remember that when you go to Next Access effective allows you to go to a new record. That is why the macro that takes you back to the start works, because it says...If field xyz is Null then go to record 1...

If you were to put a simpe macro or code on the OnCurrent that set the value of a field to "X" then you could keep the navaigation button for next pressed and it would keep going forever EXCEPT if the DB required some other field to have an entry THEM all the Halts/Debugs would surface.

Pull the code of OnCurrent and see what happens. If you are not already using a copy of the DB then use a copy as that gives you are free hand. You are free to stuff up how forms look etc and that makes things faster to do.

 

[fraser_lindsay]

Good work Mike, that's the annoying event that's messing my nice form presentation and navigation up!

Removed it and it works fine.

Hmmm. Now I have get both working together.

 

[Mike375]

PS.

If the code is a problem then it is easy to leave there by doing next differently, but still test it as you learn.

If the code is a problem then you change the macro that goes to next and the condition is [Fieldabc] Is not Null to something else.

Add another textbox and for the data source have =Count([IDField]) or I think =Count(*) will also count all the records.

Lets say the textbox that has =CurrentRecord is called Text1 and the textbox with =Count is called text2.

Then the condition column for the macro action that does GoToNextRecord has the condition Text1<Text2

Say you have 100 records. Then =Count(*) will always show a 100 but =CurrentRecord shows the number of the record you are on and so for all records up to the second last record =CurrentRecord will be < =Count(*) but when you hit the last record then =CurrentRecord will be equal to =Count(*) and thus the macro won't run once you are on the last record. You could then have a form open when =CurrentRecord will be equal to =Count(*) with Msg...Do you want to go back to the first record....and if they do what they click just runs a macro for GoToFirstRecord.

In a nutshell because =CurrentRecord gives the number for the record and =Count gives the total you can do what you like.

 

[Mike375]

See last post..made as you were posting

 

[fraser_lindsay]

Mike, you were spot on, once again. It seems so simple when you put it like that. Too simple for me it would appear.

That works perfectly, and I can now trigger the form I created to tell the user they are at the end and they need to navigate in the opposite direction (I use the same form both both directions). Best of all there is no ugly multiple macro error box.

Thanks again for all of your help.

 

[Mike375]

Happy to be of some assistance

 

[fraser_lindsay]

Here's a picture to show the result of all your hard work...