Stop the next button creating new record 1

[Steven811]

Hi

My own nav buttons work well with the exception of the next button. I only want it to go to current records and not past them as I have another button for that function.

How would I modify the code?

Here is what I currently use:

[Private Sub cmdGotoNext_Click()
On Error GoTo cmdGotoNext_Click_Err

Screen.PreviousControl.SetFocus
DoCmd.GoToRecord , "", acNext

Thanks

Steven811

 

[RoyVidar]

One version, could be to add the following test:

[tt]DoCmd.GoToRecord , , acNext
if me.newrecord then DoCmd.GoToRecord , , aclast[/tt]

Else you could also toggle the .AllowAdditions property of the form

Roy-Vidar

 

[randy700]

How about...

In the forms OnCurrent event, check to see if you are viewing the last record in the recordset and disable the Next button.



Randy

 

[jbento]

Good post!!!

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[combs]

randy700,

How do you check to see if you are viewing the last record in order to disable the Next button?

 

[paulsmoothcurt]

You could use EOF to check, but I think personally its easier to set the forms allowadditions property to false, and then just change it programmatically with a button if you need to add records. Seems the easiest method.

 

[PHV]

How do you check to see if you are viewing the last record
You may try something like this :
Me.RecordsetClone.MoveLast
If Me.RecordsetClone.Bookmark = Me.Bookmark Then...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[combs]

First, thanks for your reply - I love these forums!

Do I need any special references or libraries to use the RecordsetClone syntax??? I don't think so, but when I put it in the code (below) I get an error message:

Run-time error '13':
Type mismatch

    If IsNull(TaxID.Value) Then
        MsgBox "You must enter a Tax ID for this record first.", vbInformation, "Enter Tax ID:"
        TaxID.SetFocus
        Exit Sub
    Else
        Me.RecordsetClone.MoveLast
        [yellow][b]If Me.RecordsetClone.Bookmark = Me.Bookmark Then[/b][/yellow]
            MsgBox "There are no more records to view." & vbNewLine _
& vbNewLine & "If you want to create a new record click on the add " _
& "record button." & vbNewLine & "Otherwise click the Close Form to exit.", vbExclamation, "No More Records:"
        Else
            DoCmd.GoToRecord , , acNext
        End If
    End If

The line that is highlighted in the debugger is the following:

If Me.RecordsetClone.Bookmark = Me.Bookmark Then

I would appreciate any tips you could offer to get this working.

 

[RoyVidar]

I would try something like this:

[tt]me.recordsetclone.movelast
if me.recordsetclone.recordcount=me.currentrecord then[/tt]

I haven't tested this thoroughly, I guess perhaps a test for me.newrecord might fit in? If this doesn't work, perhaps elaborate on which event you're using.

Roy-Vidar

 

[combs]

RoyVidar,

That works beautifully! I'm not entirely sure why that line works and the other doesn't, but am happy that I finally have something that will work...

If you or anyone has the time and would care to elaborate on the differences between the two line of code (below), I would find it intersting.

This line did not work:
[b]If Me.RecordsetClone.Bookmark = Me.Bookmark Then[/b]

This line does work:
[b]If Me.RecordsetClone.RecordCount = Me.CurrentRecord Then[/b]

Thanks again and a star for you --

 

[TheAceMan1]

[blue]If you or anyone has the time and would care to elaborate on the differences between the two line of code [/blue]

Well . . . . right off the top:

[blue]        Me.RecordsetClone.MoveLast
        If Me.RecordsetClone.Bookmark = Me.Bookmark Then[/blue]

should've worked. The fact that it didn't tells me you [blue]Access Version[/blue] is 2k or higher. [purple]ADO[/purple] (ActiveX Data Objects) was introduced in 2K and will be in conflict with [purple]DAO[/purple] (Data Access Objects) unless a reference object is explicitly specified. The following should work:

[blue]   Dim rst As [purple][b]DAO[/b][/purple].Recordset
   
   Set rst = Me.RecordsetClone
   rst.MoveLast
   If Me.Bookmark = rst.Bookmark Then
      'Your Code
   End If[/blue]

To explain the above: each record in a form is [blue]assigned an unique bookmark[/blue]. The RecordsetClone is a [blue]copy of those records including the assigned bookmarks.[/blue] [purple]MoveLast[/purple] takes you to the last record in the copy which is the same as the last record on the form. [purple]So if the bookmarks match, you know your on the last record.[/purple]

As for:

[blue]If Me.RecordsetClone.RecordCount = Me.CurrentRecord Then[/blue]

[blue]Me.RecordsetClone.RecordCount[/blue] is the total number of records in the recordsetclone, hence in the form. [blue]Me.CurrentRecord[/blue] is the record number (same as in the navigation bar). So [blue]Me.CurrentRecord[/blue] will be the same as the [blue]RecordCount[/blue] when your on the last record.

See Ya! . . . . . .

 

[combs]

AceMan,

Excellent description/explantion of what's going on with this code. Makes sense to me now. Thanks for taking the time to elaborate on this...

That's what makes these forums so valuable - MVP's who have the knowledge and are willing to share. You guys rock!