Command button to open report Results

I have a database that I wrote a few years ago in Access 2000 originally. It has a form with some command buttons to open reports with data from the current record displayed in the form. Here's the code for one of the buttons:

	Private Sub AcceptanceLetter_Click()
Dim strReportName As String
   Dim strCriteria As String
    DoCmd.RunCommand acCmdSaveRecord
   strReportName = "rptAcceptanceLetters"
   strCriteria = "[FirstName]&[LastName]='" & Me![FirstName] & [LastName] & "'"
   DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

When I click that button, I get "Run time error '2427': You entered an expression that has no value."

But if I open the report without the WHERE clause in the OpenReport command, it opens just fine, though it displays all the records.

I have this same code in another database and it's working just fine.

Here's the only code in the report I'm trying to open:

	Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim strBody As String
Dim strPart1 As String
Dim strstartdate As Integer
strstartdate = [WeekendStartDayOfMonth] + 1

strBody = "Congratulation!  I am happy to confirm that arrangements have been made for you to attend the " & _
"Church Retreat " & [WeekendID] & " weekend to be held " & [WeekendStartMonth] & " " & [strstartdate] & " - " &
[WeekendEndDayOfMonth] & _
" at " & [LocationChurch] & ", " & [LocationAddress] & ", " & [LocationCity] & ", " & [LocationState] & "." & _
"  If for any reason you will be unable to attend this weekend, please let me know as soon as possible, because others may be
on a waiting list to attend.  If you have any questions or concerns, please call me.  We have a fun filled weekend planned,
and we are pleased that you will be with us."

Body.Value = strBody

End Sub

If I don't include any of the database fields in the strBody variable above, it also opens fine with the WHERE clause intact.

This used to work just fine when it was originally written. I'm not sure why it doesn't anymore. The database has moved to a different computer running Windows 7, and it's now on Access 2007 instead of the original Access 2000.

Any help is appreciated. Thanks!

What I want to do it when the report is generated and opened in a preivew mode, I want to give the user the options to:Save the report, Print the report or Email the report. How can I add these three buttons to the report?

I have a form that I use as a main menu for my database with several command buttons that open additional forms, reports, etc. Today I added a new form that is working fine with no bugs. However, when I added a command button to open that form a problem began. The new form opens and closes fine but when I try to close the database it asks me it I want to save the main form. I have saved numerous times but still the database won't close. It just continues to ask me to save. If I say "no" the database closes but the new command button is gone when I reopen, I've used the wizard with the same results.

What is preventing my database from closing?

I'm sure this is super simple and I'm just over-thinking.

I have a tabbed form with subforms on each tab. They all update how they are supposed to. I want to put a command button on one of the subforms that will open according to the record that the subform is currently on. I tried just doing the normal wizard, didn't work. So I looked online for some code and this is what I found:

Private Sub cmdOpenGrowerInforpt_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptGrowerInformation", acViewPreview, , "[GrowerID]='&"
End Sub

I bolded that last part because it's whats causing the code not to run. I don't know what I should be putting there to make it work. Hopefully I'm making sense. Any help would be great. Thanks!

Good morning all,

I have a slight issue when trying to open a report by using a command button.

What I have created is a IT database at work which allows users across the company too open the database log a problem so we can pick it up and deal with it.

We do however want to password protect the outstanding tasks report on the main switchboard. I have created a form with a simple text box and a OK button. Now users who know the password can enter it into the text box press ok and open the report.

The problem I am having is what code do I use to open the report.

I am using the following but doesnt appear too work

If Text0 = "******" Then

DoCmd.Close acForm, Me.Name

DoCmd.OpenReport "Outstanding Tasks"
End If

It comes up with a debug error on the DoCmd.Open Report line as well as trying to save something as a .xps.

Thanks in advance

EDIT: Solved it with the following line - DoCmd.OpenReport "ReportName", acViewPreview, , , , "OpenArgs"

I have a form that shows the results based on a select query. I would like to put a command button so that a report would print based on the information on the form. I've created a report based on the same query that the form is based on. However I need the report to be able to be accessed through the form. When I craete a command button to open report I get the same parameter prompt that I get when I open the form. Is there a for the Report to be previewed without prompting for that criteria again?

Thanks for all the help.

I've searched this one extensively and come up with nothing to help.

I have an unbound switchboard form with several command buttons.

