Solved Getting "No Current Record" on query that previously worked (1 Viewer)

[Sheridan]

One of my queries which has always worked before now gives a "No Current Record" error. I didn't change the query. Another query which is almost identical save for the fact that it uses parameters and a WHERE statement still works.

This started about an hour after I split the database into frontend/backend with Access's built in split tool, although the query still worked for a short time after the split. I'm not sure splitting the database is related to the cause of the error or not.

I use the query with a datasheet form. The error first occurred when I was sorting and filtering the datasheet in the form.

I've tried compact and repair. I've tried copy/pasting the sql of the query into a new query.

I finally went back to my original unsplit database, split it again, and now the query works. But I fear this problem will come back if I can't identify the cause.

The queries are really long so please forgive me. Here is the query that started giving "No Current Record":

SELECT "Edit" AS Edit,
INVOICES.MEMBER_ID_FK,
INVOICES.INVOICE_NUM_PK,
INVOICES.INVOICE_DATE,
INVOICES.DUE_DATE,
INVOICES.START_DATE,
INVOICES.END_DATE,
INVOICES.AGE_AT_INVOICE_DATE,
INVOICES.PAID_DATE,
INVOICES.Invoice_Paid,
INVOICES.INVOICE_MONTH,
INVOICES.BENEFITS_MONTH,
INVOICES.PAPER_ACH,
InvoiceData_Live.NAMEL,
InvoiceData_Live.NAMEF,
InvoiceData_Live.NAMEFL,
InvoiceData_Live.STREET,
InvoiceData_Live.CITY,
InvoiceData_Live.STATE,
InvoiceData_Live.ZIP,
InvoiceData_Live.NAMEF & ' ' & InvoiceData_Live.NAMEL & Chr(13) & Chr(10) & InvoiceData_Live.STREET & Chr(13) & Chr(10) & InvoiceData_Live.CITY & ', ' & InvoiceData_Live.STATE & ' ' & InvoiceData_Live.ZIP AS address, 'Dear ' & InvoiceData_Live.NAMEF & ":" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & 'The following is your ' & INVOICES.INVOICE_MONTH & ' bill which will pay for ' & INVOICES.BENEFITS_MONTH & ' benefits. This bill reflects payments received by ' & INVOICES.INVOICE_DATE & '.' AS message,
INVOICES.LIFE_AMOUNT,
INVOICES.DEPENDENT_AMOUNT,
INVOICES.DENTAL_AMOUNT,
INVOICES.SUPPLE_AMOUNT,
INVOICES.AMOUNT_PAID,
INVOICES.CREDIT_AMOUNT,
INVOICES.INITIAL_POCD_AMOUNT,
Sum(INVOICES.LIFE_AMOUNT+INVOICES.DEPENDENT_AMOUNT+INVOICES.DENTAL_AMOUNT) AS [Monthly Amount Due],

FORMAT(SUM((SELECT NZ(SUM(LIFE_AMOUNT + DEPENDENT_AMOUNT + DENTAL_AMOUNT - AMOUNT_PAID - CREDIT_AMOUNT + INITIAL_POCD_AMOUNT),  0)
FROM INVOICES AS OD WHERE OD.INVOICE_DATE < INVOICES.INVOICE_DATE AND OD.MEMBER_ID_FK = INVOICES.MEMBER_ID_FK AND OD.EXCLUDE_FROM_RUNNING <> TRUE) - (INVOICES.CREDIT_AMOUNT+INVOICES.INITIAL_POCD_AMOUNT)), "currency") AS [Past Due or Credit],

FORMAT(SUM((SELECT NZ(SUM(LIFE_AMOUNT + DEPENDENT_AMOUNT + DENTAL_AMOUNT - AMOUNT_PAID - CREDIT_AMOUNT + INITIAL_POCD_AMOUNT),  0)
FROM INVOICES AS OD WHERE OD.INVOICE_DATE < INVOICES.INVOICE_DATE AND OD.MEMBER_ID_FK = INVOICES.MEMBER_ID_FK AND OD.EXCLUDE_FROM_RUNNING <> TRUE) + (INVOICES.LIFE_AMOUNT+INVOICES.DEPENDENT_AMOUNT+INVOICES.DENTAL_AMOUNT - INVOICES.CREDIT_AMOUNT + INVOICES.INITIAL_POCD_AMOUNT)), "currency") AS [Total Amount Due],

INVOICES.BANK,
INVOICES.COMMENTS

FROM InvoiceData_Live INNER JOIN INVOICES ON InvoiceData_Live.ID = INVOICES.MEMBER_ID_FK

