Filter Report by combo box selection

[pswilliams0]

Hi,
I'm currently in the midst of my first foray into the access world. I'm making big strides, but also trying to extensively customize the project...

What I have right now is a command bar that has buttons that open up an variety of forms and reports. The data I am dealing with are reports that need to be written. These reports get assigned to an Author and then have a due date. I have a table of authors, and the main table of all the open reports that need to be written. I can run the query to display any open report. However, I would like to limit this same report output based on the author assigned to the report. So, an author could print out his current "open" reports.

To do this, I created a combo box of all the authors (about 20), then created a command button to open the report. Unfortunately, I keep getting syntax errors. Here is my VBA:
___________________________________________________
Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
Dim stDocName As String

MsgBox AuthorSelection

stDocName = "Open QN Items Report"
DoCmd.OpenReport stDocName, acPreview, , "QN Author= '" & Me.AuthorSelection & "'"

Exit_Command28_Click:
Exit Sub
Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

______________________________________________
So, I have all of the authors listed in the combo box (named AuthorSelection) as Last, First names (one Field Item in Author table). I think this might be my problem, but I'm not really sure how to fix it. These names display in many other reports and forms without a problem.

As you can see in the code, I threw in a MsgBox. From that I only ever get the last name from the combo box. So, as an example: I select "Smith, John" from the combo box, then click the button to run the code shown above. I would get a MsgBox that displays the AuthorSelection as "Smith" NOT "Smith, John"

The second problem is the Syntax Error I get after closing the MsgBox:
"Syntax Error (missing operator) in query or expression '(QN Author= 'Smith')'.

"QN Author" is the field in the report I'm trying to filter.

So there are two problems:
1. Why is the combo box only recognizing the last name (or text before the comma)
2. What is my syntax error? Is it that the recognized name doesn't match any of the fields in the report exactly? (Because the QN Author field has format "Last, First" ?

Thanks for any expertise/guidance you're able to offer!

 

[Isskint]

hi pswilliams0
The first thing i notice is that you have put the condition "QN Author= '" & Me.AuthorSelection & "'" in the filter argument of the openreport command and not the WhereCondition. Put an extra comma in before the condition.

The data for your combobox, is that queried from your Authors table or are you filling it another way? Is the format of Authors Name in the Authors table the same as your combobox eg Last, First? Another option for supplying the authors name to the report would be by using the OpenArgs argument in the openreport command. If you do this you would need to add the handling code to the reports on open code.

 

[Insane_ai]

If each author has a unique id associated with them, use that field in your combo box as the Bound Column and reference that ID in your filter. So if the rowsource = Select ID, (LastName & ", " & FirstName) As Name from Authors you would set the bound column to 1. For display purposes, I would set the column widths to 0;1.5 (or however long it needs to be to display the name). this allows you to have a unique reference fro teh code while providing a reference your user can relate to.

 

[pswilliams0]

hi pswilliams0
The first thing i notice is that you have put the condition "QN Author= '" & Me.AuthorSelection & "'" in the filter argument of the openreport command and not the WhereCondition. Put an extra comma in before the condition.

Hi Isskint - I checked my code and I have the correct number of commas according to the help dialog that comes up shows that it is actually in the WhereCondition.

The data for your combobox, is that queried from your Authors table or are you filling it another way? Is the format of Authors Name in the Authors table the same as your combobox eg Last, First? Another option for supplying the authors name to the report would be by using the OpenArgs argument in the openreport command. If you do this you would need to add the handling code to the reports on open code.

The Combo Box is populated from a query that accesses the "Authors" Table and just sorts the single field "Last, First" alphabetically. I'm not familiar with the OpenArgs argument, so I'll look into that.

 

[Isskint]

my bad, i did not see the first comma

I am not certain (and there are others here who will know) but i would say the use of a comma in a field name is just asking for trouble. I would surmise that when the where condition parses the expression you have supplied it encounters a comma where it is not expecting it.

 

[pswilliams0]

Hi Insane_ai - Each author does have a unique ID, but in this case, I'm not referencing the author table directly, I'm just trying to match up a field of text to another field of text. The query for the report doesn't reference the author ID, only the "Last Name, First Name" Field which is a text field.

If each author has a unique id associated with them, use that field in your combo box as the Bound Column and reference that ID in your filter.

Do you have an idea as to why the comma messes things up in getting the complete text from the combo box? Is there an easy work around? Like Say using an underscore or something like that?

Thanks for your input!

 

[pswilliams0]

No worries, it was a good thing to check. Yes, I'm also wondering if the comma is the culprit, but I've designed many other things around that field... Modifying it to parse separate Last and First Name fields together will be pushing my limited VBA abilities. I haven't been able to figure out the combobox in VBA yet...

 

[pswilliams0]

Insane_ai - You led me to the correct answer! Thanks so much!!! It turns out that my Authors query that was providing the data for the combo box had 2 columns (I designed it from the wizard) and the column providing data was the column that only had "Last Name"

Yes, the Authors form has an ID, Last Name, First Name, and Last,First name fields that are all entered manually. I'm realizing that I could make that last one automatic... In any event, once I deleted the first column for the combo box (Last Name Field from Query), the Msgbox showed the complete author name and the report ran flawlessly.

Thanks so much for your help!