I would like to set a filter in the code for each command button to open up my report "rptCurrentAll" to show all records where a field on the report called "Section" equals a variable.

For example where the filter is "NV" show all records where Section = NV
I suspect it's going to be a variant on DoCmd.OpenReport with a where clause but can't get the correct syntax.


Can anyone help me with a problem I am having getting a command button to open a report?

The VBA script generated by the wizard is

Private Sub openreport_Click()
On Error GoTo Err_openreport_Click

stDocName = "Recharge schemes all data"

Exit Sub

MsgBox Err.Description
Resume Exit_openreport_Click

End Sub

But this is resulting in nothing happening when I click on the button.
Any ideas?


I have created a database that is going to be used to look up medications to determine if donors are suitable for donating. I have a form called “Medication Search”. On this form I have a combo box that allows the user to select the medication either by a drop down or by typing the first letters and then selecting it. The problem I am having is it doesn’t show the correct information. To resolve this, I would like to have it where the user selects the medication and then click on a command button to show the report for that drug only. I created a command button to view report but it shows all medications and not the one selected. I am also open to other suggestions for searching. Ken

Hi all, I have a form with a command button to open a report. The user clicks a toggle on the form to select which records to show in the report - 'Delivered' Yes/No.

The subform displays orders which have not been processed using 'Processed' Yes/No, with No in the query criteria.

So what I would like to do is have the user select which orders to send to the report, and when the command button is clicked, update the 'Processed' field for those records to 'Yes, True, -1 etc' and requery the subform to remove those records. They have then been 'Processed'.

Could someone give me a little help with how to do this?

I would also like to add a warning dialog to the button like 'This will send selected orders for delivery and remove them from this form,are you sure you wish to continue?' Ok/Cancel. But I'm not sure how to do this either.

Here's all I have so far:-

	Private Sub btnOpenDelivRpt_Click()

  'Send selected records to delivery report.

  If Me!txtSelected = 0 Then

    MsgBox "Please select an Order to print", vbOKOnly, "Error"


    DoCmd.OpenReport "rptOrderDeliveries", acViewPreview

  End If

  Exit Sub

End Sub

Many thanks for any help!

I have put this code for a command buttons Click event, and it Ask me for the second part of the where condition. Here is the code:
DoCmd.OpenReport "Maintenance Work Request", acViewPreview,,"[tblMaintenanceWorkRequest]![ID]" & "[tblWorkPerformed]![ID]

When I click the button on the form it whats me to tell it what tblWorkPerformed!ID is. I can enter the answer, and it gives me the report.

Do I have the wrong syntax?

First off, I apologize, but I am not adept at Access 2007 yet, and am still fumbling through some basic tasks, BUT...I have a report setup called rptFullDetails that contains all the information I want to report on.

I put a command button on my Issue Details form that will load the rptFullDetails report, but currently it is pulling from every issue, as opposed to the specific one my form has open. How do I link the report to the form so it will only print the specific item I have open?


In the middle of creating a "master" form with multiple command buttons to open previously created forms, view reports, and/or print. Saved the form. When returned later the command button wizard no longer worked. Checked other databases saved on different company servers with the same result. Read the manual, searched through the help feature, and even called my company's help desk without success. Appears I turned the wizard off somehow and can not get it back on. As a last resort (have to have this up and running in the morning, started cutting and pasting code until I can figure our why the wizard stopped working. Any suggestions or thoughts is much appreciated.

I need my form to be filled out and sent to the purchasing dept for approval. I created the form and it opens in add mode, we enter the material needed and then we need to email it to purchasing. My thought is to create and email a report based on the record I just entered. I can't figure it out. I did create a command button but and it is opening a report but it's showing all the records. Any ideas please let me know. Thanks so much

Good morning All,
I have a form with a command button to open a report ( based on a query ) for the currently displayed record. here is the code I have used:

Private Sub CS_notes_Click()
On Error GoTo Err_CS_notes_Click
Dim stDocName As String

stDocName = "InternalSNwithRMAprodMASData"
DoCmd.OpenReport stDocName, acPreview, , "TLAUnit = " & Me.UnitSN & "'"

Exit Sub

MsgBox Err.Description
Resume Exit_CS_notes_Click

End Sub

I believe this came from this forum sometime. When the button is clicked I receive the error:
"Syntax error (missing operator) in query expression '(TLAUnit = 26712B')'

TLAUnit is the report field, UnitSN is the form field.
Any help with the error?

