SubQuery Parameter from a Main Query? How?

[cj92713696]

SubQuery Parameter from a Main Query? How?

I would like to pass a record value from a main query, the value ItemCode (please see below), to the subquery. How do I do this? I am using Access 2000. As of to date, I simply have the subquery query all ItemCodes and then using an external join link the main query back to ItemCode. As you would expect, this is very, very slow.

Thanks for your help.

Main Query
ItemCode
ItemDesc
QtyOnHand

SubQuery
ItemCode -> Parameter
ReceivedQuantity
Date -> Parameter

Thanks,
CJ

 

[scriverb]

When you have a situation like this it is imperative that you index the join field so that the access between the two tables is optimized. The Item number field in both tables should be indexed in your situation. As the number of records grows in your database and if you have not indexed the SubForm tables ItemNumber then the speed to fill the subform will deteriorate proportionately.

Personally I try not to maintain global links between my database tables. I maintain the referential intergrity myself through code. The reason for this is that everytime you read a record from the one side of a one to many relationship ACCESS automatically access all records on the manyside just incase you need them. Many times you don't but the time to retrieve them happens anyway.

Getting back to your situation. If you index the Item Code in the underlying table for your subForm you can either use the Main Forms subform object Child and Master file Link field property. This still requires the indexing of the ItemCode.

Another way of accessing the correct records in the subform is to use a gloval variable with a Function statement that calls that value into the criteria statement of the underlying subForm query which selects your records. This example would eliminate the need for the global linking and the use of the Child/master field linking in the subform object. Just store the ItemCode of the Master record in a gloval variable and requery the subform in code. The underlying query of the subform would access the ItemCode value from the function statement and refresh the subform with the correct records.

I hope that I haven't confused you completely at this point.

First thing I would do before changing everything your do is make sure that the ItemCode of the Many table is indexed on ItemCode. This should solve most of your speed problems.

Get back with the results and we can help you with the other.

Bob Scriver

 

[cj92713696]

Thanks for your help. I've indexed the important fields from which I base my queries. Access just seems very slow. I'm running on a 1 Gz machine too!

My problem essentially is the creation of dynamic parameters based on the primary query. I would like to pass to a subquery a value from the primary query. The problem is I'm not sure how to do this. When I declare a parameter in my subquery, ie. [@ItemCode], once the primary query is run it prompts me for the value of [@ItemCode]. I want @ItemCode to come from the primary query only.

Maybe this can't be done unless you're calling a function from a module. Any suggestions?

Thanks,
CJ