ComboSelection Controls Criteria of Report 1

[kentwoodjean]

I have a program that Tracks the projects received within a department, who works on them, the time used and the costs associated. I now have a request from that area to adjust a report that will show a designated employees results. Normally, I would set the query/report criteria to ask for the NAME of the emp. to pull their results.

What they would like is a combo box that will provide a dropdown list of all employees where they can click the employee they want and subsequently receive the report specific to that employee.

I thought about using a button on the switchboard that would give me a form, add the combo box, select the employee, and click a button on the form to Preview the Report. If this can be done, it probably requires some type of Visual Basic, of which I am not very good at. Any suggestions??

 

[dhookom]

Add the combo box to the form and name it something like "cboEmployeeID". Then add the command button using the wizard and selecting the Report|Print Preview options. Give the command button a good name like "cmdReport" during the wizard. This will write the basic code for you.

Then right click the button and choose to build the code. Modify your wizard created code

'add these lines
Dim strWhere as String
strWhere = "[EmployeeID]=" & Me.cboEmployeeID
'modify the DoCmd.OpenReport to something like:
DoCmd.OpenReport stReport, acViewPreview, , strWhere

Duane
MS Access MVP

 

[kentwoodjean]

Added combo box to form (Name: cboCSR). Added command button selecting the Report (Preview Option) naming the button "cmdReport". Form is named SelectEmp, field is CSR on table that made combobox, and report name is 701/901.
I added the 2 lines you indicated and modified the "Do Command" but I get an error when I hit the preview button. The error says: "Method or datamatch not found". When I click OK on the error, Yellow highlighted is "Private Sub cmdReport_Click()" and highlighted in black is "Me.SelectEmp". Below is what the command looks like. Any thoughts on what I am doing wrong?


Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stDocName As String

stDocName = "701/901"
Dim StrWhere As String
StrWhere = "[CSR]=" & Me.SelectEmp

DoCmd.OpenReport stDocName, acViewPreview, , StrWhere

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

 

[dhookom]

Very close. The line:
StrWhere = "[CSR]=" & Me.SelectEmp
should be:
StrWhere = "[CSR]=" & Me.cboCSR
If CSR is a text field you need to use:
StrWhere = "[CSR]=""" & Me.cboCSR & """"

Good job...


Duane
MS Access MVP

 

[kentwoodjean]

Sorry I am just getting back to you but I was at the U of M football game yesterday. Also Sorry to be such a pain but when you are so close to your result, it drives you crazy when it doesn't work. You have been so helpful, I feel badly coming back. [CSR] is a text field, however if I format it as you suggested, I get an error "Complie Error Syntex Error". The entire line is in Red and it seems to only accept ="[CSR]=". With this format, I still get an error "Comple Error method or data member not found". It continues to highlight the .cboCSR. Any additional thoughts?

 

[dhookom]

You should make sure you have the correct number of quotes. There should always be an even number of quotes ([ and ( also). If your report has a text field named CSR and your combo box is named cboCSR, then the following should work.

StrWhere = "[CSR]=""" & Me.cboCSR & """"

I should have first asked if U of M is my alma mater prior to assisting you. Minnesota? Ski-U-Mah?

Duane
MS Access MVP

 

[kentwoodjean]

OK, we have it working now. I don't know why it would not work before. You do deserve a big star for all your help.

Just one other question. If I also want to have a criteria for date range (Start and End), is that something that is attached to my form with the combo field, or is that used as criteria on the query that built the report? Currently, it is on my query, hense the report. If I just run the report, it will ask me for my date range and display the appropriate records. If I use my form, select my employee and hit the "preview report" button, I am then prompted for my dates, but the report does not return any records.

By the way, it was University of Michigan......Go BLUE!!!

 

[dhookom]

I generally remove all criteria from report record sources except those that never change. To apply a date range filter, I would add two text boxes to the form "txtStart" and "txtEnd". Don't forget the comments....

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
Dim stDocName As String
stDocName = "701/901"
Dim StrWhere As String
StrWhere = "[CSR]=""" & Me.cboCSR & """"
' go Maroon and Gold
If Not IsNull(Me.txtStart) then
strWhere = strWhere & " AND [DateField] >=#" _
& Me.txtStart & "#"
End If
If Not IsNull(Me.txtEnd) then
strWhere = strWhere & &quot; AND [DateField] <=#&quot; _
& Me.txtEnd & &quot;#&quot;
End If
'Go Golden Gophers
DoCmd.OpenReport stDocName, acViewPreview, , StrWhere

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click
End Sub

Duane
MS Access MVP

 

[kentwoodjean]

Excellent!! You have been a tremendous help to me, and I have learned some things that will help me a great deal in the future.

Thanks so much...and with that, another star.