Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Record count

    Hello!

    I cant effectively get a counter to work with my code. I'm using the following to scale my form based on the number of records the query returns. At the moment this will scale the form to include the header/footer and one record. I need to multiply the RHeight by the number of records then this will be fine.

    Code:
    Private Sub Form_Open(Cancel As Integer)
    Dim RHeight As Integer
    Dim intHeightHeader As Integer
    Dim intHeightFooter As Integer
     
     intHeightHeader = Forms!Needs_cost_Info_F.Section(acHeader).Height
     intHeightFooter = Forms!Needs_cost_Info_F.Section(acFooter).Height
     RHeight = Forms!Needs_cost_Info_F.Section(acDetail).Height
     
     intTotalFormHeight = intHeightHeader + intHeightFooter + RHeight
    With Me
    .InsideHeight = intTotalFormHeight
    End With
     
    End Sub
    Querys used:


    Needs_cost_Info_Q - This returns the records for the form.
    Needs_cost_Info_Counter - this is just a counter It displays a single number.



    I've tried with no success using DAO record count and ADODB.recordset. Im sure using these is pretty simple with some understanding but I cant get it to work at all. (I'm referencing ADODB.recordset on the DB I believe)

    another option that could work is referencing a query that I have that simply counts the results from the initial query. But I don't know how to reference this or... If I could somehow have this hidden on the form without being part of the record source for the detail section of the form.

    any feedback and ideas appreciated. Thanks

    Andy.,

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The form open event is not going to have the RecordCount for the Form's Recordset.

    Try getting the count from the Load or the Current event.

    something like ...
    Dim rs as dao.recordset
    set rs = me.recordsetclone
    rs.movelast
    msgbox rs.recordcount

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Brilliant! Thanks for that.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    When I try to use the value from rs in my code I get wrong data type. How would I convert this number to an integer?

    Code:
    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Dim RsNumber As Integer
    Dim RHeight As Integer
    Dim intHeightHeader As Integer
    Dim intHeightFooter As Integer
     
     Set rs = Me.RecordsetClone
     
     rs.MoveLast
     RsNumber = rs
    error occurs on the last line

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Your recordcount will likely fit into an Integer. However, I believe the measurement you are trying to adjust are measured in units of twips. I can't remember if you need a decimal or not. However, twips can be large numbers. So, I would not expect them to fit into an Integer. If there are not any decimals involved, you should be able to use a Long Integer.
    Dim MyNumber as Long

    Otherwise, a Double (to deal with fractions) may be large enough to manage the number.


    Code:
    Dim lngRsNumber As Long
    
    ...
    lngRsNumber = rs.recordcount

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    thanks again!

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You bet .....

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

Similar Threads

  1. Updating Multiple Record Form Based on Record Count
    By William McKinley in forum Forms
    Replies: 2
    Last Post: 12-31-2014, 12:45 PM
  2. Specific Record Count
    By FormerJarHead in forum Queries
    Replies: 3
    Last Post: 08-23-2013, 11:02 AM
  3. Need help correcting record count
    By Fivehole91 in forum Access
    Replies: 2
    Last Post: 05-15-2012, 05:15 PM
  4. Record Count
    By jgelpi16 in forum Queries
    Replies: 4
    Last Post: 12-02-2010, 09:22 AM
  5. Form Record Count
    By Texaine in forum Forms
    Replies: 0
    Last Post: 10-19-2006, 09:07 AM

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