Limit rows in subform based on field in main form

[sv89]

Hi,

I have a frmOrders and sbfrmOrderDetails linked by OrderID. I want to limit the records on sbfrmOrderDetails based on a field TotUnits on the mainform. So, if the user enters 5 as the value in TotUnits, only 5 records are to be entered in the sbfrmOrderDetails.

This is the code I'm using:

Option Compare Database
Option Explicit
Dim var As Integer


Public Sub LimitRecords(var As Integer)
Dim conRecLimit As Integer
With Me.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.AllowAdditions = (.RecordCount < var)
End With

End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.[OrderLineNum] = Nz(DMax("OrderLineNum", "tblOrderDetails", "OrderID=" & Forms!frmOrders!OrderID.Value), 0) + 1
    var = Forms!frmOrders!TotUnits.Value
    LimitRecords (var)
End Sub

This works. However, if i scroll on to enter a new Order on frmOrders, it won't let me add details on sbfrmOrderDetails. I know I'm supposed to set AllowAdditions = True but where?

Would there be a better way of doing this?

 

[nathansav]

Instead of using the parent child linking on the main for to the sub form you could set it source SQL via VBA, and use select TOP 5,

something like

strSQL = "SELECT TOP " & Forms!frmOrders!TotUnits.Value & " FROM "

Havent tried it in practice, but you can then set the allow additions in the form or via code.

Let me know if it works

Cheers

N

 

[nathansav]

Hi

Sorry, got the wrong end of the stick there. I thought you wanted to show only X records retrieved.

 

[nathansav]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount >= intRecordLimitThen
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

In the subforms code module.

 

[sv89]

This works for the current record on the mainform. If the field on the mainform is equal to 2, it allows 3 entries in the subform and then no additions. However if I move to the next record on the mainform, I want the allowadditions on the subform to be true.

 

[nathansav]

Not sure i understand

You will need to set intRecordLimitThen to the value of the main forms control

 

[vbaInet]

Use this in the Current event of the Main Form:

Private Sub Form_Current()
    Dim rst As DAO.Recordset
    
    Set rst = Me.subformName.Form.RecordsetClone
    
    rst.MoveLast
    
    If rst.RecordCount = Me!TotUnits Then
        Me.subformName.Form.AllowAdditions = False
    Else
        Me.subformName.Form.AllowAdditions = True
    End If
End Sub

You will also need to do something similar in the After Delete Confirm event and After Insert event of the subform.