MoveFirst problem ...

[techsup07]

Hi,
I am confused ... i have a report where the detail section uses a procedure.

basically, the part of the code I am getting a Run-Time error '3021'. No Current Record

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

etc ...

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
-> errors at:  .MoveFirst
        Do Until .EOF

however, i know there is data. There are 13 records of which has multiple detail records.

So I go into the Design and go to my record source and preview that to verify. I have a total of 42 detail records.

So why would I get the run-time error '3021' No Current Record?

 

[techsup07]

now, more interestingly ... i was able to print the 1st page prior that message.

But when trying to print or preview the 2nd page, i received Run-time error End of Record.

This error was the first, then when trying to see what might be the issue and previewing the report again, am now getting the No Current Record, so i can't even see the 1st page let alone all of the records.

but i am still able to preview it in Design view in the Record Source SQL statement.

 

[dhookom]

It's a bit difficult to help when we don't know what you are putting into strSQL. Do you understand the On Format event can run more than once per record in your report's record source?

Can I ask what you expect your code to do?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techsup07]

Hi dhookom,

sorry, I don't always know what's too much info or not enough info from lack of experience ...

   strSQL = "SELECT InvoiceNumber, SortOrder, Category, Items, " & _
        "Format " & _
        "FROM Details_Inv LEFT JOIN Options " & _
        "ON Details_Inv.Item = Options.Items " & _
        "WHERE InvoiceNumber = '" & Me.InvoiceNumber & "' " & _
        "ORDER BY SortOrder"

not sure what other information to provide.
What's confusing for me is that all this worked for awhile. Basically until today. So I'm not sure what or why I would get this message and therefore not sure how to fix it.

 

[dhookom]

Is it possible you have blank invoice numbers?
Can I ask what the code is supposed to accomplish? Are you trying to concatenate Category, Items, and/or Format values into a single expression?

Have you tried using code like

If Not (rs.EOF and rs.BOF) then

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techsup07]

humh,
well, i got the 1st page to preview but can't print or preview add'l pages without getting the message.

i'm not sure about your questions regarding the blank invoice?

each invoice has various types of data, on the JOIN to the Options table though, I did find 2 invoices that each had one item missing. Meaning these 2 items were not in the Options table but in the Details_Inv.

Basically the detail section is programmatically formatting the detailed information (ok, that sounded dumb )


how would and where would I use the If Not statement exactly?

 

[techsup07]

ok, it took me several different ways to determine that what's happening is that in the order of the invoices, it came to one that the item is in fact missing the Options table and it won't print the rest of the invoices.

How do I still make that invoice print and print the item even though it doesn't exist in the Options table?

i tried changing the INNER JOIN to LEFT JOIN and did not change anything.

 

[dhookom]

I'm not sure why you don't answer my twice asked question regarding the purpose of your code. You have only showed us a snippet.

However, you can try:

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
        If Not (.eof and .bof) Then
          .MoveFirst
             Do Until .EOF

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techsup07]

I apologize as I thought I had answered your question on what I want the code to accomplish ...
that being the report Detail Section is to display all the detail items in a specific format. Hence the OnFormat code to accomplish this.
Because of the JOIN from the Detail_Inv to the Options table, apparently it stops the code when it came to an invoice where even one item did not exist in the Options table when it's in the invoice (Detail_Inv).

I need to change it so that it'll either:
1. print the item(s) anyway even if it does not exist in the Options table. although it'll error since it can't format the item since it doesn't exist. So I suppose I will need to someone make these items stand out to let the user know that these items are not formatted because they are not in the Options table?
2. OR skip the invoice but print an error list of the Invoice Number and the item that's not in the Options table and reason why the invoice skipped??

What would you suggest?


BTW -
I am getting a compile error on
End With

this is what is at the end of the code and it's saying I don't have a End With.

            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

All my If has and End If in between the With rs, so now I don't get why it's saying I don't have an End With?

 

[dhookom]

Share your code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]