?
Solved

Ms Access Need Help Altering and Combining Code For Subform Limit Record Count and Allow Additions True or False

Posted on 2016-10-26
11
Medium Priority
?
35 Views
Last Modified: 2016-10-26
These are the two codes I have:

Limit Record Count in Contentious Subform:
Private Sub Form_Current()

Dim intMaxNumRecs As Integer
 
    intMaxNumRecs = 1 'Max Number of Records to Allow

    If Me.NewRecord Then
        With Me.RecordsetClone
            If .RecordCount > 0 Then
                .MoveLast:  .MoveFirst
                If .RecordCount >= intMaxNumRecs Then
                    MsgBox "You can't add more than " & intMaxNumRecs & " record of Quantity for each product condition!", vbInformation, "Warning"
                    .MoveLast
                    Me.Bookmark = .Bookmark
                End If
            End If
        End With
    End If
End Sub

Open in new window



And For AllowAdditions:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 1 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

Open in new window



I would like for the code to remove the New record line after the Max Number of Records to Allow. I have tried a few different ways but not working for me. Thanks!
0
Comment
Question by:Dustin Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41860248
Though only one function is needed, It takes a little more, as you have to call it from several events of the subform - see in-line comments for the usage.
Adjust lngRecordsMax to fit your need:
Public Sub SetFormAllowAdditions( _
  ByVal frm As Form, _
  ByVal lngRecordCountMax As Long)
  
' Limit count of records in (sub)form to that of lngRecordCountMax.
' 2004-10-06, Cactus Data ApS, CPH
'
' Call in (sub)form:
'
'   Private Sub LimitRecords()
'     Const lngRecordsMax As Long = 5
'     Call SetFormAllowAdditions(Me.Form, lngRecordsMax)
'   End Sub
'
'   Private Sub Form_AfterDelConfirm(Status As Integer)
'     Call LimitRecords
'   End Sub
'
'   Private Sub Form_AfterInsert()
'     Call LimitRecords
'   End Sub
'
'   Private Sub Form_Open(Cancel As Integer)
'     Call LimitRecords
'   End Sub

  Dim booAllowAdditions As Boolean
    
  With frm
    booAllowAdditions = (.RecordsetClone.RecordCount < lngRecordCountMax)
    If booAllowAdditions <> .AllowAdditions Then
      .AllowAdditions = booAllowAdditions
    End If
  End With

End Sub

Open in new window

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41860275
so would I just drop this into the subform VBA and all the events will take place as needed?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41860314
No, you will save it in a code module and refer to it from your subform as explained in the in-line comments.

/gustav
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Dustin Stanley
ID: 41860342
Ok I see. Sorry I was on my mobile at the time and couldn't visualize it until I made the module on my PC. But there is an issue and it is the same for this code as it was doing earlier with my code. If I click the record selectors on the main form the next record subform (If New Record) is blank and I can't add any records to the subform at all not even up to the lngRecordsMax.
0
 

Author Comment

by:Dustin Stanley
ID: 41860367
subform (If New Record)

I take that back. All the records before and after have the New Record Line Gone whether it is new or only has 2 records and the lngRecordsMax is set at 5
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 total points
ID: 41860410
You need these two additions:

1. In the parent form:
Private Sub Form_Current()
  
    Call SetFormAllowAdditions(Me!NameOfYourSubformControl.Form, 5)
  
End Sub

Open in new window

2. In the subform:
Private Sub Form_Current()

    Call LimitRecords

End Sub

Open in new window

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41860425
Call SetFormAllowAdditions(Me!SkuID.Form, 5)

Open in new window


Returns access object doesn't support this property or method
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41860428
I doubt your subform control is labelled SKuID. Sounds like a field.

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41860437
I doubt your subform control is labelled SKuID. Sounds like a field.

Well what do you know it works perfect.
Call SetFormAllowAdditions(Me!sbfrmProducts.Form, 4)

Open in new window

0
 

Author Closing Comment

by:Dustin Stanley
ID: 41860438
Thanks!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41860440
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question