Results 1 to 15 of 15
  1. #1
    Join Date
    May 2019
    Posts
    65

    Limiting Records per page on a form and using a filter.

    I am using a filter with one continuous form. The number of records displayed in the detail section forces a second scroll bar which is not user friendly. I'm trying to display either a maximum number of records or a maximum height of the form. I've tried Me.Maxrecords = 10 or Me.MaxRecords = NumberRecords (where variable is 10) and get "You enter an expression that has an invalid reference to the property MaxRecords." Run Time error 2455.



    Any thoughts are appreciated.

    Jeff

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's a property I hadn't heard of. I tested briefly with that syntax and the syntax recommended by MS, both threw the error. Since you're filtering anyway I might set the record source instead of a filter and use the TOP predicate.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Use TOP 10 in the form's recordsource (query).

    EDIT: Sorry Paul, didn't meant to jump in...
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No worries Vlad, we were posting at the same time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by pbaldy View Post
    No worries Vlad, we were posting at the same time.
    Thanks for your replies, however I have spent a lot of time developing the filter. I did read a little about Top 10, but have to look into it further.
    When I say filter, it is really a button with a subroutine that filters out the records. I was trying to put the me.Maxrecords = 10 in that code.

    Jeff

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You haven't shown the code, but it's likely it could easily be tweaked appropriately.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by pbaldy View Post
    You haven't shown the code, but it's likely it could easily be tweaked appropriately.

    Here is the code.
    Private Sub cmdFilter_Click()
    'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
    we remove the trailing " AND " at the end.
    ' 2. The date range works like this: _
    Both dates = only dates between (both inclusive. _
    Start date only = all dates from this one onwards; _
    End date only = all dates up to (and including this one).
    Dim strWhere As String 'The criteria string.
    Dim lngLen As Long 'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
    Dim NumberRecords As Long
    NumberRecords = 10
    '************************************************* **********************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '************************************************* **********************

    'Use Like to find anywhere in the field.

    If Not IsNull(Me.varCLFullName) Then
    strWhere = strWhere & "([CLFullName] Like ""*" & Me.varCLFullName & "*"") AND "
    End If

    If Not IsNull(Me.varCLPhoneNumber) Then
    strWhere = strWhere & "([CLPhoneNumber] Like ""*" & Me.varCLPhoneNumber & "*"") AND "
    End If


    If Not IsNull(Me.varCLShelterName) Then
    strWhere = strWhere & "([CLShelter] Like ""*" & Me.varCLShelterName & "*"") AND "
    End If

    If Not IsNull(Me.varCLFollowupContact) Then
    strWhere = strWhere & "([CLFollow-upContact] Like ""*" & Me.varCLFollowupContact & "*"") AND "
    End If

    If Not IsNull(Me.varCLFacilityOwnerName) Then
    strWhere = strWhere & "([CLFacilityOwnerName] Like ""*" & Me.varCLFacilityOwnerName & "*"") AND "
    End If


    If Not IsNull(Me.varCLFollowupStartDate) Then
    strWhere = strWhere & "([CLFollow-upDate] >= " & Format(Me.varCLFollowupStartDate, conJetDate) & ") AND "
    End If


    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.varCLFollowupEndDate) Then 'Less than the next day.
    strWhere = strWhere & "([CLFollow-upDate] < " & Format(Me.varCLFollowupEndDate + 1, conJetDate) & ") AND "
    End If
    If Me.varCLCallStatus = "Open" Then
    strWhere = strWhere & "[CLCallStatus] = 'Open' AND """
    ElseIf Me.varCLCallStatus = "Closed" Then

    strWhere = strWhere & "[CLCallStatus] = 'Closed' AND """
    ElseIf Me.varCLCallStatus = "All" Then
    strWhere = strWhere & "[CLCallStatus] = 'Open' OR [CLCallStatus] = 'Closed' AND """

    End If

    '************************************************* **********************
    ' THESE ARE OTHER SEARCH EXAMPLES
    '************************************************* **********************
    ' 'Number field example. Do not add the extra quotes.
    ' If Not IsNull(Me.cboFilterLevel) Then
    ' strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
    ' End If
    '
    ' 'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    ' If Me.cboFilterIsCorporate = -1 Then
    ' strWhere = strWhere & "([IsCorporate] = True) AND "
    ' ElseIf Me.cboFilterIsCorporate = 0 Then
    ' strWhere = strWhere & "([IsCorporate] = False) AND "
    ' End If
    '
    'Date field example. Use the format string to add the # delimiters and get the right international format.



    '************************************************* **********************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '************************************************* **********************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then 'Nah: there was nothing in the string.
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else 'Yep: there is something there, so remove the " AND " at the end.
    strWhere = Left$(strWhere, lngLen)
    'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
    'Debug.Print strWhere


    'Finally, apply the string as the form's Filter.
    Me.Filter = strWhere
    Me.FilterOn = True
    'Me.MaxRecords = NumberRecords THIS IS WHERE I GET THE ERROR
    DoCmd.SetOrderBy "[CLFollow-UpDate] DESC"

    End If
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So instead of

    Me.Filter = strWhere
    Me.FilterOn = True

    you could have

    Me.RecordSource = "SELECT TOP 10 * FROM TableName WHERE " & strWhere

    you could add the sort to that as well.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Instead of setting a filter, you could restrict the form's recordsource query:

    Code:
    Me.Recordsource = "Select TOP 10 * FROM tblExample WHERE " & strWhere
    EDIT: See post above. 2 votes for this method.

  10. #10
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by davegri View Post
    Instead of setting a filter, you could restrict the form's recordsource query:

    Code:
    Me.Recordsource = "Select TOP 10 * FROM tblExample WHERE " & strWhere
    EDIT: See post above. 2 votes for this method.
    Again, thanks for the replies. That works for the top 10, 20, etc. However, I just want to display 15 records, but be able to scroll to the rest of the records. I want to change the height of the display. Any thoughts there?

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The number of records displayed in the detail section forces a second scroll bar which is not user friendly.
    You might find a split form better suited for the users.

  12. #12
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by davegri View Post
    You might find a split form better suited for the users.
    Thank you for your suggestion but I started with a split form. I couldn't figure out how to "filter" the records when a table field, which I was part of the search criteria, had a null value. It just wouldn't bring the record up. When someone told me about the filter method recommended by "Browne?", that worked great except for trying to limit the number of records displayed at once.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't understand what's different about limiting to 10 via MaxRecords or TOP? Neither would allow you to view other records, but either could be reset to view other records. I don't understand what you mean in post 10 by displaying 15 records but be able to scroll the rest.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Join Date
    May 2019
    Posts
    65
    I figured it out and it was simpler than I thought. This form is in navigation form. I realized that what I was trying to do was just change the size of the form. I realized that I just had to click on the form and grab the corners and change the height. Sorry for getting everyone involved. It was great to know the issue of Max 10. So again, it wasn't an issue of records displayed it was simply form height.

    Jeff

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2016, 10:57 AM
  2. Replies: 6
    Last Post: 05-04-2016, 05:30 PM
  3. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  4. Form limiting records
    By yaro.kobzar in forum Programming
    Replies: 1
    Last Post: 05-21-2011, 06:50 AM
  5. limiting amount of records
    By yaro.kobzar in forum Programming
    Replies: 1
    Last Post: 05-17-2011, 07:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums