Custom Report based on selection (1 Viewer)

[ericryd]

Hi-

I'm looking for assistance or to find out if it is possible to create a report that when opened first opens a list of names. Once a name is selected, the actual report opens up with that persons criteria.

Any ideas?

 

[pbaldy]

Sure; the simplest would be to create a form with your list, and a button to open the report. This technique also works for OpenReport:

http://www.baldyweb.com/wherecondition.htm

 

[ericryd]

Sure; the simplest would be to create a form with your list, and a button to open the report. This technique also works for OpenReport:

http://www.baldyweb.com/wherecondition.htm

Thanks for the reponse. I'm a bit confused though as this is talking about using a second form. Will this work for opening a report too?

 

[pbaldy]

Yes, it does:

This technique also works for OpenReport

 

[ericryd]

Ok...i'm starting to catch on.

I understand how it opens a report and it is passing the selected field....but i'm not understanding what I need to put in the report itself to know this information. Does that make sense?

 

[pbaldy]

The report would be based on whatever table/query contains the data you want to report on. With this setup, if you just open the report from the database window, it would show all records. Opening it with a wherecondition limits it to the person you choose.

 

[ericryd]

ok, now it is making sense. I think i'm just about there, but i'm getting an error when it tries to open the report.

Here is the code in the form:

Private Sub EmpInd_LaunchReport_Click()
On Error GoTo Err_EmpInd_LaunchReport_Click
Dim stDocName As String
stDocName = "All Techs Occurrences"
DoCmd.OpenReport stDocName, , , "Employee Name = '" & Me.EmpOccList & "'", acPreview
Exit_EmpInd_LaunchReport_Click:
Exit Sub
Err_EmpInd_LaunchReport_Click:
MsgBox Err.Description
Resume Exit_EmpInd_LaunchReport_Click

End Sub

Then when I run everything, I get the error: "Syntax error (missing operator) in query expression '(Employee Name = 'name')'.

any thoughts?

 

[pbaldy]

That part looks okay, but the preview argument is out of place. Try this:

DoCmd.OpenReport stDocName, acViewPreview, , "Employee Name = '" & Me.EmpOccList & "'"

 

[pbaldy]

I'm sorry, just noticed the inadvisable space. Try this

DoCmd.OpenReport stDocName, acViewPreview, , "[Employee Name] = '" & Me.EmpOccList & "'"

 

[ericryd]

same thing.

btw EmpOccList is the combo list where you select the name.

 

[ericryd]

ahhh....success!!!! Thank you very much!!!!!

 

[pbaldy]

Excellent! You might consider getting rid of the spaces in your field name(s). They're more trouble than they're worth in the long run. You'll have to use those brackets a lot.

 

[ericryd]

Gotcha...That's something I will do for sure in the future.

One final question, to gather the options for the data combo box, I am having to put the names in manually for it to work. I can't reference another table that just lists all employee names. When I do that, it just comes up blank.

Any thoughts?

 

[pbaldy]

You should be able to use a query (a saved query or SELECT... right in the row source). If you're getting blanks, double check the column count and column widths properties of the combo (and the bound column). You might try building a combo with the wizard, which should set all that up for you.

 

[ericryd]

hmm....Interesting. I just made a new query that just copies all of the info from the table and now it works.

Probably a good idea to avoid ever hitting the table directly and only hit queries?

Thanks again.