Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unhappy Unanswered: finding number records in recordset

    I am trying to find number of records in recordset by usiing the following code:
    Dim db As Database
    Dim rs As DAO.Recordset
    'Set db = OpenDatabase("Job_Flow.mdb")
    Dim coun As Integer
    coun = 0
    'On Error GoTo errormark
    Set rs = CurrentDb.OpenRecordset("select * from Job_Flow where date_req=' " & txt_dor & "' and type_proj='" & cbo_req & "')
    rs.movelast
    counta=rs.recordcount
    but i dont now wht is the error and im unable to get number of records in recordset.I am using Access2002

    I also found that the movenext, moveprevious is also not working.

    Can anybody help me out....

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Dim rs As DAO.Recordset
    Dim coun As Integer
    coun = 0
    'On Error GoTo errormark
    Set rs = CurrentDb.OpenRecordset("select * from Job_Flow where date_req=' " & txt_dor & "' and type_proj='" & cbo_req & "')
    rs.movelast
    coun=rs.recordcount

    Also if date_req is a date field you will need to change the query to
    Set rs = CurrentDb.OpenRecordset("select * from Job_Flow where date_req=#" & txt_dor & "# and type_proj='" & cbo_req & "')

    Greetz

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    date_req=' " & txt_dor & "' seems to be wrong

    if text:
    date_req='" & txt_dor & "'

    if date:
    date_req=#" & txt_dor & "#

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2004
    Location
    U.S.
    Posts
    26
    dude you are using "rs.recordcount" this returns a Long value so you need to cast or change the "coun" datatype to Long.

    You have :: coun = rs.recordcount 'cant convert a long to an integer

    Casting is like this:: coun = cint(rs.recordcount)

    -----This is error checking--------------
    did you try to check the error is is throwing??? do this by:
    On Error Goto errormark
    Your code

    :Continue
    Exit Sub

    :errormark
    msgbox ("Error number is: " & err.number & " Error Description is: " err.description.")
    Resume Continue

  5. #5
    Join Date
    Jan 2004
    Location
    U.S.
    Posts
    26
    izyrider - I dont think you need the '#' in the string. I could be wrong, but i know you need to cast or change datatypes for sure.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's not just count that is failing
    next/prev also fail...
    ...so the recordset is empty.

    just after the Set rs = ...... line, insert:
    if rs.bof and rs.eof then msgbox "my recordset is empty!"

    ...and see what happens.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Jay,

    As long as the value in the Long variable is "small enough" it is automaticaly converted to an integer. Small enough mening between -32,768 and 32,767
    Try this simple bit of code, it runs just fine
    Dim x As Long
    Dim y As Integer
    x = 30000
    y = x
    MsgBox y

    While below will crash, with an overflow error
    Dim x As Long
    Dim y As Integer
    x = 33000
    y = x
    MsgBox y

    Greetz

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by jayblaze2
    izyrider - I dont think you need the '#' in the string. I could be wrong, but i know you need to cast or change datatypes for sure.
    That would depend on the type of the fields,
    For texts you need ' in the string
    For dates you need #
    for number you need nothing.

    Greetz

    P.S. I am assuming you have the DAO reference "referenced" ?

  9. #9
    Join Date
    Jan 2004
    Location
    U.S.
    Posts
    26
    Your right about the '#' for dates. I found somthing wrong with the string ::
    ("select * from Job_Flow where date_req=' " & txt_dor & "' and type_proj='" & cbo_req & "')

    At the end of the statement you dont close the string correctly.

    your end is :: & " ' )

    should be :: & " ' " )

  10. #10
    Join Date
    Jan 2004
    Location
    U.S.
    Posts
    26
    also i would use ADO instead of DAO. just my preference suppost to be better. and like namliam said, "do you have DAO or ADO referenced?"

  11. #11
    Join Date
    Feb 2005
    Posts
    4
    Thanks to every body.....for responding........

Posting Permissions

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