Results 1 to 5 of 5
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Limit amount of records on sub form

    On the Receipts form in the app, the intent is to limit the amount of records on the sub form to 1. The VBA in red works well. It is placed on the Sub form.


    The question here is; I only want this restriction if field txtTratyp_IDd <> 1510.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 1 Then
    MsgBox "Only one record allowed. Select Batch Receipt if multiple deposits are made.", vbExclamation + vbOKOnly, "Perfac"
    DoCmd.GoToRecord , , acPrevious
    End If
    End Sub

    The need is to add an extra criteria or condition. But then the code does not work anymore, if this line replaces the second line in red above.
    If Me.NewRecord = True And Me.RecordsetClone.RecordCount =1 And Me.txtTratyp_IDd <> 1510 Then

    I found on a forum the advice below, maybe I do not apply it exactly correct.
    Private Sub Form_Current()
    If Me.txtTratyp_IDd <> 1510 Then
    allowadditions = dcount("*"",recordsource)=1
    End If



  2. #2
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    No solution yet.
    1. I set up code that successfully limit the first captured record to allow one record in the sub form.
    2. When I want to capture the next record, the problem is the sub form does not allow additions.
    3. I tried AllowAdditions = True, on various events, not getting it right yet.
    4. I truly would like to get the right way of the code in purple above.

    Refer to discussion.
    https://www.access-programmers.co.uk...ubform.130119/
    Last edited by Perfac; 05-09-2023 at 11:32 PM.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Challenge Solved.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by Perfac View Post
    Challenge Solved.
    Well it would be polite to share your method.?

    You never know, it could help someone else in the future?
    After all that is what these forums are all about.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you Mr. Gasman. Here it goes.

    Back ground.
    In my application; on the "Receipt form" and "Payment form" the user must be forced to use only one record on the sub form for all other Receipts or Payments than Batch Payments or Batch Receipts. When a business has many cheques or other forms of payment for the day from debtors to deposit and capture one deposit it is a Batch Receipt. Or multiple creditors can be paid electronically while only one payment shows on the bank statement, that is a Batch Payment. Salaries or Wages is generally paid in a batch.

    In the sub query q02ReceiptSub that is the record source for the sub form f02ReceiptSub I added the expression in blue. 1510 is the ID for Batch receipt then zero, otherwise the expression counts the records for the transaction I am busy with. If one or more records are counted, the VBA in red will stop adding any records to the sub form.
    RecordCountA: IIf([Tratyp_IDd]=1510,0,DCount("RecsubID","t02ReceiptsSub","Receip _IDa =" & [ReceipID]))

    The limits of my experience saw me having lots of issues with "AllowAdditions = True" so I stayed away from that.
    The VBA in red I added as an Update event (Sub Form) on the "Amount Paid" which is named CreditC in the sub query.
    Private Sub cboLedacc_IDu_BeforeUpdate(Cancel As Integer)
    If Me.txtRecordCountA > 0 Then The expression above
    MsgBox "Only one record allowed. Select Batch Receipt if multiple deposits are made.", vbExclamation + vbOKOnly, "Perfac"
    Me.Undo
    End If
    End Sub

    Eureka! Simple. The final result is the two forms limit the sub form to one record for every transaction, except if the user selects Batch Payment or Batch Receipt under Transaction Type, then the sub form allows the user to capture as many records the user would like.
    Click image for larger version. 

Name:	230510a.png 
Views:	5 
Size:	27.5 KB 
ID:	50222
    If anybody has a question, ask me. I will try.
    Attached Thumbnails Attached Thumbnails 230510a.png  

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

Similar Threads

  1. Ho can I limit the amount of records in a table to one
    By Freddie81 in forum Programming
    Replies: 9
    Last Post: 05-13-2018, 08:02 AM
  2. Replies: 9
    Last Post: 08-23-2016, 03:44 PM
  3. Replies: 6
    Last Post: 11-18-2013, 04:50 PM
  4. Replies: 3
    Last Post: 12-01-2011, 06:51 AM
  5. Replies: 5
    Last Post: 05-14-2011, 04:31 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