Remove "Enter Parameter Value"

[shoba7]

Hi,
I am an amatuer in MS Access, i hope you will all be able to help me solve this problem. Before i state my problem, i would like to give a little bit of background info about my project. I have three main tables namely Customer table, Customer Product table and Product Table.

Customer Table
CustomerID(PK)
Name
Address
Telephone
Email
Company

Customer Product
CustomerID
ProductID

Product Table
ProductID (PK)
Product Name

I have a form the contains one combo box and one command button. This combo box contains the list of product names from the Product Table. The main purpose of this form is to select a particular product name and once the command button is clicked, to display all the customers details who have bought that particular product in a report. I have somehow managed to accomplish this task.

But after i have selected the product name from the combo box and clicking on the command button. It displays the "Enter parameter value" box. I have to key in the product name into this box before i am able to view the report. Is there any other way to do this without the "Enter parameter value" box appearing?

I have gone through other similar threads in this forum but i still can't seem to solve this problem. Please help me !!!
Thanks in advance.

 

[Pat Hartman]

If you are opening a report from a form, you have two choices of how to pass parameters.
1. In the report's RecordSource query, use the key field from the form as the criteria -
Where SomeField = Forms!FormName!SomeField;
2. In the OpenReport method, use the "where" argument to filter the report.

 

[shoba7]

Hi Pat,
Thanks for your reply. I tried both the solution you gave me. It worked partially. The "Enter Parameter Value" box does not appear. But based on the selection that i have selected from the product Name combo box, the respective records are not appearing in the report. Below is the query that the report refers to.

SELECT [Customer Table].[CustomerID], [Customer Table].[Name], [Customer Table].[Address], [Customer Table].[Company], [Customer Table].[Title], [Customer Table]., [Product Table].[Product Name]
FROM [Customer Table], [Product Table]
WHERE [Customer Table].CustomerID IN (SELECT DISTINCT (CustomerID) FROM [Customer Product] WHERE ProductID IN (SELECT [Product Table].ProductID from [Product Table] WHERE [Product Table].[Product Name] = Forms!SearchforProduct![Product Name]) ) AND [Product Table].[Product Name]= Forms!SearchforProduct![Product Name];

What am i doing wrong? I hope you can help. Thanks.

 

[MarkK]

SELECT [Customer Table].[CustomerID], [Customer Table].[Name], [Customer Table].[Address], [Customer Table].[Company], [Customer Table].[Title], [Customer Table]., [Product Table].[Product Name]
FROM [Customer Table], [Product Table]
WHERE [Customer Table].CustomerID IN (SELECT DISTINCT (CustomerID) FROM [Customer Product] WHERE ProductID IN (SELECT [Product Table].ProductID from [Product Table] WHERE [Product Table].[Product Name] = [Forms]![SearchforProduct]![Product Name]) ) AND [Product Table].[Product Name]= [Forms]![SearchforProduct]![Product Name];

More square brackets??

 

[shoba7]

Hi,
Thanks for replying but it still does not work.

 

[maikon]

It's function:

SELECT [Customer Table].CustomerID, [Customer Table].Name, [Customer Table].Address, [Customer Table].Telephone, [Customer Table].Email, [Customer Table].Company
FROM ([Customer Product] INNER JOIN [Customer Table] ON [Customer Product].CustomerID = [Customer Table].CustomerID) INNER JOIN [Product Table] ON [Customer Product].ProductID = [Product Table].ProductID
WHERE ((([Product Table].[Product Name])="YourProductName"));

If you to place a combobox with product names in a form, then you need to place on the combobox rowsource property the query above.

You can save the query and call it for its name on the rowsource.

Don't forget to change the query to receive the combobox value:

WHERE ((([Product Table].[Product Name])= [Forms]![YourForm]![YourComboBox]));

That's it!

 

[shoba7]

Hi,
It still does not work.....