Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    No current record (2002)

    On running a query I get a popup error message stating "No current record". Can anyone explain what this means?

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

    Re: No current record (2002)

    There are several possible causes. We'd have to know more about the query. Can you post the SQL for the query, or better, a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    Below is the SQL expression for the query. I generated the application to assist my wife in coordinating & logging visits to shutins from our church. The application maintains a table of shutins (tblShutins) and a table of visits (tblVisits) with a one to many relationship. I recently did two things:

    1. I added two yes/no fields to the Visits table: Visit and Phone. This seemed to work ok until:
    2. I split the database into a front end and backend.

    I think that the latter operation must have done something to the relationships, but I'm not sure what. If I omit from the Select Query the terms involving the two new fields, then the query works. Why adding these new fields causes a problem is beyond me.



    SELECT Max(tblVisits.Visited) AS MaxofVisited, tblShutins.Shutin, First(tblVisits.Visitor) AS FirstOfCaller, tblShutins.Priority AS Expr2, Choose([Priority],"Hi","Med","Low") AS Ptext, tblShutins.Notice, tblShutins.City, tblShutins.Zip, tblVisits.Visit, tblVisits.Phone
    FROM tblShutins LEFT JOIN tblVisits ON tblShutins.ID = tblVisits.VisitsID
    GROUP BY tblShutins.Shutin, tblShutins.Priority, Choose([Priority],"Hi","Med","Low"), tblShutins.Notice, tblShutins.City, tblShutins.Zip, tblVisits.Visit, tblVisits.Phone, tblShutins.Inactive, tblShutins.Deceased, tblShutins.CardOnly
    HAVING (((tblShutins.Inactive)=False) AND ((tblShutins.Deceased)=False) AND ((tblShutins.CardOnly)=False))
    ORDER BY tblShutins.Shutin;

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

    Re: No current record (2002)

    Yes/No fields can cause problems in totals queries with outer joins - see 'No Current Record' error in Group By query with outer joins - SOLUTION by Allen Browne.

    Try this:

    SELECT Max(tblVisits.Visited) AS MaxofVisited, tblShutins.Shutin, First(tblVisits.Visitor) AS FirstOfCaller, tblShutins.Priority AS Expr2, Choose([Priority],"Hi","Med","Low") AS Ptext, tblShutins.Notice, tblShutins.City, tblShutins.Zip, Nz(tblVisits.Visit, False) AS Visit, Nz(tblVisits.Phone, False) AS Phone
    FROM tblShutins LEFT JOIN tblVisits ON tblShutins.ID = tblVisits.VisitsID
    GROUP BY tblShutins.Shutin, tblShutins.Priority, Choose([Priority],"Hi","Med","Low"), tblShutins.Notice, tblShutins.City, tblShutins.Zip, Nz(tblVisits.Visit, False), Nz(tblVisits.Phone, False), tblShutins.Inactive, tblShutins.Deceased, tblShutins.CardOnly
    HAVING (((tblShutins.Inactive)=False) AND ((tblShutins.Deceased)=False) AND ((tblShutins.CardOnly)=False))
    ORDER BY tblShutins.Shutin;

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    Hi Hans,

    Thanks, that worked just fine.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    I thought it worked, but it didn't work right. I have attached a vastly stripped down version with just the query and reports and some sample records..

    The query works EXCEPT I need it to include the Visit and Phone (yes/no) fields. However, when I add these fields to the query, I get the "no current record" message OR, depending on the relationship, the query returns more records than just the one with the latest date.

    The objective is to create a report based on this query that lists only the latest date ("Visited" field) of a visit to a shutin along with the Visit and Phone check boxes.

    Also, in my main database I tried to enforce referential integrity, but I get a message stating that there are some unmatched records somewhere. I've searched the records manually several times, and I can't find any unmatched Visits in the Shutin table. Is there some query that creates a list of unmatched records?

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

    Re: No current record (2002)

    To check for unmatched records use the "Find Unmatched Query wizard".
    In the queries page, click the new button at the top.

    Otherwise, just create a normal query, and try both and left an right joins. ie. double click the join line, and try all three different options for the join.
    Regards
    John



  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: No current record (2002)

    Your visits table does not have a primary key.

    Add an autonumber field say VisitIDKey (because you have already used visitID ) then include that field in your query.

    edited by John

    Sorry what follows is just wrong. I was careless and did not check carefully enough.
    *************
    Then create second query that joins the first query back to the visits table using this key field, and add the visit and phone fields to this second query.
    Base the report on this second query.
    ******************

    I would still try to do it in two stages. The first stage to identify what records you want, the second to add the extra data.

    I think you want the Max of Visited when you group by ID (Use the key field for this, not the name. 2 people can have the same name, but different ID's) (I can't see why you have also included the first of Visitor as well.)
    Then join this query back to both the other tables, to add in the extra fields. (Join to the Visits table using the MaxofVisited field and the ID field)

    Another thing I noticed: when you set criteria in a Group by query, you set the group by line to be "Where" not "group by"
    Regards
    John



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

    Re: No current record (2002)

    Does the attached version do what you want? I agrree with John's suggestion that it should be done in two steps, so I created two queries.

  10. #10
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    Hi Hans,

    Sorry to be such a pest. However, when I unzip and then open the file you sent I get a blank Access screen -- I can't even get the Database Display Window to appear! In the meantime I'll be trying some of the other suggestions.

  11. #11
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    Post deleted by Chuck Tucker

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    Are the tables you're trying to relate in the same database? You can't enforce RI between databases. You also can't enforce it from the front end between tables in the back end.
    Charlotte

  13. #13
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No current record (2002)

    You're right Charlotte -- I was trying to set up the relationship in the front-end whereas I should be doing it in the back end.

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

    Re: No current record (2002)

    The database opens normally for me in both Access 2002 and 2003 (I downloaded the zip-file from this thread again to make sure).

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

    Re: No current record (2002)

    When you open Hans' attachment do you have scroll bars in the Access window?


    The database window is very small, and low down. Uisng a screen resolution of 600 x 800 I can't see it at all until i scroll down.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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