2451 Runtime error with DoCmd.OpenReport

[ahoffman50]

I have a button on my form that prints a report and one that previews the report. The preview report button works perfectly, but the print button gives a 2451 runtime error. The code for each button is exactly the same with the exception of the DoCmd.OpenReport stDocName, acPreview and DoCmd.OpenReport stDocName, acNormal.

See the attached file for code.

 

[genomon]

I don't have time to look it up; what is the Err.Description for 2451?

"Teamwork means never having to take all the blame yourself."

 

[ahoffman50]

It is:
"The report REPORTNAME you entered is misspelled or refers to a report that isn't open or doesn't exist."

REPORTNAME Changes dependent on which report I am trying to open.

 

[genomon]

Well for starters, the constants you need are acViewPreview & acViewNormal, not just "acPreview" & "acNormal".
Maybe that will help?

"Teamwork means never having to take all the blame yourself."

 

[ahoffman50]

Thanks, but it did not help. When I click on the print button, it prints as the error message pops up, but it does not filter the results.

 

[genomon]

Assuming School is a field name, this might be incorrect syntax:

Case "ISURHIT"
strWhere = strWhere & "([School] = ""ISU"" OR [School] = ""RHIT"") AND "

This is not incorrect - enclose string literals in single quotes:

Case "ISURHIT"
strWhere = strWhere & " School = 'ISU' OR School = 'RHIT' AND "

Is this by any chance a first effort?


"Teamwork means never having to take all the blame yourself."

 

[ahoffman50]

Yes this is my first go at a database of this size and scope.

School is a field name on the table to which the report refers.

If it is incorrect syntax, why does it work correctly?

 

[genomon]

Could be "" is interpreted as " & "?
A good starting place is here:

http://www.justphukit.com/sql/sql-tutorials-1.php

Also an Access help search on concatenating strings would be helpful. My head is full & I'm outta here (and now that I look at the snippet I posted it looks odd, too). I'll try & return tomorrow & see what's transpired. Good luck!

"Teamwork means never having to take all the blame yourself."

 

[Remou]

I would say that your problem is here:

stDocNafrm = "Sacrafrmnts"

I also wonder if Grad Year is a text field?

 

[kjv1611]

I think this one has at least one or two errors, just guessing at the data types and all:

    If Not IsNull(Me.txtYear) Then
        strWhere = strWhere & "[Grad Year] = """ & Me.txtYear & """ AND "
    End If

I think it SHOULD be:

    If Not IsNull(Me.txtYear) Then
        strWhere = strWhere & " " & TableName.[Grad Year] & "= '" & Me.txtYear & "'" AND "
    End If

Or similar. That might not be the absolute correct, but I'm not putting it in to test it right now. There, I changed your use of double quotes to single quotes, which may not be what you're after either. Also, if [Grad Year] is a field name of a table, then it should have the table name and field name, but not inside of quotes, or else Access just sees that as text. If it's a field on the form, then you need just the field name (again, not inside quotes).

'Course, I could be missing something altogether, but that looks like a problem spot to me.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

The problems with the SQL would not produce " .. REPORTNAME you entered is misspelled ..", whereas:

stDocNafrm = "Sacrafrmnts"

Is almost certainly misspelt.

 

[ahoffman50]

BTW, I should mention that I am using Access 97. I will try the suggestions above next week and let you know how it pans out. Thank you for all the help!

 

[Remou]

In particular, check the spelling of Sacraments, it does not have an 'f'.

 

[ahoffman50]

Remou, I cannot find that misspelling anywhere in the code.

Perhaps I should explain in more detail what this is doing. This is a form with a bunch of checkboxes and a few textboxes that are used to create a filter string to apply to different reports based on what the user selects to filter. For example, if a user wants to see all the people in the database that are eucharistic ministers and lectors, they will check the appropriate checkboxes and click on view report. The Ministry report should open with only the eucharistic ministers and lectors. All of my reports reference the same table.

Grad year and hometown are text boxes the rest are checkboxes.

I remember trying to use single quotes before and it would not work properly, but when I used double quotes it worked.

When I open a report in acViewNormal, does it automatically close the report or does it work like acViewPreview? The way this is reacting it leads me to believe that it opens the report, prints it, closes the report and moves to the next line, which cannot be processed because the report is now closed. Is this the way it works? If so, how can I open the report, apply a filter and print it with one button?

 

[Remou]

You show this section in your code:

        If frm.chkIsLector = -1 Or frm.chkIsEucharistic = -1 Or frm.chkIsServer = -1 Or frm.chkIsHospitality = -1 Or frm.chkIsMusic = -1 Or frm.chkIsReligious = -1 Then
            stDocNafrm = "Ministry"
            DoCmd.OpenReport stDocNafrm, acPreview
            Reports!Ministry.Filter = strWhereMinistry
            Reports!Ministry.FilterOn = True
        End If
        If frm.chkIsFaith = -1 Or frm.chkIsPrayer = -1 Or frm.chkIsService = -1 Or frm.chkIsSocial = -1 Or frm.chkIsWorship = -1 Then
            stDocNafrm = "Interests"
            DoCmd.OpenReport stDocNafrm, acPreview
            Reports!Interests.Filter = strWhereInterest
            Reports!Interests.FilterOn = True
        End If
        If frm.chkNeedConfirmation = -1 Or frm.chkNeedEucharist = -1 Or frm.chkNeedReconciliation = -1 Or frm.chkNotCatholic = -1 Then
            stDocNafrm = "Sacrafrmnts"
            DoCmd.OpenReport stDocNafrm, acPreview
            Reports!Sacrafrmnts.Filter = strWhereSacrafrmnts
            Reports!Sacrafrmnts.FilterOn = True
        End If
        If Not IsNull(frm.txtHofrmtown) Then
            stDocNafrm = "Hofrmtown"
            DoCmd.OpenReport stDocNafrm, acPreview
            Reports!Hofrmtown.Filter = strwhere
            Reports!Hofrmtown.FilterOn = True
        End If
        'if special boxes are checked or hofrmtown is filled in do not create the all report
        If Not IsNull(frm.txtHofrmtown) Or frm.chkIsLector = -1 Or frm.chkIsEucharistic = -1 Or frm.chkIsServer = -1 Or frm.chkIsHospitality = -1 Or frm.chkIsMusic = -1 Or frm.chkIsReligious = -1 Or frm.chkIsFaith = -1 Or frm.chkIsPrayer = -1 Or frm.chkIsService = -1 Or frm.chkIsSocial = -1 Or frm.chkIsWorship = -1 Or frm.chkNeedConfirmation = -1 Or frm.chkNeedEucharist = -1 Or frm.chkNeedReconciliation = -1 Or frm.chkNotCatholic = -1 Then
        'do nothing
        Else
            stDocNafrm = "All"
            DoCmd.OpenReport stDocNafrm, acPreview
            Reports!All.Filter = strwhere
            Reports!All.FilterOn = True
        End If
    End If

Ministry and Interests are normal, then you have Sacrafrmnts, which may be the name of your report, but then again it may not be. It would be useful to step through the code and check on which line the error is occuring. It is possible that a report is not opening, I guess.

 

[ahoffman50]

I see what you are talking about now, it looks like you ran a replace and replaced all the "Me" to "frm" which would change the "me" in sacraments to "frm". If you look at the original code in the attachment, you will notice that it is spelled correctly.

As it has been years since I took a programming class, I have forgotten how to step through the code. Please, jog my memory.

Thanks again for all the tips.

 

[Remou]

Apologies, I replace Me to step without a form, but that is a poor excuse

However, it still seems likely that a report name is misspelt.

You can step through the code by setting a breakpoint (F9 to toggle) and then pressing F8 to run each line.

 

[PHV]

Why not simply use the 4rd argument of the DoCmd.OpenReport method ?
DoCmd.OpenReport "Interests", acViewNormal, , strWhereInterest

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886