T
TotallyConfused
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.
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.