Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    How to control Number of Entries

    HI Guys,



    I am working on a simple Billing Database. I have tblBill & tblBillDetails (Child Table). I am using frmBill & frmBillDetail(Sub form) for user to input data. frmBillDetail is showed in datasheet view.

    I want to control that each entry of tblBill, can have maximum of 10 records for tblBillDetails. How to achieve the same. Please help.

    Thanks and Regards
    Deepak Gupta

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put this code in the parent form , to test the records in the subform and lock it if > 9

    Code:
    Private Sub Form_AfterUpdate()
    CheckSubForm
    End Sub
    
    
    sub CheckSubForm()
    subFrm.Locked = subFrm.Form.Recordset.RecordCount > 9
    end sub


    it would also need to test in the subform event AFTERUPDATE as users enter data.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I tried Ranman's code and it worked provided you closed and reopened the form

    Here's an alternative that worked for me without needing to close the form

    Use this code in the SUBFORM current event

    Code:
    Private Sub Form_Current()
        If Me.Dirty Then Me.Dirty = False
        Me.AllowAdditions = DCount("*", Me.RecordSource) < 10
    End Sub
    No code is needed in the main form

    This will lock the form after the tenth record is added
    Deleting a record will unlock it again ... loop ad nauseam
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear RanMan256,

    I have tried the code but unable to get the desired result. I am a total novice and VBA still tried various combinations with my limited knowledge and were not able to get the desired results.

    I have changed the code to following
    Private Sub Form_AfterUpdate()
    CheckSubForm
    End Sub


    sub CheckSubForm()
    frmSaleDetailSubformNew.Locked = frmSaleDetailSubformNew.Form.Recordset.RecordCount > 9
    end sub(frmSaleDetailSubformNew is the name of the subform)

    I have added the above code to both main form and subform.
    Now when ever I try to add new record in subform it is giving me error 424 Stating Object Required.

    Please help.

    Thanks and Regards
    Deepak Gupta

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Did you try my alternative version?
    The code I posted is all that you need
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    yes, just tried it right now.

    Now unable to make any entries in the subform. Unable to under stand where i made a mistake.

    Thanks and Regards
    Deepak Gupta

  8. #8
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear ridders52,

    The problem with your code is that the code is calculating total number of records in the table. In place of locking the entry of records for the particular bill number in parent form, it is locking the entry for the complete table. The maximum number of entries restricted to 9 is acting for the whole table in place of particular bill number.

    Looking forward to your help.
    Thanks and Regards
    Deepak Gupta

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah yes - just re-read your first post
    Try this

    Code:
    Private Sub Form_Current()   
    If Me.Dirty Then Me.Dirty = False
        Me.AllowAdditions = DCount("*", "tblBillDetails","BillID = '" & Me.BillID & "'") < 10
    End Sub
    The part in RED need to be altered for your subform
    It assumes a text datatype primary key field BillID and a form field BillID

    If it's a number PK field, use

    Code:
    Private Sub Form_Current()    
    If Me.Dirty Then Me.Dirty = False
        Me.AllowAdditions = DCount("*", "tblBillDetails","BillID = " & Me.BillID) < 10
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear RanMan256,

    Further trying around with your code, I removed the code checksubform from child form, now when i try to update the child form it gives me an error statng:
    Sub Or Function not defined. Where as the function is defined in parent form.

    Please help. Thanks and regards
    Deepak Gupta

  11. #11
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Ridders52,

    tried this

    Private Sub Form_Current()
    If Me.Dirty Then Me.Dirty = False
    Me.AllowAdditions = DCount("*", "tblSaleDetail", "SaleID_FK = '" & Me.SaleID_FK & "'") < 3
    End Sub

    AND this also

    Private Sub Form_Current()
    If Me.Dirty Then Me.Dirty = False
    Me.AllowAdditions = DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_FK) < 3
    End Sub


    Nothing worked. Records just keep getting added. SaleID is the PK in main table and SaleID_FK is the foreign in in the child table (number type).

    Looking forward to your help. Also really thankfull for all your help.

    Thanks and Regards
    Deepak Gupta

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. What is the datatype of the SaleID field?
    2. Did you use the code in the SUBFORM with no other similar code in the MAIN FORM?
    3. Why use <3 when you said the limit was 10?
    Last edited by isladogs; 01-13-2018 at 12:19 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Deepak,

    I suggest you post a copy of the database (zip format) with enough info to show the issue.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954

    Limit Number of Records example

    Attached is an example that should be similar to your situation.

    Form & subform based on tblSale & tblSaleDetails with SaleID as PK & FK respectively
    The table relationship is an OUTER join so extra records can be added up to the record limit

    The code limiting the records is in the Form_current event of the subform

    Code:
    Private Sub Form_Current()
    
        If Me.Dirty Then Me.Dirty = False
        Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) < Nz(DLookup("MaxRecords", "tblRecordLimit"), 0)
    End Sub
    You can set the record limit on the main form - currently this is 5.
    This value is stored in a separate table tblRecordLimit
    Alternatively just set your own fixed value (e.g. 10), remove the table & form controls and alter the above code to:
    Code:
    Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) < 10
    Hope this helps

    NOTE: Do NOT allow end users access to the tables otherwise they will still be able to add additional records
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi ridders52,

    Please find attached copy of the project. I have uploaded a fresh copy for your reference, I have not tried to add your code to restrict the data entry.

    As for your example, which you have uploaded, I tried it, I am unable to add any records in tblSaleDetails form.

    Looking forward to your help.
    Thanks and Regards
    Deepak GuptaBillingU - Control Number of Records.zip

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-05-2016, 01:56 PM
  2. limit number of hours entries per day
    By shod90 in forum Forms
    Replies: 5
    Last Post: 03-24-2016, 10:54 AM
  3. Generate Fixed Number of Entries
    By dustonabt12 in forum Reports
    Replies: 6
    Last Post: 07-10-2013, 01:55 PM
  4. Replies: 1
    Last Post: 02-26-2013, 11:20 AM
  5. Form with an unkonwn number of entries?
    By bakkouz in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 12:56 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