Results 1 to 5 of 5
  1. #1
    PJT is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2014
    Posts
    3

    limit number of records in a subform

    I am relatively new to Access, and my search fu is weak, so any help is appreciated.

    I would like to limit the number of records in a subform to 15 records per main form record. I found this particular solution:



    Private Sub Form_Current()
    ' if 15 is the max records to enter
    Me.AllowAdditions = Me.RecordsetClone.RecordCount < 15
    End Sub

    However, it limits the number of records in the subform to 15 total, not 15 per main form. When I go to a new record on the main form I cannot enter anything into the subform. Other ideas?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The code seems to work for me. Each record in the main form can have 15 records in the sub form. Where are you using the code.
    When I go to a new record on the main form I cannot enter anything into the subform.
    To fix that, try following code the main forms After Update event.
    Code:
    Me.YourSubFormCtrlName.Form.AllowAdditions = Me.YourSubFormCtrlName.Form.RecordsetClone.RecordCount < 15
    Replace YourSubFormCtrlName with the name of your sub form control. This may or may not be the same as the name of the form being shown as a sub form.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    PJT is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2014
    Posts
    3
    hmm.. not working. still only letting me enter 15 subform records on the first main form record, and then none on subsequent main form records.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you see Bob's post? Did you adjust your code accordingly?
    Please post the modified code. Did you get an error message?
    Please show us some sample data for
    a) mainform, and
    b) subform.

  5. #5
    PJT is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2014
    Posts
    3
    ok i think i've got it (for now).

    On the main form I did this: It resets the ability to add to the subform.

    Private Sub mysubformname_Enter()
    Me.mysubformname.Form.AllowAdditions = True
    End Sub

    and I kept the original code associated with the subform

    Private Sub Form_Current()
    ' if 15 is the max records to enter
    Me.AllowAdditions = Me.RecordsetClone.RecordCount < 15
    End Sub

    (edit) Strange that it works fine when the subform is in datasheet view, but not in continuous form view.
    Last edited by PJT; 04-13-2014 at 01:33 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-10-2012, 10:25 PM
  2. Limit number of records in report
    By aksnell in forum Reports
    Replies: 3
    Last Post: 12-05-2011, 02:31 PM
  3. Replies: 3
    Last Post: 12-01-2011, 06:51 AM
  4. Replies: 11
    Last Post: 10-20-2011, 08:41 AM
  5. Limit number of records in report by group
    By Dr Ennui in forum Reports
    Replies: 0
    Last Post: 06-22-2010, 12:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums