Generating Report from Form with Options

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.
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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
Need help with frm/subfrm TY 4
OpeningWord 1
Form Controls 2
Removing OrderBy from Tables and Queries in VBA 2
How to create a form to print a report from selected data? 1
Strange form problem? 3

Top