DropDown Box to Select Report Criteria

[kentwood]

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??

 

[WayneRyan]

K,

If the report is based on a query, the query can reference the
combo box on your form in its criteria:

=Forms![YourForm]![YourCombo]

From your description, I don't think the report is based on a
table, it it was you could:

DoCmd.OpenReport "YourReport",,,"[Employee] = '" & Me.YourCombo & "'"

Wayne

 

[kentwood]

my report is based on a query and your response was just enough to get me on track and get the result that I wanted. Thanks very much!

 

[kentwood]

I am finally getting back to finalizing my project and realize it is not working quite right, plus my user has an additional request. Currently, the users clicks the Report Name on the switchboard where the "on open" event opens a criteria form. The dropdown box with employee names appears and it has a "Preview Report" button. Below is the code that I have written:

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 & """"

DoCmd.OpenReport stDocName, acPreview, , StrWhere

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

Problem: When I select an employee, and click the "preview report" button, the Report does appear but has no data.

Additional Request: My user would also like to set 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.

Hope this makes sense.

 

[WayneRyan]

K,

I think that you have a problem with your quotes on the
DoCmd.OpenReport. Need to solve that first.

The dates should be added to the form where the user enters
the employees name.

You have two ways to proceed.

In the query, use the Criteria line for the CSR and
Date field as follows:

Under the CSR, in the criteria line put:
=Forms![YourSearch]![cboCSR]

Under the appropriate date field, in the criteria line put:

Between Forms![YourSearch]![Date1] And Forms![YourSearch]![Date2]

Using this method, you change to:

DoCmd.OpenReport "701/901", acPreview (No criteria)!

The other method, and I won't suggest is (still add the two
date fields to the form with the combo), but change the
DoCmd to:

DoCmd.OpenForm "701/901",,, (Put the three conditions here)


Wayne

 

[kentwood]

Thanks for your reply. There are some improvements, but it is still not correct. I am sure it is not for lack of what you have told me but simply my ignorance with Visual Basic etc. Each project I learn just that much more.

I have worked hard to get to this point and thought perhaps I would attach a very skeleton copy of my DB (I removed all queries, reports etc that do not apply), leaving my form (SelectEmp) that is fed by query (SelectEmp). the report is 701/901 and fed by the query of the same name.

Only 2 records remain for purposes of this test copy. Your review of what I am doing wrong would be greatly appreciated. You will need to hold the shift key down when you open it.

 

[WayneRyan]

Kent,

Not bulletproof, but play with it.

Wayne

 

[kentwood]

Wow! As I check the properties and the event notes, it is quite a bit different than my own. It works really slick. I'm going to take some time tomorrow to understand each of the changes that you made.

I see that it will automatically pick the 1st date and the last date recorded specific to that individual. Therefore, if a person only wanted to select a certain date range, they would not be able to do it.....correct? I say that because if my user wants to attach this information to a performance review between date 1 and date 2, they would only want those specific dates.

Your help is awsome.

 

[WayneRyan]

Kent,

I just put the dates there, because for any person it is nice to
know their earliest and latest dates. You can type over the
values, if you specified a "wider" range (same report), or you
could narrow it down (to eliminate projects).

Wayne

 

[kentwood]

Very clever you are. There really were a lot of changes in the code as well as criteria formating on the queries. My database is Access97 however I do have a laptop that has both Access97 and Access2000 on it.

I am trying to convert (copy/paste etc) the information into my main database (Access97) from the shell you were working on, and it is a bit difficult.

Any chance you can send the same file in the 97 format? Otherwise I will continue to chop away at this and hope that it works the same way.

 

[WayneRyan]

Kent,

I can go find it, but can't you just do a:

Tools --> Database Utilities --> Convert Database

and convert what I sent to A97?

If you can't, tell me the name of the database and I'll go find
it.

Wayne

 

[kentwood]

I did it. For some strange reason I did not think you could go back from Access2000 to 97. Someone mentioned this to me, but I am glad to know that it is not the case.

Thanks again.