By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Filter Report Data thru Combobox Selection

100+
P: 101
Hi,
I have project in MS Access.In that I have one form in which there is one combobox .I want to know when I select an item from that combobox and click on submit button then it should open a report which filter data from table.So i only want specific data from table in report based on selection of item from combobox in a form.

Can anyone help?
Thanks.
Dec 4 '07 #1

✓ answered by ADezii

Try:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "ProductionReport", acViewPreview, , "[palletno]=" & Me![cmbpalletno], acWindowNormal

Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,627
Hi,
I have project in MS Access.In that I have one form in which there is one combobox .I want to know when I select an item from that combobox and click on submit button then it should open a report which filter data from table.So i only want specific data from table in report based on selection of item from combobox in a form.

Can anyone help?
Thanks.
We need much more specific information than this, such as:
  1. Name of the Combo Box?
  2. Name of the Report?
  3. Record Source for the Report (Table Name)?
  4. Fields in the Combo Box?
  5. Field in Combo Box that will Filter Report data?
  6. Is this above Field Text or Numeric?
  7. Any other information you may think is relevant?
Dec 4 '07 #2

100+
P: 101
We need much more specific information than this, such as:
  1. Name of the Combo Box?
  2. Name of the Report?
  3. Record Source for the Report (Table Name)?
  4. Fields in the Combo Box?
  5. Field in Combo Box that will Filter Report data?
  6. Is this above Field Text or Numeric?
  7. Any other information you may think is relevant?
In form name of combobox is "cmbpalletno"
name of report is "ProductionReport"and record source is table "PRODUCTION"
in combobox I select field("palletno") of table("PRODUCTION") using wizard.
now I want to select palletno from combobox
all fields are of type Text

example


PalletNo CuastomerCode Qtyonhand Shippeddate status

1 Sears 1060 11/12/2007 SHIPPED
2 MACY 1600 11/01/2007 SHIPPED
3 OLD NAVY 1000 12/02/2007 SHIPPED
4 DKNY 500 12/03/2007 TO BE SHIPPED

now in my form I have combobox which have item list.

Sears
MACY
OLD NAVY
DKNY

when I select "MACY" from combobox and click on button(btnsubmit) then it should open report in which it will show

Customer Details
---------------------------------------------------------------------------------------------------------
PalletNo CuastomerCode Qtyonhand Shippeddate status
2 MACY 1600 11/01/2007 SHIPPED

if i select "Sears" from combobox then it will show me

Customer Details
---------------------------------------------------------------------------------------------------------
PalletNo CuastomerCode Qtyonhand Shippeddate status
1 Sears 1060 11/12/2007 SHIPPED


that's it.
I think now u can understand very easily.don't worry abt names.just give me solution using anyname feasible for u.
I willl change it in mine code.

Thanks for ur reply
Dec 4 '07 #3

ADezii
Expert 5K+
P: 8,627
Try:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "ProductionReport", acViewPreview, , "[palletno]=" & Me![cmbpalletno], acWindowNormal
Dec 5 '07 #4

100+
P: 101
Its working man.
Thank You buddy for ur help.
Dec 6 '07 #5

ADezii
Expert 5K+
P: 8,627
Its working man.
Thank You buddy for ur help.
You are quite welcome.
Dec 7 '07 #6

P: 6
Okay how do you do this for two combo boxes?
Mar 4 '08 #7

100+
P: 101
DoCmd.OpenReport "ReportName", acViewPreview, , "TableName.ColumnName=' " & Me![FieldName] & " ' ", acWindowNormal
or instead of me u can use FormName.

DoCmd.OpenReport "ReportName", acViewPreview, , "TableName.ColumnName=' " & [Forms]![FormName]![ComboBoxName] & " ' ", acWindowNormal

This one I used in my project.

DoCmd.OpenReport "ProductionReport", acViewPreview, , "(QCDatasheetForBottles.WONo=[Forms]![QCDatasheetForBottles13]![WONo]) and (QCDatasheetForBottles.ReportDate=[Forms]![QCDatasheetForBottles13]![ReportDate])"
Mar 4 '08 #8

P: 6
I am a little confused with your posting.

My 1st combo box is named: titleName
second combo box is named: artistName
the report is named: Comic_List

titleName is in tblTitle
artistname is in tblArtist

Both have Primary keys:

Title_ID
Artist_ID
Mar 4 '08 #9

P: 6
Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, , "(tblTitle.TitleName=[Forms]![Comic_List]![titleName]) and (tblArtist.artistname=[Forms]![Comic_List]![artistName])"

End Sub
-----------------------------------------------------------------------------------------------------------------------
I input this code into the form and it works BUT first a msg box comes up asking for input of titleName and then a second msg box asking for artistName. How do i open the report with out the msg boxes popping up and it just accepting it form the combo boxes?
Mar 4 '08 #10

100+
P: 101
Problem in ur case, caused bcz both comboboxes' values are fetched from two different tables.

Now there are two options for U.
First Case

U have to use subreport in ur main report.

For that U have to set the FirstTableName(tblTitle) as RecordSource of Main Report and set SecondTableName(tblArtist) as RecordSource of SubReport.Also in SubReport U have to set the Foreign Key(Common Field in Both Tables, may be in ur case NameofBook or something as I think bcz u didn't mention it)as Link Child Fields and Link Master Fields properties.and then U have to only do this

Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, , "(tblTitle.TitleName=[Forms]![Comic_List]![titleName])"

Second Case

In ur Form When U select the value from ComboBox(artistName) then in its AfterUpdate event fetch all the values(Use DLOOKUP for fetching values) from table(tblArtist), u want in report, and set them in textboxes in ur Form.
Then in Ur Report(no SubReport in this case) set the Record Source of TextBox as =Forms!Comic_List!TextBoxName for each field u want.
And then U have to only do this

Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, , "(tblTitle.TitleName=[Forms]![Comic_List]![titleName])"


I suggest to use First Case bcz its the proper way to do it.
I hope it will help u.
Mar 5 '08 #11

P: 16
@ADezii
ok but what if i have two tables for example : the combobox is from table1 and the report i want to open is from table2.i tried that code u wrote. but amsgbox appeares before the report shows asks me to enter aknown value ofcourse i don't know what to enter so i just press ok then the report shows up but with out data so can u help?

report i want to open is called(managers) from table(personaldata)
the combobox is from table (specialization)

the selection that i want to click to open the managers report is( managersjob)
thnx XD
Oct 11 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.