Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Using Access-query as source for a recordset

    When starting my DB I want to make a check if a certain field is filled in.
    Therefor I made a query (using the Access Query-wizard) which I use for generating a report.
    Now even if there are no records in the query a (empty) report is shown.
    So I was thinking of using a recordset (based on my query) and if the recordcount is null the report will not be shown.
    But is it possible to create a recordset based on an access-query ?
    If yes how can I do that ?

    Thanks

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set rst = dbs.QueryDefs("qryCheck").OpenRecordset
    If rst.EOF then
    'We have no records
    Else
    'We do
    End If
    rst.Close

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Risky
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set rst = dbs.QueryDefs("qryCheck").OpenRecordset
    If rst.EOF then
    'We have no records
    Else
    'We do
    End If
    rst.Close

    I did following, but it gives me an error.

    Code:
    Private Sub Form_Load()
        Dim rs As New ADODB.Recordset
        
        rs.Open CurrentDb.QueryDefs("myQuery"), CurrentProject.Connection, adOpenStatic, adLockReadOnly
        
        If rs.RecordCount > 0 Then
            'Here comes the rest of my code
        End If
        
        rs.Close
    Errormessage is :
    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another


    Any idea?

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Peterborough, UK
    Posts
    2
    Artemide,

    The previous solution posted for you uses DAO, not ADO. If you want to use the code presented, you'll have to use DAO.Recordset in that sub, or the whole of your code.

Posting Permissions

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