using a combo box value with my SQL query

[Gaby]

I made a subform on my my form from a query i have. I am asking it to
populate a list. The query works fine but I am having to type in the
parameter value form what company I am looking for.

As for the actual form I have a combo box. I want this list to
populate when they select a compnay from the combo box. What to I have
to type in the SQL statement to achieve this???

Here is my SQL statement I used to create my query, like i said- its
works fine with me having to type in the company name manually:
SELECT DISTINCT Projects.Project_Date, Projects.Project_Name,
Company_Name.Company_ID, Projects.Project_Cost,
Company_Name.CompanyName, Invoice.Invoice_Date
FROM Projects INNER JOIN (Invoice INNER JOIN ((Company_Name INNER JOIN
[Board Members] ON Company_Name.Company_ID = [Board
Members].Company_ID) INNER JOIN Project_Activities ON
Company_Name.Company_ID = Project_Activities.Company_ID) ON
Invoice.Invoice_ID = Project_Activities.Invoice_ID) ON
Projects.Project_ID = Project_Activities.Project_ID
WHERE (((Projects.Project_Date)="07") AND
((Company_Name.CompanyName)=[Combo14]))
ORDER BY Projects.Project_Date DESC , Projects.Project_Name;

i hope i explained it ok. let me know if you guys have any questions.

 

[Douglas J. Steele]

Where you have [Combo14], you need Forms!NameOfForm![Combo14]

If [Combo14] is on a subform, then you need
Forms!NameOfForm!NameOfSubformControl.Form![Combo14]

(replace NameOfForm and NameOfSubformControl with the real names)

Note that depending on how you added the subform to the parent form, the
name of the subform control on the parent may not be the same as the name of
the form being used as the subform. It's the name of the control on the
parent form that you need to use.

 

[Gaby]

not sure if i typed it wrong but i changed my query to look like
thisand it wont work. im not sure if there is something else i need to
do. i pasted the form "SubformProjects" as a subform on
"Projects_Records"

SELECT DISTINCT Projects.Project_Date, Projects.Project_Name,
Company_Name.Company_ID, Projects.Project_Cost,
Company_Name.CompanyName, Invoice.Invoice_Date
FROM Projects INNER JOIN (Invoice INNER JOIN ((Company_Name INNER JOIN
[Board Members] ON Company_Name.Company_ID=[Board Members].Company_ID)
INNER JOIN Project_Activities ON
Company_Name.Company_ID=Project_Activities.Company_ID) ON
Invoice.Invoice_ID=Project_Activities.Invoice_ID) ON
Projects.Project_ID=Project_Activities.Project_ID
WHERE (((Projects.Project_Date)="07") And
((Company_Name.CompanyName)=Forms!Projects_Records!SubformProjects.Form!Combo14))
ORDER BY Projects.Project_Date DESC , Projects.Project_Name;

let me know what else i might have to do. thanks again for ur help.

gabriel

Where you have [Combo14], you need Forms!NameOfForm![Combo14]

If [Combo14] is on a subform, then you need
Forms!NameOfForm!NameOfSubformControl.Form![Combo14]

(replace NameOfForm and NameOfSubformControl with the real names)

Note that depending on how you added the subform to the parent form, the
name of the subform control on the parent may not be the same as the name of
the form being used as the subform. It's the name of the control on the
parent form that you need to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I made a subform on my my form from a query i have. I am asking it to
populate a list. The query works fine but I am having to type in the
parameter value form what company I am looking for.

As for the actual form I have a combo box. I want this list to
populate when they select a compnay from the combo box. What to I have
to type in the SQL statement to achieve this???

Here is my SQL statement I used to create my query, like i said- its
works fine with me having to type in the company name manually:
SELECT DISTINCT Projects.Project_Date, Projects.Project_Name,
Company_Name.Company_ID, Projects.Project_Cost,
Company_Name.CompanyName, Invoice.Invoice_Date
FROM Projects INNER JOIN (Invoice INNER JOIN ((Company_Name INNER JOIN
[Board Members] ON Company_Name.Company_ID = [Board
Members].Company_ID) INNER JOIN Project_Activities ON
Company_Name.Company_ID = Project_Activities.Company_ID) ON
Invoice.Invoice_ID = Project_Activities.Invoice_ID) ON
Projects.Project_ID = Project_Activities.Project_ID
WHERE (((Projects.Project_Date)="07") AND
((Company_Name.CompanyName)=[Combo14]))
ORDER BY Projects.Project_Date DESC , Projects.Project_Name;

i hope i explained it ok. let me know if you guys have any questions.

 

[Gaby]

not sure if i typed it wrong but i changed my query to look like
thisand it wont work. im not sure if there is something else i need to

do. i pasted the form "SubformProjects" as a subform on
"Projects_Records" . The actual combo box i am using in on the form.
but the data sheet it will generate is a subform. i hope that makes
sense....

SELECT DISTINCT Projects.Project_Date, Projects.Project_Name,
Company_Name.Company_ID, Projects.Project_Cost,
Company_Name.CompanyName, Invoice.Invoice_Date
FROM Projects INNER JOIN (Invoice INNER JOIN ((Company_Name INNER JOIN
[Board Members] ON Company_Name.Company_ID=[Board Members].Company_ID)
INNER JOIN Project_Activities ON
Company_Name.Company_ID=Project_Activities.Company_ID) ON
Invoice.Invoice_ID=Project_Activities.Invoice_ID) ON
Projects.Project_ID=Project_Activities.Project_ID
WHERE (((Projects.Project_Date)="07") And
((Company_Name.CompanyName)=Forms!Projects_Records!SubformProjects.Form!Com­bo14))

ORDER BY Projects.Project_Date DESC , Projects.Project_Name;


let me know what else i might have to do. thanks again for ur help.


gabriel