Two part question:
1)I am working on a Quality Control database to store test data on over 1700 different products. Right now I have a form designed that has a set of three tab controls so the end user could switch the three sets of test data subforms that are linked to the main product information, thus displaying just that products data.

I want to add a command button that opens up a report displaying the data information for the product displayed. I have yet to find out how i can do this without having a popup dialog asking me what Product ID I want. I want to be able to eliminate this step and go straight into the report with the currently displayed information.

2) Now once that is accomplished how do I have the report ONLY display and print the last 30 chronological entries by date...NOT the last 30 entered, and sort them in ascending order?

I think i am going gray over here trying to figure it out.

Currently this is the coding that i have:

Private Sub GoToDataCompReport_Click()
On Error GoTo Err_GoToCompReport_Click
DoCmd.OpenReport "rptDataComp", acPreview, , "[MixID]=[Forms]![frmDataComp]![MixID]"

Exit Sub

MsgBox Err.Description
Resume Exit_GoToDataCompReport_Click

End Sub

Sorry, I'm sure this sort of thing has been done to death but I've spent all morning looking up solutions and nothing has worked so far

I can cope with access at a fairly basic level - I can design tables and forms, run queries and reports. For the most part, my database works, but I'm now trying to exceed the limits of my knowledge! I've been asked by work to put a database together to keep client records.

My table contains a list of clients and their details. I have a query from it which is just called "Application Query". I've set up a form with a comman button that runs a report based on this query, and pulls up EVERY client. Which is fine, and it works.

What I want is to be able to filter by client. I know how to do this using the macro builder and [Enter Client] in order to enter the name, but the problem I'm having is that some of the spellings of names are unconventional, and if there is a spelling error, it brings up a blank report. So, what I want is a combo box with a list of clients, so that users can just click the client name and click a command button to bring up the report for that specific person.

On my form, I have the combo box (cboClient) which is linked to the query. It brings up a list of all of the clients. But I can't seem to make my command button (cmdOpen) use the selection in the combo box and I'm either getting a full report or the headings with no data. My report is called repApplication Query.

I don't really understand coding and I've been looking for a basic tutorial, but most of the information I've found has been far more complex than what I need (i.e. using multiple entries in a list box, or more than one combo box).

My code for the command button is currently:

Private Sub cmdOPen_Click()
DoCmd.OpenReport "repApplicationQuery", acViewPreview, , , , Nz(Me!Client, "")
End Sub

Which just opens the full report with everyone's details in it. Any suggestions or a link to a basic tutorial would be appreciated.


I am having trouble opening a report from a command within a subform.

I have a main form with a control source in which a navigation panel no the main form dictates what opens in the control source i.e. subforms.
This all works great but one action I require is within this control source is a subform with a command button to open a report. But it wont open. However it will open when I just view that subform as a form and not in the control source.

Any ideas much appreciated thanks.

Dear All

On a form I have a command button that on click opens a report, based on the selection made in a combo box on the same form.

I have used following code. It does open the report but does not show any data. Can somebody advice please.

Private Sub CmdVin_Click()
Dim stDocName As String
Dim vinno As String
vinno = "Forms![New form Sample]!Combo100"
stDocName = "R_VIN nos summary"

DoCmd.OpenReport stDocName, acPreview, , "[CUSTOMER] = '" & vinno & "'"

End Sub


I am new to access and have learned a great deal from this site but there is one tiny problem I have that I just can't seem to lick. I know enough about VB to get my self in trouble but I can't figure out how to get a report made from a form.

My goal is to have forms for purchase orders, fax covers, quotes, and the like that are generated from a form. The purpose is to track what we send out. I have the tables and form junk figured out but I need to make a command button to open a report which displays data in Justified view (for obvious reason). I have some code but it's not working. I'm sure it's variable types, I do a little C++ but even then I have a cheat sheet.

	Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

    Dim stDocName As String
    Dim stRefNum As String

    stDocName = "tblFax"
    stRefNum = "[ReffNumber]=" & "'" & Me![ReffNumber ] & "'"
    DoCmd.OpenReport stDocName, acPreview, , stRefNum

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Preview_Click
End Sub

The idea is to open the report from the current autonumber value displayed on the form. It's in the table too I just don't know how to make the forms appear filtered. The database is used by folks who don't understand much about computers and it must be point and click so I'm doing it all in the code. Thanks in advance for any input.

Not finding an answer? Try a Google search.