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

filter report based on combobox seletion in forum

P: 18
what i want to do is basically have form where my selection in the combox make the diffrence with what the form reports.

this is what the underlying informaion is....

software is access 2007
i got a table where there is name of contacts and with status of away or available
then i got form...which has combo box with options AWAY and AVAILABLE
and also a command button.
then my report has selected colums from the contacts table.

so now what i need to know is basically what is the code to put in "on click" in command button that would cause the filteration to happen based on the selection in th combo box.
Aug 22 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,359
what i want to do is basically have form where my selection in the combox make the diffrence with what the form reports.

this is what the underlying informaion is....

software is access 2007
i got a table where there is name of contacts and with status of away or available
then i got form...which has combo box with options AWAY and AVAILABLE
and also a command button.
then my report has selected colums from the contacts table.

so now what i need to know is basically what is the code to put in "on click" in command button that would cause the filteration to happen based on the selection in th combo box.
The DoCmd.OpenReport function has a filter parameter.
Aug 22 '07 #2

P: 18
The DoCmd.OpenReport function has a filter parameter.

can you explain to me how i do that?
Aug 22 '07 #3

Rabbit
Expert Mod 10K+
P: 12,359
From the help files:

OpenForm Method
The OpenForm method carries out the OpenForm action in Visual Basic.

DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

FormName Required Variant. A string expression that's the valid name of a form in the current database. If you execute Visual Basic code containing the OpenForm method in a library database, Microsoft Access looks for the form with this name first in the library database, then in the current database.

View Optional AcFormView.

AcFormView can be one of these AcFormView constants.
acDesign
acFormDS
acFormPivotChart
acFormPivotTable
acNormal default. Opens the form in Form view.
acPreview
If you leave this argument blank, the default constant (acNormal) is assumed.


FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

DataMode Optional AcFormOpenDataMode. The data entry mode for the form. This applies only to forms opened in Form view or Datasheet view

AcFormOpenDataMode can be one of these AcFormOpenDataMode constants.
acFormAdd The user can add new records but can't edit existing records.
acFormEdit The user can edit existing records and add new records.
acFormPropertySettings default
acFormReadOnly The user can only view records.
If you leave this argument blank (the default constant, acFormPropertySettings, is assumed), Microsoft Access opens the form in the data mode set by the form's AllowEdits, AllowDeletions, AllowAdditions, and DataEntry properties.


WindowMode Optional AcWindowMode. The window mode in which the form opens.

AcWindowMode can be one of these AcWindowMode constants.
acDialog The form's Modal and PopUp properties are set to Yes.
acHidden The form is hidden.
acIcon The form opens minimized in the Windows taskbar.
acWindowNormal default The form is in the mode set by its properties
If you leave this argument blank, the default constant
(acWindowNormal) is assumed.


OpenArgs Optional Variant. A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

This argument is available only in Visual Basic.

Remarks
For more information on how the action and its arguments work, see the action topic.

The maximum length of the wherecondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Example
The following example opens the Employees form in Form view and displays only records with King in the LastName field. The displayed records can be edited, and new records can be added.

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"
Aug 23 '07 #4

P: 13
what i want to do is basically have form where my selection in the combox make the diffrence with what the form reports.

this is what the underlying informaion is....

software is access 2007
i got a table where there is name of contacts and with status of away or available
then i got form...which has combo box with options AWAY and AVAILABLE
and also a command button.
then my report has selected colums from the contacts table.

so now what i need to know is basically what is the code to put in "on click" in command button that would cause the filteration to happen based on the selection in th combo box.

Hi! pouj,

If I understand well what you mean that, after you select the option on your from and click command button your report will show all records which match to your selection.

1. Create the query (queryName) from your table, put the criteria under field status:

Like [Forms]![formName]![comboName]

2. Create report from this query.

3. Your form (formName) contains field combo (comboName), which you can either selecting from fields status (you table) or from value list (AWAY and AVAILABLE) or you can also type ahead.

4. In the event on click of your command button put this:

Docmd.OpenReport "reportName", acPreview

That all, this works fine for mine so far.
Aug 23 '07 #5

P: 18
Hi! pouj,

If I understand well what you mean that, after you select the option on your from and click command button your report will show all records which match to your selection.

1. Create the query (queryName) from your table, put the criteria under field status:

Like [Forms]![formName]![comboName]

2. Create report from this query.

3. Your form (formName) contains field combo (comboName), which you can either selecting from fields status (you table) or from value list (AWAY and AVAILABLE) or you can also type ahead.

4. In the event on click of your command button put this:

Docmd.OpenReport "reportName", acPreview

That all, this works fine for mine so far.

okay but how would the comand button know whats the selection in the combo box? and would this work with ore then 1 combo box? cause i might need to run up to 10 different filters.
Aug 23 '07 #6

P: 13
okay but how would the comand button know whats the selection in the combo box? and would this work with ore then 1 combo box? cause i might need to run up to 10 different filters.

OK, it works like this:

1. After you filled out all your selection/combo box (for example 10 combo) in your form and click the command button. (This form contains only 10 combo boxes and command button).

2. It will then try to open your report, which took the record from the query, which had a criteria and

3. This criteria will match to the value in your combo on your form, then it previews the report.

Try this and get back to the forum if you are still unsuccess.
Aug 24 '07 #7

Post your reply

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