Hi Query Nuts....
I need your help! This may get a little complicated...I suppose I should give some background so that it may be easier to comment on whether I have the correct approach....
My DB consists of 3 tables setup to capture customer complaints -
tblEnquries
tblCustomers
tblEmployees
Relationships -
Customer_No (tblCustomers) to Customer_No (tblEnquiries) - One to Many
Employee_No (tblEmployees) to Employee_No (tblEnquiried) - One to Many
I have setup a form to analyse logged complaints. I have also setup a query on almost the entire database. There are three subforms on the mainform, linked to the query - to display various details (eg complaint details, customer details).
These are refreshed based on the complaint ID - which is entered into a text box. Requery occurs when a 'search' button is pressed.
Now to my problem.....
I have set up a fourth subform. When a complaint ID is searched, I would like the subform to display ALL complaints lodged by the customer.
The way I thought it would work is -
Create a text box on the mainform that links to the first query (tblCustomers, Customer_No). When the query is run - the textbox is populated with the Customer Number. This part works.
Create another query containing -
Customer_No (tblEnquries)
ID (tblEnquiries)
and other relevant fields (I believe these are not part of the problem).
I also wrote a SQL condition in the query (Customer_No = Customer_No Textbox) to filter accordingly.
Create the subform based on the new query.
Now, when I key in the complaint ID number, the new query only returns the details for the current complaint ID - not all complaints lodged by the customer.
I notice when I open the query - it prompts me for a Complaint ID number AND Customer Number - I guess this is the problem.
I am assuming that it wants the complaint ID number, because the Customer Number in the textbox is based on the original query. Although I'm not sure if this is the case.
Could anyone offer insight/solution to my problem?
I have attached my DB to explain...please don't make fun of my code! I really am new at this
plus the DB is not finished yet 
The relevant form is 'frmPSUpdate'.
Thanks
Rob
I need your help! This may get a little complicated...I suppose I should give some background so that it may be easier to comment on whether I have the correct approach....
My DB consists of 3 tables setup to capture customer complaints -
tblEnquries
tblCustomers
tblEmployees
Relationships -
Customer_No (tblCustomers) to Customer_No (tblEnquiries) - One to Many
Employee_No (tblEmployees) to Employee_No (tblEnquiried) - One to Many
I have setup a form to analyse logged complaints. I have also setup a query on almost the entire database. There are three subforms on the mainform, linked to the query - to display various details (eg complaint details, customer details).
These are refreshed based on the complaint ID - which is entered into a text box. Requery occurs when a 'search' button is pressed.
Now to my problem.....
I have set up a fourth subform. When a complaint ID is searched, I would like the subform to display ALL complaints lodged by the customer.
The way I thought it would work is -
Create a text box on the mainform that links to the first query (tblCustomers, Customer_No). When the query is run - the textbox is populated with the Customer Number. This part works.
Create another query containing -
Customer_No (tblEnquries)
ID (tblEnquiries)
and other relevant fields (I believe these are not part of the problem).
I also wrote a SQL condition in the query (Customer_No = Customer_No Textbox) to filter accordingly.
Create the subform based on the new query.
Now, when I key in the complaint ID number, the new query only returns the details for the current complaint ID - not all complaints lodged by the customer.
I notice when I open the query - it prompts me for a Complaint ID number AND Customer Number - I guess this is the problem.
I am assuming that it wants the complaint ID number, because the Customer Number in the textbox is based on the original query. Although I'm not sure if this is the case.
Could anyone offer insight/solution to my problem?
I have attached my DB to explain...please don't make fun of my code! I really am new at this
plus the DB is not finished yet The relevant form is 'frmPSUpdate'.
Thanks
Rob
