Hi, Karen.

I have a subform and the max records to be entered is two. Is there a way
to cycle for more than one and less than All.

Not with the form's Properties settings. The only options for cycling the
records are "All Records," "Current Record," and "Current Page." However,
one may do this with code in the subform's OnCurrent( ) event, as long as the
subform's Cycle Property is set to "All Records."

To prevent more than two records from being added to the subform use the
following code in the subform's OnCurrent( ) event:

' * * * * Start Code * * * *

Private Sub Form_Current()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim fOpenedRecSet As Boolean

Set recSet = Me.RecordsetClone
fOpenedRecSet = True

If (Me.NewRecord And (recSet.RecordCount 1)) Then
recSet.MoveLast
Me.Bookmark = recSet.Bookmark
Else
Me!txtStuff.SetFocus ' Set focus to first editable control.
End If

CleanUp:

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in Form_Current( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

' * * * * End Code * * * *

.... where txtStuff is a text box that may be edited by the user.

A new record will not be created, so if the primary key is an AutoNumber, it
won't increment when a new record is attempted and aborted. Therefore, it
has the effect of cycling up to two records in that tabbing after the last
control will return focus to the first control on that second record (which
is then moved to focus on the first editable control in the example above).

Unlike Gina's suggestion, it doesn't warn the user that a maximum of two
records may be entered into the subform. But when the Cycle Property is set
to "Current Record," the user is never warned that only one record may be
entered, either.

This same technique may be used in a demo application to limit the number of
records that the user may enter into the application. The user gets to see
the application in action, but doesn't get to use the full capabilities for
free.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are and

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


"Karen" wrote:

Hi,

I have a subform and the max records to be entered is two. Is there a way
to cycle for more than one and less than All.

Thanks,
Karen