Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / January 2007

Tip: Looking for answers? Try searching our database.

passing two selected fields to filter a report

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
FilemakerPro_Developer - 31 Jan 2007 01:39 GMT
I have a form with two combo box value list fields: reportName, fiscalQtr
The form these fields are on shouldn't store the data.   When I created the
value list
I chose store the value for later use.  I noticed that there are 8 records
in the form.
I don't want it to store a value for the field just use it as a selection
for the reports.
I couldn't figure out which property to not allow for creation of records.

The main question I have is how to pass the two values to the form so I can
filter the report?  When I click the button on this form it says it can't
find the  field reportname in my expression.

Private Sub cmdGetReport_Click()

'On Error GoTo Err_
   Dim stDocName As String
   Dim stFiscalQtr As String
   
   stDocName = [Forms]![frmChooseReport]![ReportName]
   stFiscalQtr = [Forms]![frmChooseReport]![FiscalQtr]
   DoCmd.OpenReport stDocName, acPreview, , strFilter

End Sub
thanks for all your help, I'm almost done.
Signature

Janis

Albert D. Kallal - 31 Jan 2007 04:01 GMT
> I have a form with two combo box value list fields: reportName, fiscalQtr
> The form these fields are on shouldn't store the data.

Ok, so, what this means is that you should not have bound the form to a
table. If the form is not going to enter data, and is just some type of
"interface" form, then you

  When I created the
> value list
> I chose store the value for later use.  I noticed that there are 8 records
[quoted text clipped - 20 lines]
> End Sub
> thanks for all your help, I'm almost done.
Albert D. Kallal - 31 Jan 2007 04:18 GMT
opps..bumped send key...sorry....

> I have a form with two combo box value list fields: reportName, fiscalQtr
> The form these fields are on shouldn't store the data.

Ok, so, what this means is that you should not have bound the form to a
table. If the form is not going to enter data, and is just some type of
"interface" form, then you the "term" we use in ms-access is a
"un-bound" form.

>> I couldn't figure out which property to not allow for creation of
>> records.

Well, the above is a difernt question, and a differnt problem. However, if
we make the form un-bound, and the form is not attahced to a table, then
we really solved this problem anyway.

> When I created the
> value list
> I chose store the value for later use.

Good!!!...the option to store the value for laster use
means that the combo box is un-bound (the other option
you are presetned with is to "store that value in a field".

So, you 100% on the right track for the combo box, but
still seems that your form is bound to a table, and it should
not be. So, you want to fire up the form in desing mode, and
then go

       edit->select form    (you must do this step if you clicked on
                                     or modifed anything else on the form
                                     this step ensures you are about to
                                     view the properites sheets for the
FORM!!!!!!!!!!

      view->properteis

So, you have to do both steps to display the propdofery sheet for the form
(else, you get the properity sheet for the detail section of the form, or
even
for the last contorl you were modyfing in desing mode).

Now that the forms properity sheet is dispsyed, select the data tab

The first entry is what the form (table or query) is bound to.
RecordSource:  "some table name or query"

Simply erase (blank out) the RecordSource setting

This now means that the form is un-bound, not attched to a table,
and therefore can't enter reords into any table. Of couse, since
there is not table attached, then you licky should click on the
format tab, and get rid of TONS of junk. some things to
get rid of:

scoll bars:  none            you don't need scollr bars
Record Selecotres:  no     this is the bar are the left side of a
form/reocrd that you can click on, and then hit
                                      the delect key (or copy...or
wgatever). Since we are a un-bound form..then might
                                      as well set this to no
Navagation Buttions:no       again, might as well turn off the navagation
buttions that access puts at the bottom
                                         of each form.

Auto Resize: no            you don't need the size of the form chaning on
you.....

So, even when a form is un-bound, you still have to manually disoaplbe all
of those
extra features yourself. In fact, for a lot of bound forms, I turn off a
fair amont of
these features.
\
> The main question I have is how to pass the two values to the form so I
> can
> filter the report?  When I click the button on this form it says it can't
> find the  field reportname in my expression.

Private Sub cmdGetReport_Click()

'On Error GoTo Err_
   Dim stDocName As String
   Dim stFiscalQtr As String

   stDocName = me.ReportName
   stFiscalQtr = "FiscalQtr = " & me.FiscalQtr
   DoCmd.OpenReport stDocName, acPreview, , strFilter

End Sub

the above is all you need, but you not given the name of the fiscal qery
field you want to filter on. And, you not mentioned if the fiscalqry field
is number, or text. You have to correct my code based on the answers you
give.

Also, you said you have two selected fields????? In the above I see one
field that is the report, and one field that is the filter value.  Am I
miss-understanding you..and you actually want ONE report to be filtered by
two field values????

Signature

Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.