Trying to embed a command button to generate a report

[Action82]

I am trying to place a command button in my form so that I can let other users click on the button to bring them directly to the report so they can skip the process of querying.

I am trying to build the code in VBA but I am having alot of trouble. I've tried this line that sort of works, but not was automated as I would like:

DoCmd.OpenReport "Summary of Findings Report", acViewPreview, , "[lastname]= [Last Name] and [date] = [Date ##/##/####]"

Clients come in and then a report is generated for them. Because clients can come more than once, the criteria I set for running the report are their last names and date, so that it will eliminate the chance of printing out the report from an earlier visit. With the current line, a box pops up and the user still has to type in the last name and date, but I would like to skip it. I tried using the keyword Me.dastname and Me.date to try to get Access to plug in the last name and date automatically but I can't seem to get it to work. Is there any way for me to get a report with the two criteria that I have with just the simple click?

Thank you in advance for your help!:beerchug:

[CreganTur]

Welcome to the forum- always good to have new members.

Before I offer a solution, I need you to clarify something: On your form, do you have objects (textboxes, comboboxes, etc.) for the Last Name and Date? If so, what are their names? If not, then were are [Last Name] and [Date ##/##/####] coming from?

[Action82]

My form has only textboxes for all fields. The name of the fields are [lastname] and [date]. The [Last Name] and [Date ##/##/####] names are just made up so that a box pops up with a text field and is labled with the [Last Name] and [Date ##/##/####]. It seems like whatever I label as the name will pop up in the box. I just labeled them as [Last Name] and [Date ##/##/####] to remind the user what to type in.

[CreganTur]

Thanks for the clarification.

I'm going to rewrite the WHERE condition of the OpenReport method so that it will pull the last name and date form the textboxes on your form. It will use those values to open the report. Try this code:

DoCmd.OpenReport "Summary of Findings Report", acViewPreview, _
, "[lastname]= '" & Me.lastname & "' and [date] = #" & Me.date & "#"


The underscore character allows you to split a line. I did it so it won't stretch the window here on the forum, but it will also work in the VBA IDE.

Me is a kayword that refers to the form that the code is behind. What the above code does is open your report WHERE the [lastname] and [date] are equal to the values in the lastname and date textboxes on your form.

The WHERE condition that is part of the OpenForm method is written exactly like a WHERE statement in a SQL string, just without the WHERE keyword. Whenever you use VBA variables as a part of a SQL string, you must wrap them with symbols that tell SQL what data type they are working with. A single quote is for text, and a pound sign (#) is for date. If you're working with a number, then no symbol is used.

Try the above code and see if it works.

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: When you post code, be sure to wrap it in VBA tags (click the green VBA button). This will format it according to VBIDE and make it easier to read.

Also, if your issue is resolved you can mark your thread as solved by clicking Thread Tools ->Mark as solved at the top of this window.

[Action82]

Wow!!! That was exactly what I needed! You are a godsend!:cool: Just a quick question, can you explain a bit more about the syntax of how that script works? Why is there a & before and after the Me.* and why do the quotes go outside of the & symbols? I'm used to the quotation marks and then the & or the AND on the outside when writing the code in Access Reports.

Thanks again for your help. I have been banging my head on the wall over this for a while now!

[Action82]

Uh no.... ran into a problem. When I open a new record and fill out the fields, the data doesn't get saved until I close out of the form. When I click on the button, the report comes out with errors because I technically haven't saved the data to Access yet. However, if I close out of the form, I can no longer click on the button to generate the report.

Perhaps if I can somehow save the record without closing the form, this might still work, otherwise any ideas?

[CreganTur]

Just a quick question, can you explain a bit more about the syntax of how that script works? Why is there a & before and after the Me.* and why do the quotes go outside of the & symbols?

Sure. I'll try to be coherent :old:

The ampersand (&) is the concatenation symbol for VB and VBA (VBA is a superset of Visual Basic, so if you know VBA you kind of know VB- syntax is the major difference). Concatenation means to link or join together. In programming terms, it means you are taking multiple pieces and joining them together in a whole.

For example, take this code and run it in your Immediate Window:
MsgBox "Hello" & " " & "world!"
I am taking three different items: the word 'Hello', a space, and the word 'world!', and concatenating them by using &. Even though these are three separate items in code, when you run the code a message box appears showing: "Hello world!". This is because VBA knows that the parts are supposed to be shown as a whole.

Here's another example. Place it in a new module, put your cursor anywhere within the code, and press F5 to run it:
Sub ConcatTest()

Dim strInput As String

strInput = InputBox("Please write something: ")

MsgBox "You entered: " & strInput

End Sub

strInput is a variable, which will hold whatever the User types into the input box. Using concatenation, we add the variable to the MsgBox's text, so that whatever input the user provided will be shown in the message box.

Now we'll take a look at the code for your report.

Lets say that your user put the value 'Smith' in the lastname textbox, and '1/19/08' in the date textbox. If we put these values directly into the code, it would look like this:


DoCmd.OpenReport "Summary of Findings Report", acViewPreview, _
, "[lastname]= 'Smith' and [date] = #1/19/08#"

The above code will work, opening the report so it shows a report for Smith on 1/19/08.

Here is an incorrect way to reference the textbox values in your code:


DoCmd.OpenReport "Summary of Findings Report", acViewPreview, _
, "[lastname]= 'Me.lastname' and [date] = #Me.date#"

The above code is incorrect because SQL literally thinks that you want to look for the last name 'Me.lastname', and the date 'Me.date'. Obviously, this is not what you want to do! The reason SQL thinks this is because the objects are being referenced within the SQL string. This means that it is using the object references as literal values, instead of pulling the value of the object- it thinks the last name is 'Me.lastname', when the user clearly entered 'Smith' in the lastname textbox.

So, to make it correct, we have to break the object names out of the string so that their values are referneced. The correct code, as we know, looks like this:


DoCmd.OpenReport "Summary of Findings Report", acViewPreview, _
, "[lastname]= '" & Me.lastname & "' and [date] = #" & Me.date & "#"

The WHERE condition is expecting a single string value for the parameter. We have to break the object names out of the string. So, we close off the string by using double quotes, and then use & to concatenate the object name to the string. When this line of code runs, SQL will see all of it as a continuous string, exactly how the first two examples showed a continuous string in their message boxes.

I really hope this answered your question. If it didn't, let me know what I need to clarify.

[CreganTur]

Perhaps if I can somehow save the record without closing the form

Try using this code- it should be the very first thing to happen when your user clicks the button to open the report:
DoCmd.RunCmd acCmdSaveRecord

[Action82]

Yup that did the trick! Just for your reference, the command was:

DoCmd.RunCommand acCmdSaveRecord

The RunCmd gave an error, so a quick search of the commands yielded the RunCommand.

Thanks again!