Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Question Unanswered: Form and Query problem

    All,

    I'm an Access novice and while creating a database I have hit upon a problem displaying the results of a query on a form.

    I am using a form in continuous form view with bound textboxes to display the results of a query. This works perfectly. However I wish to display a message to the user if the query contains no results and prevent the form from loading.

    I have looked for an equivalent form property to that of the report property - On No Data. I have not found one, does an equivalent exist?

    If not would it be possible to place some code in the form to examine the query and if empty report to the user and close the form.


    Any hints or help greatly appreciated.

    Yours frustrated,

    kevinlmorgan

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Place this code in the form_load event

    Dim rst As Recordset

    Set rst = New ADODB.Recordset
    rst.Open Me.RecordsetClone
    If rst.RecordCount = 0 Then
    MsgBox "Some message telling the user what's up"
    DoCmd.Close acForm, "Form Name", acSaveNo
    End If
    rst.close
    Cathy

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Angry

    Cathy,

    Thank you very much for you response, However I am receiving a run time error when I load the form.

    I have included the as you suggested the following code in the Form_load event.


    Dim rst As Recordset

    Set rst = New ADODB.Recordset
    rst.Open Me.RecordsetClone
    If rst.RecordCount = 0 Then
    MsgBox "No Data"

    'Close form
    'DoCmd.Close acForm, "FORMNAME", acSaveNo

    End If
    rst.Close


    When the form loads I get the following message:

    Run-time error '3001:

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


    Debugging the code highlights the following line:

    rst.Open Me.RecordsetClone


    From my limited experience of Access I presume what the code does is copy the recordsource of the form and then counts the number of records in this copy. Am I correct in my assumption?

    Any help on why I am receiving this error would be more than appreciated.


    Thank you

    Kevinlmorgan

  4. #4
    Join Date
    Feb 2004
    Posts
    3

    Thumbs up

    Cathy,

    Success!

    After trying to solve my run-time error to no avail, I decided, based on your suggested code to examine RecordSetClone property in Access VB help files.

    As a result I played around with some code and eventually came up with this:

    If Forms!formname.RecordsetClone.RecordCount = 0 Then
    MsgBox "Some message telling the user what's up"
    DoCmd.Close acForm, "formname", acSaveNo
    End If

    Which worked! Though I am still none the wiser to the cause of my run-time error

    Thank you again.

    All the best,

    Kevinlmorgan

  5. #5
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    You are receiving the error because you need to add a reference to the ADO library to your VB project. On the VB Tools menu, click on References. Scroll down until you find the Microsoft ActiveX Data Objects 2.7 library.

    Glad to hear that you found something comperable. That's one of the beauties of programming. There is always more than one way to accomplish the task!
    Cathy

Posting Permissions

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