Pass [Forms]!.. item froma form to query of second form (1 Viewer)

[KAsad]

Pass data from a Form to another form
Hi

I have a form which I ask user for date from, date to, and Pratice. Then I want to pass them to second form which is based on a query. I want to use the items from the first form to filter the query for the second form like this
SELECT DISTINCT t1.CUST_PRACTICE, t2.CUST_NAME, t3.EMP_NAME
FROM
(
(SELECT CUSTOMER_PRACTICE.CUST_PRACTICE, TIMESHEET.TIME_DATE, TIMESHEET.TIME_ID, CUSTOMER.CUST_NAME, CUSTOMER.CUST_PRACTICE_ID
FROM (TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID)
LEFT JOIN CUSTOMER_PRACTICE ON CUSTOMER.CUST_PRACTICE_ID = CUSTOMER_PRACTICE.CUST_PRACTICE_ID
WHERE ( (TIMESHEET.TIME_DATE) >=[Forms]![frmClientbyPractice].[dtFrom] AND (TIMESHEET.TIME_DATE)<=[Forms]![frmClientbyPractice].[dtTo])
AND (CUSTOMER_PRACTICE.CUST_PRACTICE=[Forms]![frmClientbyPractice].[cboTSPractice])) AS t1
LEFT JOIN
( SELECT CUSTOMER.CUST_NAME, TIMESHEET.TIME_ID
FROM TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID) AS t2 ON t1.TIME_ID = t2.TIME_ID)
LEFT JOIN
( SELECT EMPLOYEE.EMP_NAME, TIMESHEET.TIME_ID
FROM (TIMESHEET
LEFT JOIN [ORDER] ON TIMESHEET.ORD_ID = ORDER.ORD_ID)
LEFT JOIN EMPLOYEE ON ORDER.ORD_PM = EMPLOYEE.EMP_ID) AS t3 ON t1.TIME_ID = t3.TIME_ID;

but the [Forms]![... items are always empty??? Please help me.
Thank you

 

[pr2-eugin]

Make sure the Form that feeds into the new form is not closed until the Form is loaded. Also is the Query a pre built query? or is it generated dynamically?

 

[KAsad]

Thank you for your reply
The form is open and the query is a separate query that I have created in query wizrd and then attached it to form as datasource

 

[pr2-eugin]

Hmmm.. Does the Query execute normally? giving you the desired result, without the Forms interfering here?

 

[KAsad]

When I run the query, I get the pop up which asks for the value of [Forms]![....] and when I put the details, it works fine. So I was assuming when I click on GO button in first page which calls teh second page, the value of [Forms...] are still somewhere in memory and by using the [Forms]!... I can use the values in my query...

I hope it makes sense

 

[pr2-eugin]

Yes I understand what you mean, I use the same thing on my Forms.. I call another Form that is based on a built query.. I know this sounds crazy, but instead of having a query name as the data source, copy the Query from SQL window and paste that in the Data source of the second form.. If that does not sort it, could you upload a stripped down version of your database in a MDB format, so we can look at it??

 

[KAsad]

Thanks a lot. It works if I copy the SQL query to the form as you have said.

It is very odd!!

Again thank you

 

[pr2-eugin]

Well my guess was right.. Glad it sorted things..

I am not sure if this is the exact answer for this.. I hope someone will correct me if I am wrong..

Based on my experience, If you have an in built Query (which is pre-compiled) as a recordsource, Access thinks the values are present and hence they do not ask for new values, unless the query is manually opened (maybe using a DoCmd.OpenQuery method).

But when you use a natural Query as a RecordSource it is executed when you are trying to open the Form. Thus requires those values..