Filter Report Data thru Combobox Selection

[billa856]

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.

[ADezii]

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?

[billa856]

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 "cmbpalletn o"
name of report is "ProductionRepo rt"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(btnsubmi t) 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

[ADezii]

Try:
[CODE=vb]DoCmd.OpenRepor t "ProductionRepo rt", acViewPreview, , "[palletno]=" & Me![cmbpalletno], acWindowNormal[/CODE]

[billa856]

Its working man.
Thank You buddy for ur help.

[ADezii]

Its working man.
Thank You buddy for ur help.

You are quite welcome.

[jvan2008]

Okay how do you do this for two combo boxes?

[billa856]

DoCmd.OpenRepor t "ReportName ", acViewPreview, , "TableName.Colu mnName=' " & Me![FieldName] & " ' ", acWindowNormal

or instead of me u can use FormName.

DoCmd.OpenRepor t "ReportName ", acViewPreview, , "TableName.Colu mnName=' " & [Forms]![FormName]![ComboBoxName] & " ' ", acWindowNormal

This one I used in my project.

DoCmd.OpenRepor t "ProductionRepo rt", acViewPreview, , "(QCDatasheetFo rBottles.WONo=[Forms]![QCDatasheetForB ottles13]![WONo]) and (QCDatasheetFor Bottles.ReportD ate=[Forms]![QCDatasheetForB ottles13]![ReportDate])"

[jvan2008]

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

[jvan2008]

Private Sub Submit_Click()
DoCmd.OpenRepor t "Comic_List ", acViewPreview, , "(tblTitle.Titl eName=[Forms]![Comic_List]![titleName]) and (tblArtist.arti stname=[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?

[billa856]

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.OpenRepor t "Comic_List ", acViewPreview, , "(tblTitle.Titl eName=[Forms]![Comic_List]![titleName])"

Second Case

In ur Form When U select the value from ComboBox(artist Name) 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_Li st!TextBoxName for each field u want.
And then U have to only do this

Private Sub Submit_Click()
DoCmd.OpenRepor t "Comic_List ", acViewPreview, , "(tblTitle.Titl eName=[Forms]![Comic_List]![titleName])"

I suggest to use First Case bcz its the proper way to do it.
I hope it will help u.

[kurai hikari]

what about 2 different tables?

Try:
[CODE=vb]DoCmd.OpenRepor t "ProductionRepo rt", acViewPreview, , "[palletno]=" & Me![cmbpalletno], acWindowNormal[/CODE]

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