Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Control Subform from Main form

Status
Not open for further replies.

puforee

Technical User
Oct 6, 2006
741
US
I have a travel DB. On the main form all the job information is entered including a field called "Number of people traveling" (not the real name). I have a subform that contains each travelers information.

Can I control the number of records available on the subform based on the number of travelers on the main form.

So...if the main form indicates 4 travelers.

The sub form should only allow 4 entries.

Thanks,
 
You can turn off the AllowAdditions property when the RecordCount of the SubForm reaches the value you are looking for.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit
 
RuralGuy,

Sounds like something I could use. Can you show me the code or explain doing this? What subform event would I use? And, how do I get the record count?

Thanks,

 
Something like-

if recordcount(subform) > 4 then mainform.allowadditions = false

This will make the last line in the datasheet (the one with the *) go away.
 
Great. But I will be doing the oppsite.

From within the Subform I will:\

if recordcount(subform)> mainform field x then subform.allowadditions = false

Mainform field x is contains the count of 4 or whatever.

You see, I want the subform to be limited to the number of records from the mainform field x.

What subform event do I attach this code to?

Thanks again JuniorData....we are very close here.
 
Main form:
to ensure you reopen the subform for additions

Private Sub Form_Current()
Const maxRecords = 5
If Me.subFrmCntrl.Form.Recordset.RecordCount <= maxRecords Then
Me.subFrmCntrl.Form.AllowAdditions = True
End If
End Sub

Sub Form:

Private Sub Form_Current()
Const maxCnt = 5
If Me.Recordset.RecordCount = maxCnt And Me.NewRecord Then
MsgBox "No More Allowed"
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub
 
sorry I missed that the max count is variable based on a field

child form
Private Sub Form_Current()
Dim maxCnt As Integer
maxCnt = Me.Parent.EmployeeID
If Me.Recordset.RecordCount = maxCnt And Me.NewRecord Then
MsgBox "No More Allowed"
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub

Main form
Private Sub Form_Current()
Dim maxRecords As Integer
maxRecords = Me.EmployeeID
If Me.subFrmCntrl.Form.Recordset.RecordCount <= maxRecords Then
Me.subFrmCntrl.Form.AllowAdditions = True
Else
Me.subFrmCntrl.Form.AllowAdditions = False
End If
End Sub
 
MajP

I fullly understand the subform code.

What is the Main form code doing for me? It seems like the sub form code would reset everytime is select a new record on the Main form. It then would read the mainform field value and set the number of subform records?

I know you did the main form code for a reason....can you explain? I am a bit of a novice.

Thanks for the great help.
 
Actually I think you can get rid of the main form code. I recycled the code from another form that had some more constraints. Try it without, should run.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top