Using Access '97.
I have a database named fillrate.mdb.
It contains the following tables:
tblData (primary key of 3 fields: item,order#,day)
tblCrossReference (primary key of item)
tblItemHierarchy (primary key of item)
tblFiscalYear (primary key of day)
I have created a form named frmTimeFrame which has 3 different "parts".
1st Part I have an option group (grpTimeFrame) which allows user to select a timeframe from comboboxes that pull data from 3 fields (day,week,period)from the tblFiscalYear. The user can select only one option from this group.
2nd part allows user to farther limit data by choosing various criteria in combo boxes(all driven by fields in the 3 tables mentioned above). The user can select something from as many different combo boxes as they wish.
The 3rd part of the form contains several command buttons that preview different reports.
What I am doing currently (and it seems to work) is when a command button is clicked the data for the report comes from a query. The query (qryPullCriteria) contains as its source another query (qryPullData) which source is tblData.
The qryPullData creates a record set from tblData (which also contains fields [day],[week],[year]) which corrosponds to the option choosen in option group 1 (grpTimeFrame) on the form.
The qryPullCriteria then creates a record set from that based on the option(s) selected from the combo boxes. For each combobox associated field (using and) I have something similiar to this in the criteria field of the query:
Like [Forms]![frmTimeFrame]![ComboSCCAT] & "*" Or Is Null
This seems to work fine. My issue is that it takes a long time for each report to pull up. I am hoping someone can coach me through a way of programming this so it speeds the process up. Because I have a feeling the part that is slowing it down is the running of the two queries each time a command button is choosen.
I am only a very beginner at programming, so the simplist explanation you can give would be greatly appreciated!
lisa.
I have a database named fillrate.mdb.
It contains the following tables:
tblData (primary key of 3 fields: item,order#,day)
tblCrossReference (primary key of item)
tblItemHierarchy (primary key of item)
tblFiscalYear (primary key of day)
I have created a form named frmTimeFrame which has 3 different "parts".
1st Part I have an option group (grpTimeFrame) which allows user to select a timeframe from comboboxes that pull data from 3 fields (day,week,period)from the tblFiscalYear. The user can select only one option from this group.
2nd part allows user to farther limit data by choosing various criteria in combo boxes(all driven by fields in the 3 tables mentioned above). The user can select something from as many different combo boxes as they wish.
The 3rd part of the form contains several command buttons that preview different reports.
What I am doing currently (and it seems to work) is when a command button is clicked the data for the report comes from a query. The query (qryPullCriteria) contains as its source another query (qryPullData) which source is tblData.
The qryPullData creates a record set from tblData (which also contains fields [day],[week],[year]) which corrosponds to the option choosen in option group 1 (grpTimeFrame) on the form.
The qryPullCriteria then creates a record set from that based on the option(s) selected from the combo boxes. For each combobox associated field (using and) I have something similiar to this in the criteria field of the query:
Like [Forms]![frmTimeFrame]![ComboSCCAT] & "*" Or Is Null
This seems to work fine. My issue is that it takes a long time for each report to pull up. I am hoping someone can coach me through a way of programming this so it speeds the process up. Because I have a feeling the part that is slowing it down is the running of the two queries each time a command button is choosen.
I am only a very beginner at programming, so the simplist explanation you can give would be greatly appreciated!
lisa.