Report Print preview - Need Help Please

[Guest]

I hope someone can help me with this. I have tried many different ways to do
this and can't seem to get any results. Not sure if it can be done. So far
I am only able to generate the report with a list of everything (not
reflecting what was picked on the option box) and I can generate the report
with only one doctor (doctor listed in the main form - not what I want).

I have a form with an option group with 3 different choices. I have combo
box 1 with names of the categories from option group. Then I have combo box
2 that list docs. I have a command button that when I click on this button I
get a print preview of the list of docs in combo box 2 and I have the option
of printing.

Is this possible? If so how? Do I have different reports? Right now I
only have one report. It is an unbound report with the same fields as in
Combo Box 2.

Any help will be greatly appreciated.

 

[Ken Snell \(MVP\)]

See sample database here; it shows how you can build the WHERE clause for a
query's SQL string (which then is used as the RecordSource for a report)
based on values selected in various controls on a form:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

 

[Guest]

Thank you for responding. I have looked at your sample and yes that is what
I am working with. However, my db is much simpler. I am not sure I get it.
This is what I have come up with and I am still getting a printpreview of all
doc listed. It is not picking up what I have selected in the option box and
combo 1. Can you please help me through this? I am not savy with code.
This is what I narrowed it down to. I don't have a search button. Any help
will be greatly appreciated.

Private Sub cmdPrintPreview_Click()

Dim strSQL As String
Dim strWHERE As String
Dim strReportName As String

strReportName = "rpt DoctorList"
strWHERE = "[DocID] = " & Me.[DocID]


strSQL = "SELECT [tblDocTracking].[DocID], GROUP BY [tblDocTracking].[CNTY],
[tblDocTracking].[CITY],[tblDocTracking].[Company]"


DoCmd.OpenReport strReportName, acViewPreview, , strSQL = Me.[DocList]


End Sub

 

[Ken Snell \(MVP\)]

I apologize for the long delay in replying -- somehow I missed your post.

I assume that the report's RecordSource is the query that you posted (the
strSQL string)? If yes, you just need to pass the strWHERE string:

DoCmd.OpenReport strReportName, acViewPreview, , strWHERE


--

Ken Snell
<MS ACCESS MVP>

Thank you for responding. I have looked at your sample and yes that is
what
I am working with. However, my db is much simpler. I am not sure I get
it.
This is what I have come up with and I am still getting a printpreview of
all
doc listed. It is not picking up what I have selected in the option box
and
combo 1. Can you please help me through this? I am not savy with code.
This is what I narrowed it down to. I don't have a search button. Any
help
will be greatly appreciated.

Private Sub cmdPrintPreview_Click()

Dim strSQL As String
Dim strWHERE As String
Dim strReportName As String

strReportName = "rpt DoctorList"
strWHERE = "[DocID] = " & Me.[DocID]


strSQL = "SELECT [tblDocTracking].[DocID], GROUP BY
[tblDocTracking].[CNTY],
[tblDocTracking].[CITY],[tblDocTracking].[Company]"


DoCmd.OpenReport strReportName, acViewPreview, , strSQL = Me.[DocList]


End Sub

See sample database here; it shows how you can build the WHERE clause for
a
query's SQL string (which then is used as the RecordSource for a report)
based on values selected in various controls on a form:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm