Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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

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
Dustin Stanley
Asked:
Dustin Stanley
  • 6
  • 5
2 Solutions
 
Gustav BrockCIOCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
so would I just drop this into the subform VBA and all the events will take place as needed?
0
 
Gustav BrockCIOCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Call SetFormAllowAdditions(Me!SkuID.Form, 5)

Open in new window


Returns access object doesn't support this property or method
0
 
Gustav BrockCIOCommented:
I doubt your subform control is labelled SKuID. Sounds like a field.

/gustav
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now