Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict records in subform (2000)

    Hi, is there a way of restricting the number of records in a sub form form, say to 4, so in essence you have a 1 to 4 relation?

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Restrict records in subform (2000)

    You can use the recordcount in the current event of the sub subform to set the allowadditions property to false when the recordcount >= 4, is that's what you're trying to do.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Restrict records in subform (2000)

    You could try a TOP 4 query, however I have not tried this.

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Thanks for your replies guys, Charlottes seems to be the route I'll try. I am having a spot of bother with the code however, my reference bible is at work, I am at home!

    Thanks Darren.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Restrict records in subform (2000)

    Yes, try Charlotte's solution. I misread your request, sorry.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Could anyone give me a hint with the code please as mentioned in Charlottes post?

    Thanks Darren.

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Restrict records in subform (2000)

    If interested see attached demo database that demonstrates one possible approach. Demo uses modified versions of Orders main form & Orders Subform from Northwind.mdb. The modified form allows you to enter no more than four items per order (this number can be changed). When max limit reached, the subform's AllowAdditions property is set to False, and warning label displayed on main form. A textbox on main form displays subform recordcount. Sample code:

    Public Sub GetSubformRecordCount()
    On Error GoTo Err_Handler

    Dim rst As DAO.Recordset
    Dim intCount As Integer
    Dim intMax As Integer
    Dim strMsg As String

    With Me
    Set rst = .Orders_Subform.Form.RecordsetClone

    ' Get recordcount:
    intCount = rst.RecordCount
    .SubformCount_txt = intCount

    ' Specify maximum subform records allowed:
    intMax = 4

    If intCount >= intMax Then
    .Orders_Subform.Form.AllowAdditions = False
    .WarningLabel_lbl.Visible = True
    Else
    .Orders_Subform.Form.AllowAdditions = True
    .WarningLabel_lbl.Visible = False
    End If

    End With

    Exit_Sub:
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "GetSubformRecordCount Error Message"
    Resume Exit_Sub
    End Select

    End Sub

    This sub is called by main form Current event, and by subform's Form_AfterInsert and Form_AfterDelConfirm events - see attached file. This is simple example, there may be other actions you need to take when max limit is reached, and I used a generic error handler. Note: in attached file, the tables are linked to default folder for Northwind in Access 2002 (C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb) - if your copy of Northwind is located elsewhere, use Linked Table Manager to reset links (I was too lazy to make up some dummy data). The db is A2K format.

    HTH
    Attached Files Attached Files

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Restrict records in subform (2000)

    Mark has given you the code you requested, but you should be aware that someone can still add records at the table level and then you end up with more than 4 records. Therefore you will want to make sure users cannot get at the database container window.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Thank you guys, that does the trick. Cheers for the extra tip Wendell.

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Cheers Mark, I have modified the code as needed to a point:

    Public Sub GetSubformRecordCount()
    On Error GoTo Err_Handler

    Dim rst As DAO.Recordset
    Dim intCount As Integer
    Dim intMax As Integer
    Dim strMsg As String

    With Me

    Set rst = .filter_Subform.Form.RecordsetClone

    ' Get recordcount:
    intCount = rst.RecordCount
    .SubformCount_txt = intCount

    ' Specify maximum subform records allowed:
    intMax = 4

    If intCount >= intMax Then
    .filter_Subform.Form.AllowAdditions = False
    .WarningLabel_lbl.Visible = True
    Else
    .filter_Subform.Form.AllowAdditions = True
    .WarningLabel_lbl.Visible = False
    End If

    End With

    Exit_Sub:
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "GetSubformRecordCount Error Message"
    Resume Exit_Sub
    End Select

    End Sub

    Problem is I am getting this error message: 'use-defined type not defined' with ref to the DAO.recordset datatype. sorry if this is basic stuff I should be able to remedy, I have tried online help and my reference books are at work!

    Thanks Darren.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Darren,

    Open any module and go to Tools / References.
    Scroll to Microsoft DAO 3.6 Object Library and check it.
    This should work.
    Francois

  12. #12
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict records in subform (2000)

    Sorry, I was a little hasty, in the VB Editor I have realised you select tools|references and select as necessary.

    Thanks Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •