Stop ADD rec at the end of recordset

[Carl_R]

I have two forms:
One form is for adding a record, the other for editing an existing record.

I have some custom buttons for navigation on the form used for editing.

When navigating to the end of the recordset in the editing form, the user is presented with a blank form (as in ADD mode). I don't want the user to be able to ADD a record via this form.

Any ideas on how to stop this?

Access97

 

[Mile-O]

Using your navigaton buttons (and a little modification) you could use code like this:

Private Sub Form_Current()
   
    Dim rsClone As Recordset
    Dim intNewRecord As Integer
    
    ' Make a clone of the recordset underlying the form so we can move
    ' around that without affecting the form's recordset
    Set rsClone = Me.RecordsetClone
    
    ' If this is a new record then disable the <NEXT> button and enable
    ' the <PREV> button and then exit the procedure
    intNewRecord = IsNull(Me.PartyID)
    
    If intNewRecord Then
        cmdFirst.Enabled = True
        cmdPrev.Enabled = True
        cmdNext.Enabled = False
        cmdLast.Enabled = False
        Exit Sub
    End If
    
    ' If we reach here, we know we are not in a new record so need to
    ' see if there are no records. If this is the case then we must
    ' disable both the <PREV> and <NEXT> buttons.
    
    If rsClone.RecordCount = 0 Then
        cmdPrev.Enabled = False
        cmdNext.Enabled = False
        cmdFirst.Enabled = False
        cmdLast.Enabled = False
    Else
    
    ' Synchronise the current pointer in the two recordsets
    rsClone.Bookmark = Me.Bookmark
    
    ' If there are records, see if we are on the first record.
    ' If so, we should disable the <PREV> buttons.
    
    rsClone.MovePrevious
    cmdPrev.Enabled = Not (rsClone.BOF)
    cmdFirst.Enabled = Not (rsClone.BOF)
    rsClone.MoveNext
    
    ' And then check whether we are on the last record.
    ' If so, we should disable the <NEXT> buttons.
    
    rsClone.MoveNext
    cmdNext.Enabled = Not (rsClone.EOF)
    cmdLAst.Enabled = Not (rsClone.EOF)
    rsClone.MovePrevious
    
    End If
    
    ' Finally, we close the cloned recordset
    rsClone.Close

End Sub

 

[Carl_R]

Mile-O-Phile

This also gets rid of those annoying 'end of recordset' messages.

When I grow up, I wanna be just like you

Thanks a heap.

 

[Len Boorman]

Just an alternative method.
I presume you are using VBA to open the edit form using DoCmd. Openform etc with the open mode as edit. Just leave off the open mode at the end of the DoCmd statement and then set the properties of the Edit Form. You will see that there are 4 properties that you can set
Allow Edits
Allow Deletions
Allow Additions
Data Entry

Just set the Allow Additions to No.

If you set the open mode within the Docmd.openform statement it will over ride the form properties and the Edit Mode also allows additions.

HTH

Len B

 

[Carl_R]

Ok, thanks Len.
'I'm buying the beer'

 

[Len Boorman]

Okay I will just pop round this lunchtime.

Len B