Qdf not getting parameter from referenced queries/form

[SomeDude]

I'm creating an amortization schedule database using Access 2010.

I designed it such that I'll input actual principal and interest amounts in tbl_LoanActivity when I get statements in. I'd like to be able to press a button to update forecasted principal and interest amounts, then later I'll add code to make a report pop-up.

I initially designed this with only one loan to see if I could make the thing work, and it did. I've since added another loan and made adjustments, and this is where I'm having an issue.

tbl_LoanActivity references tbl_Loans, which has interest rates and monthly payment amounts. Needless to say tbl_Loans is the RecordSource for frm_Loans, and as such has a field LoanID. I have two queries that need to reference [Forms]![frm_Loans]![LoanID]:
1) qry_LoanActivity_MaxDate,
2) qry_LoanActivity_withPrincipalBalance

I can run these 2 queries (individually) with a reference to frm_Loans.LoanID just fine. (Without that reference, forecasted principal and interest numbers are highly inaccurate.) I have a qdf append query (vba) to calculate forecasted principal and interest amounts based on the most recent principal balance (referencing queries above), and relevant data in tbl_Loans (InterestRate, and RecurringAmount).

So here's the problem: without the above queries referencing [Forms]![frm_Loans]![LoanID] the qdf runs, yet produces inaccurate numbers. With the above queries referencing [Forms]![frm_Loans]![LoanID], Access gives me a message "Too few parameters. Expected 1." If I take the qdf and put it into a standard query it runs just fine. I need this append query to be a qdf so it will loop until RecordsAffected = 0.

I've sterilized the data and attached the database for your review. If somebody could take a look at this and see what I've done wrong, I'd appreciate it. I was thinking this adjustment of adding the reference to [Forms]![frm_Loans]![LoanID] in the 2 above queries would work, but it seems like the qdf is not inheriting the paramter.

 

[pbaldy]

Sounds like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

You may be able to wrap the form references in the Eval() function to get around the problem, in addition to the method in the link.

 

[Galaxiom]

Unlike DoCmd.OpenQuery, the Execute Method of a QueryDef object is run directly by the database engine and has no awareness of Access objects.

Hence it is expecting the Form references to be supplied as parameters.

 

[SomeDude]

I tried the Eval( function in the queries. No effect.

The qdf doesn't have any direct references to the form frm_Loans. The qdf is referencing a table (tbl_Loans) and the 2 queries (which reference frm_Loans). It seems like the qdf should pick up what the queries pick up since it's the queries that reference frm_Loans. Since that's not the case, any ideas on how to pass parameter values to the qdf, even though it doesn't directly reference frm_Loans?

 

[Galaxiom]

The queries it is based on are still execute directly by the engine so will fall over anywhere in the chain if they encounter an Access object.

I usually build the whole query as text with the control values concatenated and Execute it directly from VBA.

 

[pbaldy]

I tried the Eval( function in the queries. No effect.

How exactly did you try to use it? What is the SQL of the query with that effort?