Assigning an SQL value to a field on a subform

[Lucky33]

I have a form (FrmAddQuotes) with a tab control and two pages ( quotes for insurance). I am trying to automatically fill the rate field on the quote subform (FrmQuotesAuto), by a rate from the rates table that meets 4 contditions with an SQL statement. here is my code:

Private Sub Year_AfterUpdate()

Dim strSQL As String
Dim rs As DAO.Recordset


strSQL = "SELECT TblRatesAuto.Rate FROM TblRatesAuto " & _
"WHERE (((TblRatesAuto.Year2)>[Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year]-1) " & _
"AND ((TblRatesAuto.CoID)=[Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboCoID]) " & _
"AND ((TblRatesAuto.Product)=[Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboProduct]) " & _
"AND ((TblRatesAuto.Year1)<[Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year]+1));"

Set rs = CurrentDb.OpenRecordset(strSQL)
Me.Rate = rs!Rate
rs.Close
Set rs = Nothing

End Sub

i am getting an error message : too few parameters. expected 3
is it a code error or SQL string syntax error? may be both

any help? :banghead:
thanks

 

[jdraw]

Put a debug.print strSQL statement right before your SET .... statement

That will print the contents/value of strSQL in the immediate window and will show your rendered SQL

EDITED:Then, post the sql, so we can see what was rendered.

 

[Lucky33]

i don't know how this is going to help me. i did it before and all i am getting is the SQL string in one line, no value, and i could not see the error
tks

 

[DrallocD]

use this:

strSQL = "SELECT Rate FROM TblRatesAuto " & _
" WHERE ((Year2> " & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year] - 1 & ")" & _
" AND (CoID)=" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboCoID] & ")" & _
" AND (Product)=" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboProduct] & ")" & _
" AND (Year1)<" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year] + 1 & "))" & ";"

and look at the SQL before creating the recordset. If you don't see the problem, post the SQL for us to see or create a query in SQL view and paste it into there to see what the problem is.

 

[Lucky33]

tks for your efforts. actually my SQL statement is copied from the row source of a combo box on the subform (frmquotesauto). the SQL is working in the combo box but as you know the result will not be posted until you chose it, and i want to get rid of this box. I used your version and i got an error message (see attached clip). here is the result of the debug.print of the sql :
SELECT Rate FROM TblRatesAuto WHERE ((Year2> 2004) AND (CoID)=1) AND (Product)=Prestige) AND (Year1)<2006));

the numbers and conditions are all true, but am not sure about the syntax

 

[jdraw]

What data type is Year2 and Year1?
You seem to have mismatched parentheses {that's what the error message tells you}
Product would seem to be text data type and thus require quotes around "Prestige"

 

[Lucky33]

Year1 and Year2 are long integer, Product is a text

 

[jdraw]

I think this code should work (but not tested)

strSQL = "SELECT Rate FROM TblRatesAuto " & _
" WHERE ((Year2> " & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year] - 1 & ")" & _
" AND (CoID =" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboCoID] & ")" & _
" AND (Product= '" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![CboProduct] & "')" & _
" AND (Year1<" & [Forms]![FrmAddQuotes]![FrmQuotesAuto].[Form]![Year] + 1 & "))" & ";"

 

[Lucky33]

thanks jdraw, this last code is working like a charm.
and this should put an end to my long night

 

[jdraw]

Glad to help

 

[Lucky33]

sorry jdraw but i had to get back to you. i have a new issue with the code. while it is working when all fields have values, it is failing when one or more don't. one or more fields in the quote form have no value because the relevant fields in the lookup table also have no value. i guess the code is failing to compare an empty field in the current form to its corresponding one in the table. i don't know if the following is the problem : the debug.print is giving for example : (category = ' ') for the empty category field, 2 one quotes and not 2 double quotes and thus not able to do the comparison . any ideas ?

 

[Lucky33]

never mind i went around and inserted some IFs and Elses and all worked out.