Generating Report from Form with Options

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
You can open the report and get the same data as on the form if you use the
Primary KEY that is in the form and matches the report:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]=" & Me!YourPKOnForm

And if your PK is text (heaven forbid):

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]='" & Me!YourPKOnForm & "'"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
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.
Click to expand...
 
Thank you. This works fine for the report picking up the main forms data.
However, my dilema is I want the report to pick up the list of docs in the
combo box. Can this be done? If so how? I have main form. On main form I
have an option group, of 3 choices/categories. My first combo box 1 lists
names of category to choose from. Comb Box 2 lists docs. This is the list I
want my report to pick up (combox box 2). I 've tried everything I can.
Feel that it should be easy enough but don't know how. I hope you can help.
Thank you in advance for any help you can provide.

boblarson said:
You can open the report and get the same data as on the form if you use the
Primary KEY that is in the form and matches the report:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]=" & Me!YourPKOnForm

And if your PK is text (heaven forbid):

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]='" & Me!YourPKOnForm & "'"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
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.
Click to expand...
Click to expand...
 
I'm not sure I get what you're saying. Are you wanting the report based on
the ONE combo box then just substitute the correct things:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
[YourFieldInReport]=" & Me!YourComboBoxOnForm

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
Thank you. This works fine for the report picking up the main forms data.
However, my dilema is I want the report to pick up the list of docs in the
combo box. Can this be done? If so how? I have main form. On main form I
have an option group, of 3 choices/categories. My first combo box 1 lists
names of category to choose from. Comb Box 2 lists docs. This is the list I
want my report to pick up (combox box 2). I 've tried everything I can.
Feel that it should be easy enough but don't know how. I hope you can help.
Thank you in advance for any help you can provide.

boblarson said:
You can open the report and get the same data as on the form if you use the
Primary KEY that is in the form and matches the report:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]=" & Me!YourPKOnForm

And if your PK is text (heaven forbid):

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]='" & Me!YourPKOnForm & "'"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for responding. Yes I want the report based on the combo Box 2.
Combo Box 2 changes accoridng to what is picked in the option box and combo
box 1. How do I write the code? Combo 2 lists docs according to County,
City, Company.

The "[YourfieldinReport]" can be 3 different fields. County, City or
Company.
thank you in advance for any help.


boblarson said:
I'm not sure I get what you're saying. Are you wanting the report based on
the ONE combo box then just substitute the correct things:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
[YourFieldInReport]=" & Me!YourComboBoxOnForm

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
Thank you. This works fine for the report picking up the main forms data.
However, my dilema is I want the report to pick up the list of docs in the
combo box. Can this be done? If so how? I have main form. On main form I
have an option group, of 3 choices/categories. My first combo box 1 lists
names of category to choose from. Comb Box 2 lists docs. This is the list I
want my report to pick up (combox box 2). I 've tried everything I can.
Feel that it should be easy enough but don't know how. I hope you can help.
Thank you in advance for any help you can provide.

boblarson said:
You can open the report and get the same data as on the form if you use the
Primary KEY that is in the form and matches the report:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]=" & Me!YourPKOnForm

And if your PK is text (heaven forbid):

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]='" & Me!YourPKOnForm & "'"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


:

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.
Click to expand...
Click to expand...
Click to expand...
 
You would substitute the field name on the report that is going to match up
with the data in the combo box.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
Thank you for responding. Yes I want the report based on the combo Box 2.
Combo Box 2 changes accoridng to what is picked in the option box and combo
box 1. How do I write the code? Combo 2 lists docs according to County,
City, Company.

The "[YourfieldinReport]" can be 3 different fields. County, City or
Company.
thank you in advance for any help.


boblarson said:
I'm not sure I get what you're saying. Are you wanting the report based on
the ONE combo box then just substitute the correct things:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
[YourFieldInReport]=" & Me!YourComboBoxOnForm

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


TotallyConfused said:
Thank you. This works fine for the report picking up the main forms data.
However, my dilema is I want the report to pick up the list of docs in the
combo box. Can this be done? If so how? I have main form. On main form I
have an option group, of 3 choices/categories. My first combo box 1 lists
names of category to choose from. Comb Box 2 lists docs. This is the list I
want my report to pick up (combox box 2). I 've tried everything I can.
Feel that it should be easy enough but don't know how. I hope you can help.
Thank you in advance for any help you can provide.

:

You can open the report and get the same data as on the form if you use the
Primary KEY that is in the form and matches the report:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]=" & Me!YourPKOnForm

And if your PK is text (heaven forbid):

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourPKFieldInReport]='" & Me!YourPKOnForm & "'"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box /Row Source Help 8
Need help with Combo Box? 15
Display Count of records from subform on Main form 7
Combo Box Question 2
Removing OrderBy from Tables and Queries in VBA 2
How to create a form to print a report from selected data? 1
Report Print preview - Need Help Please 3
Form Controls 2

Back
Top