Query by sub-form

[Artifiring]

I am having issues with running a query from a subform. I have a form
that consists of a drop-down box to load the appropriate information
into the subform. In this subform, I have a list of companies. I then
have a button next to each company (detail section of sub-form contains
only company name and button) to show company information. I want to
make this button run a query that I can use to generate a report.

The form in itself works fine, as well as the query in itself. My issue
is pulling the company name from the subform into the critera of the
query. I have tried using the expression builder to pull the company
name but can't seem to get it right. It always prompts me for the
company name.

Any help that you can offer would be GREATLY appreciated.

 

[Marshall Barton]

I am having issues with running a query from a subform. I have a form
that consists of a drop-down box to load the appropriate information
into the subform. In this subform, I have a list of companies. I then
have a button next to each company (detail section of sub-form contains
only company name and button) to show company information. I want to
make this button run a query that I can use to generate a report.

The form in itself works fine, as well as the query in itself. My issue
is pulling the company name from the subform into the critera of the
query. I have tried using the expression builder to pull the company
name but can't seem to get it right. It always prompts me for the
company name.

The reference to the subform's control would be like:

Forms!mainform.subformcontrol.Form.companytextbox

BUT, that's involving more objects than needed to meet your
goal. The code in the buutton;s Click event can take care
of it all. First, remove the company field's criteria from
the query. Then use the OpenReport method's WhereCondition
argument so the code includes lines like:

Dim stLinkCriteria As String
stLinkCriteria = "[company name field] = """ _
& companytextbox & """"
DoCmd.OpenReport "report name", acViewPreview, _
WhereCondition:= stLinkCriteria

 

[Artifiring]

Marsh

THANK YOU SOOOO MUCH!!! You are a life saver. I can't believe it was
that easy to do and I was using so many more objects then necessary.
Didn't realize you could use criteria in the OpenReport method like
that. Thanks for your help. I appreciate it.

- Artie

 

[Marshall Barton]

Marsh

THANK YOU SOOOO MUCH!!! You are a life saver. I can't believe it was
that easy to do and I was using so many more objects then necessary.
Didn't realize you could use criteria in the OpenReport method like
that. Thanks for your help. I appreciate it.

Thanks for posting that, especially that you got it to work
from my first response. I was beginning to think I was
using a weird language or something. Since lately all(?) my
other responses seem to come back as "doesn't work", it's
nice to know I'm not completely out of whack ;-)