GROUP BY INVOICES.MEMBER_ID_FK, INVOICES.INVOICE_NUM_PK, INVOICES.INVOICE_DATE, INVOICES.BENEFITS_MONTH, INVOICES.DUE_DATE, INVOICES.START_DATE, INVOICES.END_DATE, INVOICES.AGE_AT_INVOICE_DATE, INVOICES.PAID_DATE, INVOICES.Invoice_Paid, INVOICES.INVOICE_MONTH, INVOICES.PAPER_ACH, InvoiceData_Live.NAMEL, InvoiceData_Live.NAMEF, InvoiceData_Live.NAMEFL, InvoiceData_Live.STREET, InvoiceData_Live.CITY, InvoiceData_Live.STATE, InvoiceData_Live.ZIP, INVOICES.LIFE_AMOUNT, INVOICES.DEPENDENT_AMOUNT, INVOICES.DENTAL_AMOUNT, INVOICES.SUPPLE_AMOUNT, INVOICES.AMOUNT_PAID, INVOICES.CREDIT_AMOUNT, INVOICES.INITIAL_POCD_AMOUNT, INVOICES.BANK, INVOICES.COMMENTS

ORDER BY INVOICES.MEMBER_ID_FK, INVOICES.INVOICE_DATE, INVOICES.INVOICE_NUM_PK;

And here is the almost identical query which still works:

PARAMETERS
[Input month number in form of MM] Integer,
[Input year number in form of YYYY] Integer;

SELECT
INVOICES.MEMBER_ID_FK,
INVOICES.INVOICE_NUM_PK,
INVOICES.INVOICE_DATE,
INVOICES.DUE_DATE,
INVOICES.START_DATE,
INVOICES.END_DATE,
INVOICES.AGE_AT_INVOICE_DATE,
INVOICES.PAID_DATE,
INVOICES.Invoice_Paid,
INVOICES.INVOICE_MONTH,
INVOICES.BENEFITS_MONTH,
INVOICES.PAPER_ACH,
InvoiceData_Live.NAMEL,
InvoiceData_Live.NAMEF,
InvoiceData_Live.NAMEFL,
InvoiceData_Live.EUID,
InvoiceData_Live.DOD,
InvoiceData_Live.DOD_Flg,
InvoiceData_Live.ACTIVE,
InvoiceData_Live.STREET,
InvoiceData_Live.CITY,
InvoiceData_Live.STATE,
InvoiceData_Live.ZIP,
INVOICES.LIFE_AMOUNT,
INVOICES.DEPENDENT_AMOUNT,
INVOICES.DENTAL_AMOUNT,
INVOICES.SUPPLE_AMOUNT,
INVOICES.AMOUNT_PAID,
INVOICES.CREDIT_AMOUNT,
INVOICES.INITIAL_POCD_AMOUNT,
Sum(INVOICES.LIFE_AMOUNT+INVOICES.DEPENDENT_AMOUNT+INVOICES.DENTAL_AMOUNT) AS [Monthly Amount Due],

FORMAT(SUM((SELECT NZ(SUM(LIFE_AMOUNT + DEPENDENT_AMOUNT + DENTAL_AMOUNT - AMOUNT_PAID - CREDIT_AMOUNT + INITIAL_POCD_AMOUNT),  0)
FROM INVOICES AS OD WHERE OD.INVOICE_DATE < INVOICES.INVOICE_DATE AND OD.MEMBER_ID_FK = INVOICES.MEMBER_ID_FK AND OD.EXCLUDE_FROM_RUNNING <> TRUE) - (INVOICES.CREDIT_AMOUNT+INVOICES.INITIAL_POCD_AMOUNT)), "currency") AS [Past Due or Credit],

FORMAT(SUM((SELECT NZ(SUM(LIFE_AMOUNT + DEPENDENT_AMOUNT + DENTAL_AMOUNT - AMOUNT_PAID - CREDIT_AMOUNT + INITIAL_POCD_AMOUNT),  0)
FROM INVOICES AS OD WHERE OD.INVOICE_DATE < INVOICES.INVOICE_DATE AND OD.MEMBER_ID_FK = INVOICES.MEMBER_ID_FK AND OD.EXCLUDE_FROM_RUNNING <> TRUE) + (INVOICES.LIFE_AMOUNT+INVOICES.DEPENDENT_AMOUNT+INVOICES.DENTAL_AMOUNT - INVOICES.CREDIT_AMOUNT + INVOICES.INITIAL_POCD_AMOUNT)), "currency") AS [Total Amount Due],

INVOICES.BANK,
INVOICES.COMMENTS

FROM InvoiceData_Live
INNER JOIN INVOICES ON InvoiceData_Live.ID = INVOICES.MEMBER_ID_FK

WHERE YEAR(INVOICES.INVOICE_DATE) = [Input year number in form of YYYY] AND MONTH(INVOICES.INVOICE_DATE) = [Input month number in form of MM]

