How do you create a set of records

[gwog]

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.

 

[pbxdave]

Hi

I am curious as to why you don’t just do something like this:

Select Field1,Field2,Field3
From Table1
Where date >= '12/22/2000' and other_stuff = 1
Order by order_number

If you can get your SQL statement optomized you wouldn't need to have a query call a query.

You could also build the query in access and save it so all you pass are parameters when you call the query.

Hope this helps.

david

 

[gwog]

David,

Thanks for responding. As I said I have very limited programming knowledge, so I don't really know if I understand what you are saying.

IF you don't mind walking me through it sort of step by step.

Let's start with where would I put the above code? I would assume it would be place in to on open property of each report? Is that correct?

If I know where to place it then perhaps I can make an attempt at it.

Thanks!

 

[johnwm]

gwog
You may do better with this sort of question in one of the Access forums that you use, rather than here in the VB6 forum.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'