Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank forms (Access 97)

    I have a form that has three text boxes to be filled out. When a button is clicked on this form, another form is opened that is based on a parameter query that gets its arguments from the first form. When there are no records, this second form is blank. Is there any way to give the user the message that there are no records and bypass the blank screen treatment? I guess this isn't an error that can be trapped. I've been trying different things and I have missed the solution with great consistency. Any suggestions?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank forms (Access 97)

    There are probably more graceful solutions, but on the OnClick Event of that button Count the number of records returned from your parameter query. If the Count = 0, give the user a message (use MsgBox) and do not open the form. When the Count >0, open the form as usual.

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank forms (Access 97)

    Thanks so much for this idea. I have tried this and now I am getting an error # 3061,"Two few parameters. Three expected". My form has three text boxes that must be filled in before the query can be run by clicking a button, and worked fine before I added this code. Is this what you meant?
    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qrySelTime")
    rst.MoveLast
    If rst.RecordCount = 0 Then
    MsgBox "There are no records for this User."
    GoTo Exit_cmdInquire_Click:
    Else
    DoCmd.OpenForm stDocName, acNormal, acEdit

    End If

    Now I completely lost. Can anyone help? Any idea will be appreciated.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Blank forms (Access 97)

    Do you know how far it got with this code before the error?

    To test this you can set breakpoints, or just insert a msgbox("Here") in the code and progressively move it down the code until it doesn't get displayed any more. Find out whether the problem occurs only if count = 0, only if count >0, or in both cases.

    In the code you posted stDocName is not declared, nor given a value. Is this so in your real code?

    Is the form providing the parameters for the query still open behind the scenes?
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank forms (Access 97)

    The code runs until the line
    Set rst = dbs.OpenRecordset("qrySelTime")
    at which point it drops directly to the error trapping code at the bottom of the module which I have set up to give me the error number and message and then exits the module. The variable stDocName is declared at the top of the module (stDocName = frmUserInquiry) before the code I have to make sure all three text boxes are filled in. This runs before the button can be clicked. The form(frmInquiryParam) stays open in the background even when the new form (frmUserInquiry) opens. Without this new code to count records, the module has been running fine for a year. I just thought that a blank form with no information was rather rude, not to mention bad user interface, so I thought I'd find a way to offer some info and a graceful exit. Now, I just have to find an answer because it is bugging me badly.
    No good deed goes unpunished.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank forms (Access 97)

    From your description, I gather that qrySelTime takes its parameters from a form. You open the query as a DAO recordset. The problem is that DAO doesn't know about Access forms, so it sees the parameters as ordinary parameters, and raises an error because they haven't been filled in.

    One solution is to provide the parameter values in code:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("qrySelTime")
    ' Set parameter values based on values on form.
    qdf.Parameters("Forms!frmInquiryParam!txtOne") = Forms!frmInquiryParam!txtOne
    qdf.Parameters("Forms!frmInquiryParam!txtTwo") = Forms!frmInquiryParam!txtTwo
    qdf.Parameters("Forms!frmInquiryParam!txtThree") = Forms!frmInquiryParam!txtThree

    ' Open the recordset.
    Set rst = qdf.OpenRecordset

    <img src=/w3timages/blueline.gif width=33% height=2>

    and then proceed as before.

    Notes:
    You must replace Forms!frmInquiryParam!txtOne etc. by the parameters in your query, exactly as they are entered there.
    There is no real need to use rst.MoveLast. If the recordset is empty, rst.RecordCount = 0, otherwise it's <> 0. You *do* need rst.MoveLast if you need the exact number of records. You can also check for rst.EOF; this is true after opening the recordset if there are no records.

    Another solution is to use DCount on the table behind qrySelTime (or on a query without the parameters) with the appropriate WHERE condition. DCount "knows" about forms.

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank forms (Access 97)

    Thanks so much for this answer. The code works like a charm. I never thought about DAO recordsets not knowing about the form. I made an attempt to identify the parameters (obviously the wrong way) and then the error message asked for SIX parameters. I think I'll hunt down more info on DAO. And I think I'll experiment with DCount, too. Thanks again to all who helped. I learned a lot.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Blank forms (Access 97)

    Looking at what you had to do to make this work convinces me that it is much easier to do this task without using DAO.

    You just need one line:

    if dcount("[Keyfield]","qrySelTime") >0 then

    docmd.openform etc
    else
    msgbox("No records etc ")
    end if

    Just replace Keyfield by whatever field uniqely identifies records in the query.
    Regards
    John



  9. #9
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank forms (Access 97)

    Slick! I had a feeling before I originally posted that the answer would be something so simple I'd want to whack myself in the head. And now I've just thought of two other places to use this technique. Thanks!

Posting Permissions

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