GROUP BY INVOICES.MEMBER_ID_FK, INVOICES.INVOICE_NUM_PK, INVOICES.INVOICE_DATE, INVOICES.BENEFITS_MONTH, INVOICES.DUE_DATE, INVOICES.START_DATE, INVOICES.END_DATE, INVOICES.AGE_AT_INVOICE_DATE, INVOICES.PAID_DATE, INVOICES.Invoice_Paid, INVOICES.INVOICE_MONTH, INVOICES.PAPER_ACH, InvoiceData_Live.NAMEL, InvoiceData_Live.NAMEF, InvoiceData_Live.NAMEFL, InvoiceData_Live.EUID, InvoiceData_Live.DOD, InvoiceData_Live.DOD_Flg, InvoiceData_Live.ACTIVE, InvoiceData_Live.STREET, InvoiceData_Live.CITY, InvoiceData_Live.STATE, InvoiceData_Live.ZIP, INVOICES.LIFE_AMOUNT, INVOICES.DEPENDENT_AMOUNT, INVOICES.DENTAL_AMOUNT, INVOICES.SUPPLE_AMOUNT, INVOICES.AMOUNT_PAID, INVOICES.CREDIT_AMOUNT, INVOICES.INITIAL_POCD_AMOUNT, INVOICES.BANK, INVOICES.COMMENTS

ORDER BY INVOICES.MEMBER_ID_FK, INVOICES.INVOICE_DATE, INVOICES.INVOICE_NUM_PK;

 

[theDBguy]

It’s possible that the file got corrupted during the split process, which should be gone now and may not come back anymore.

 

[Sheridan]

I remembered I happened to have been recording a training video when this first occurred so I reviewed it. I use this query for a form. The "edit" column on my form is turned into links that open a modal window to view a single record.

Private Sub Edit_Click()
    DoCmd.OpenForm "Invoice Modal Form", , , WhereCondition:="[INVOICE_NUM_PK]=" & Me!INVOICE_NUM_PK, WindowMode:=acDialog
    Me.Requery
End Sub

You can edit the record in the modal window. If you click 'Ok' it runs the Close Window embedded macro. After that Me.Requery of Edit_Click() should run on the datasheet form.

I've done this several times before---opening a record in the modal window, editing it, clicking 'Ok', and returning back to the datasheet. This time when the error occurred, there was a little bit of a snafu that might be relevant? I clicked 'edit' to open a record in a modal window, I cleared out the value of a currency field, but accidentally left the dollar sign in so it of course complained that the value wasn't valid, so I then just deleted the dollar sign, leaving the currency field completely blank, and clicked 'OK' which runs the Close Window embedded macro. After closing the window it threw run error 3420 'Object Invalid or No Longer Set' and every cell on the datasheet form changed to '#Name?' After that, if I tried opening the form again, it started giving the 'No Current Record' error. Maybe it had something to do with the bit where I had accidentally left the dollar sign in? Or maybe it was because I left the field blank rather than setting it to 0?

edit: I should add that after this it throws the 'No Current Record' error regardless of whether I open the form that uses the query, or run the query directly.

 

[The_Doc_Man]

First thing to note is that the FROM clause of the two queries is pretty much identical including the JOIN members and the joining fields. Your MAIN query doesn't have a WHERE clause so the only way to get a "No Current Record" from it would be if NO records matched in the JOIN clause. But you said that queries with two otherwise identical FROM clauses produce different error behavior. So we have to look elsewhere.

The next place to look is that both queries have two sub-queries that have FROM INVOICES AS OD and they both have non-trivial WHERE clauses. It is possible that you might have over-qualified their WHERE criteria such that you have "No Current Record" for one of the sub-queries. I.e. the WHERE clause eliminated every possible matching record in the sub-query.

If the problem has gone away, I suppose it is possible that you had some corruption and some change that you made to the query cleaned it up. It is ALSO possible that this could go away spontaneously if new records were added that filled in the prior gaps for the sub-query. In other words, there used to be no matching records for that WHERE clause but when data was added, the new records matched and thus eliminated the error.

I cannot tell from here which of the two possibilities (query corruption or data-related error) is correct, but at least one of the options is totally benign.

 

[Sheridan]

I've done this several times before---opening a record in the modal window, editing it, clicking 'Ok', and returning back to the datasheet. This time when the error occurred, there was a little bit of a snafu that might be relevant? I clicked 'edit' to open a record in a modal window, I cleared out the value of a currency field, but accidentally left the dollar sign in so it of course complained that the value wasn't valid, so I then just deleted the dollar sign, leaving the currency field completely blank, and clicked 'OK' which runs the Close Window embedded macro. After closing the window it threw run error 3420 'Object Invalid or No Longer Set' and every cell on the datasheet form changed to '#Name?' After that, if I tried opening the form again, it started giving the 'No Current Record' error. Maybe it had something to do with the bit where I had accidentally left the dollar sign in? Or maybe it was because I left the field blank rather than setting it to 0?

I found the problem.

I redid these steps on a copy of my database, and the error came back. It's because the null value I left in the currency field on that one record. Once I replaced the null field with 0 on that record, the error resolved itself. The null value probably broke those sub-queries because they don't have adequate protection against null values, so I'm going to work on that.

Sorry, probably should have talked to my rubber duck a little longer before posting.

 

[The_Doc_Man]

Yep, data-related. Easy to fix once you find it.

Look, about that rubber duck... if it starts giving you good answers, is it for sale?

 

[Gasman]

When you have not changed anything and you are sure about that, then it will be the data. Now whether that is original data, or data you have modified/created is up to you.