Data Saving

The issue is based on this code below:

----- start of function code -----
Public Function LimitRecords( _
                    frm As Access.Form, _
                    Optional RecLimit As Integer = 1)

    ' Limit the number of records in the form passed as
    ' to no more than the number specified by .

    With frm.RecordsetClone
        If .RecordCount <> 0 Then .MoveLast
        frm.AllowAdditions = (.RecordCount < RecLimit)
    End With

End Function
'----- end of function code -----


=LimitRecords([Form], 1)


Because of using this code to limit the number of lines to be used in the sub form, though everything works fine but after saving data it does not refresh in readiness for the new data entry until I close the form and reopen again that is when it work again.
I have tried to use this code:
Me.Requery
In the sub form, well it has worked but it save directly immediately once the last control is done leaving the parent form data un saved. It does not give chance for corrections

Temporally I have now resorted to us the VBA code below:
Private Sub Refresh_Click()
DoCmd.Save
DoCmd.Close
End Sub
This now works Okay, but I have to keep on reopening until all transactions are finished

(2) Is there way to refer to a control from a subform to the other subform like the we do it when referring to the parent form:

Me.TTDate = Me.Parent!TTDate
 
Any help on this will be high appreciated.

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
This is the full code and guide how to implement it:

Public Sub SetFormAllowAdditions( _
    ByVal frm As Form, _
    Optional ByVal RecordCountMax As Long = 1)
  
' Limit count of records in (sub)form to that of RecordCountMax.
' 2016-10-26, Cactus Data ApS, CPH
'
' Call in (sub)form:
'
'   Private Sub LimitRecords()
'       Const RecordsMax As Long = 5
'       Call SetFormAllowAdditions(Me.Form, RecordsMax)
'   End Sub
'
'   Private Sub Form_AfterDelConfirm(Status As Integer)
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_AfterInsert()
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_Current()
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_Open(Cancel As Integer)
'       Call LimitRecords
'   End Sub
'
' If the record count of a subform is to be limited, also
' the parent form must be adjusted:
'
'   Private Sub Form_Current()
'       Call SetFormAllowAdditions(Me.Form)
'   End Sub
'

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

End Sub

Open in new window

The function could be kept in a code module separate from the form(s).

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
<<(2) Is there way to refer to a control from a subform to the other subform like the we do it when referring to the parent form:>>

Me.Parent.OtherChild.Form.txtTextboxName

Open in new window


(Where OtherChild is the name of the 'sibling' subform control, as seen in the parent form's design)
mbizupCommented:
Chris,

You had two people working with you on this question.  I believe Gustav’s post addressed the bulk of your question; mine addressed your second concern.

Please use the Request Attention button to reopen the question, and accept/award points to the first post.

Thanks -
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
I'm sorry I cannot see the re open button but all the same what worked perfectly well for me is as follows:

Replace re query with three staged code:

Private Sub Refresh_Click()
DoCmd.Save
DoCmd.Close
DoCmd.Open Form
End Sub
 
The above code worked very with the code below:

Public Function LimitRecords( _
                    frm As Access.Form, _
                    Optional RecLimit As Integer = 1)

    ' Limit the number of records in the form passed as
    ' to no more than the number specified by .

    With frm.RecordsetClone
        If .RecordCount <> 0 Then .MoveLast
        frm.AllowAdditions = (.RecordCount < RecLimit)
    End With

End Function

Once see the re open button I will do as per request many thanks to all.
Regards

Chris
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.