Access subform filter data from parent

I have an application that starts with a Main form. When the user selects a job and clicks a button this code executes
   
    stLinkCriteria = "[Job#]=" & Me![lboxJobList]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

this works and the form/subform display correctly

The problem is that access appears to load the entire table for the subform

The subform recordsource is just a simple select

select * from jobdetails

How can I convert this to a query with a where clause and then set the parameter before loading?
LVL 9
JonMnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
If the form and subform both show the correct record, I don't see what the problem is...

By subform, do you mean that is embedded in the main form? Which form does the button open?
0
JonMnyAuthor Commented:
it's a performance problem.

one form opens a second. The second form has the subform
0
Rey Obrero (Capricorn1)Commented:
<The second form has the subform >

on this second form, did you set the Master/Child Link fields property of the subform?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JonMnyAuthor Commented:
yes those are set to the job#
0
etech0Commented:
Assuming that you have the ability to change records from the parent form, your setup is  really the best setup for what you want. We could set it so that the subform's recordsource was that one record, but then it would have to requery each time you change records, which would take more time.
0
Rey Obrero (Capricorn1)Commented:
the problem here is, the subform is loaded first before the main form, so when you apply the filter to the main form from the command  "docmd.openform" the subform is already loaded.. so add this code in the load event of the main form

private sub form_load()



me.subformcontrolName.requery

end sub
0
JonMnyAuthor Commented:
the table is linked to a SQL Azure database. It takes about 2 min to open the form. So really needs to load on demand
0
etech0Commented:
Do you ever use the parent form to change records?
0
JonMnyAuthor Commented:
no they have to go back to the main form to do that.
0
etech0Commented:
What's the recordsource of the parent form?
0
mbizupCommented:
If you wamt to load records on demand,  leave your main and subform recordsources blank in their property sheets (Keep the master/child links intact), and set the recordsources through code in their open events, loading just the records specified by the first form.

In the Subform Open Event:

Dim strSQL as string
Dim strCriteria as string

StrSQL = "SELECT * FROM jobdetails " 

strCriteria = NZ(Forms!YourFirstFormName.lboxJobList,"")
If strCriteria <> "" Then  strCriteria = "WHERE [job#] = " & strCriteria

Me.Recordsource =  strSQL  & strCriteria

Open in new window



And in the main form Open event (adjust according to your actual mainform query, as needed):


Dim strSQL as string
Dim strCriteria as string

StrSQL = "SELECT * FROM jobMain " 

strCriteria = NZ(Forms!YourFirstFormName.lboxJobList,"")
If strCriteria <> "" Then  strCriteria = "WHERE [job#] = " & strCriteria

Me.Recordsource =  strSQL  & strCriteria

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.