Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    17

    Question Unanswered: Form has No Data from a Query

    I have a form that gets its data from a query, sometimes the query will produce no records, and the form opens up blank, ideally I would like the form not to open if there are no records and a message box open telling the user to input different valuves into the criteria for the query.
    Any suggestions?

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Create an "Open" even procedure for the form that you're talking about.

    Within that code do something like this:

    Code:
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cnt As Integer
    
    Set con = Application.CurrentProject.Connection
    set rs = con.Execute "SELECT Count(*) as num_rows FROM your_query_name"
    
    cnt = rs.Fields("num_rows").Value
    
    rs.close
    Set rs = nothing
    con.close
    set con = nothing
    
    If cnt = 0 Then
         MsgBox "Form will be closed"
         DoCmd.Close acForm, "your_form_name", acSaveNo
    End If

  3. #3
    Join Date
    Sep 2004
    Posts
    17

    Unhappy

    hanks Warren,
    I have done this as you suggest but get a complie error with the line --
    set rs = con.Execute "SELECT Count(*) as num_rows FROM your_query_name"

    I've have changed the your_query_name bit to the name of my query

    Any idea why this is happening?

    Thanks Adrian

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    SELECT Count(yourTableName.anyFieldName) AS [Number of records]
    FROM yourTableName;

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    Another way, is the use of the recordsetclone property( Acc97). The Recordsetclone is a copy of the recordset used by your form, and you use like this :

    Code:
    if Me.recordsetclone.EOF  and  Me.recordsetclone.BOF then
    
    Msgbox "toto"
    exit sub
    Endif

  6. #6
    Join Date
    Sep 2004
    Posts
    17
    Thanks Jepi

Posting Permissions

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