Generating Report from Form with Options

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I had posted this question a few days ago in the Report section and have not
seen an answer. Maybe it is the wrong section.

I have also reviewed everything in this forum to see if I can find something
similar and have not seen anything. This forum helped me with a form and
combo boxes. However, I need to take this a step further and I would
appreciate any help with how to generate a report from a form's combo box
(list of docs) box. I have an unbound report with the same fields as in
the list of Doc in Combo2. Not sure if it can be done but I would like the
report to be able to pick up the lists of docs generated by the option
choices and combo 1. Without having to create a lot of individual reports.

This is the code for the optionGroup in my form. Form is based on an Option
Group of 3 choices depending on choice my 1st combo box lists the names of
the category. My 2nd combo box lists doc in category. I want from a command
button to generate a report of the Docs listed in the 2nd combo box.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String 'combo1 (County/City/Group)
Dim strDOCListRowSource As String 'combo2 (DOC List)

strDOCListRowSource = "SELECT DISTINCTRow
[tblOFCTRKG].[ID],[tblOFCTRKG].[DOC CNTY],[tblOFCTRKG].[DOC
CITY],[tblOFCTRKG].[Company],[tblOFCTRKG].[DOC ID],[tblOFCTRKG].[DOC NAME],
[tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[St]FROM [tblOFCTRKG] GROUP BY
[tblOFCTRKG].[DOC CNTY],[tblOFCTRKG].[DOC
CITY],[tblOFCTRKG].[Company],[tblOFCTRKG].[DOC ID],[tblOFCTRKG].[DOC
NAME],[tblOFCTRKG].[DOC PH],[tblOFCTRKG].[DOC FX],[tblOFCTRKG].[DOC
ADD],[tblOFCTRKG].[DOC ADD2],[tblOFCTRKG].[DOC
CITY],[tblOFCTRKG].[St],[tblOFCTRKG].[Company],[tblOFCTRKG].[ID]"

Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [DOC CNTY] FROM [qry Counties];"
strDOCListRowSource = strDOCListRowSource & "
HAVING((([tblOFCTRKG].[DOC CNTY])= [forms]![frm DOC Tracking]![Search by]))"
Case 2 'City
strRowSource = "SELECT [DOC City] FROM [qry Cities];"
strDOCListRowSource = strDOCListRowSource & "
HAVING((([tblOFCTRKG].[DOC City])= [forms]![frm DOC Tracking]![Searchby]))"
Case 3 'Company
strRowSource = "SELECT [Copy Company] FROM [qry Groups];"
strDOCListRowSource = strDOCListRowSource & " HAVING
((([tblOFCTRKG].[Company])= [forms]![frm DOC Tracking]![Searchby]))"
End Select

'county, city or company combo box
Me.Searchby.RowSource = strRowSource

'doctor combo box
Me.DOCList.RowSource = strDOCListRowSource & " ORDER BY
[tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[Company],[tblOFCTRKG].[DOC PH];"

End Sub


I can get the report to print view by using the DoCmd.Open Report but it
either comes up null or lists everything. I have also tried DoCmd.Open
Report, "report name", acPreView, Me.Filter . I can't seem to get the
different
options. I would like the report to mirror the lists on the form. Thank you
very much.



.

Relevant Pages

  • RE: Should be simple but...
    ... Staff directory- I need it to list last name, first name, phone location ... I did as you suggested and created a subreport. ... Used the wizard to link the srpt to the main report. ... I have one location that lists 6 staff ...
    (microsoft.public.access.reports)
  • Re: I need a form to select the items to be presented in the repor
    ... Dim strCriteria As String ... change "ReportName" to the name of your report. ... In the report query remove the "Where" clause. ... Instead of a query asking for a site, I want a form which lists all the ...
    (microsoft.public.access.formscoding)
  • RE: create any report
    ... "Duane Hookom" wrote: ... sign/operator, and criteria. ... fields they want to display in a published report. ... Lists ...
    (microsoft.public.access.reports)
  • RE: Weird Problem with a Subreport
    ... If you don't want to show the header on the first group, ... Open the report in design view. ... I have a header of shutdown in front of the whole list but the two lists ... The Subreport will only show as many rows as will fit in the area I delegated ...
    (microsoft.public.access.reports)
  • RE: Generating Report from Form with Options
    ... This works fine for the report picking up the main forms data. ... Comb Box 2 lists docs. ... I have also reviewed everything in this forum to see if I can find something ... 'county, ...
    (microsoft.public.access.forms)