SQl Recordset error (1 Viewer)

[Sameh101]

Hello, help is appreciated;

Dim qname As String
qname = Me.CboName.Value

Dim RcC As Recordset
Dim RCsql As String

RCsql = "SELECT IncomeTrans.iID, IncomeTrans.iDate, IncomeTrans.iname, IncomeTrans.MeNet, IncomeTrans.Oshor, IncomeTransDeu.MeDeduct, IncomeTransDeu.OshDeduct," _
& " IncomeTrans.RTOshor, IncomeTrans.RTMeNet FROM IncomeTrans LEFT JOIN IncomeTransDeu ON IncomeTrans.[iID] = IncomeTransDeu.[iiU] WHERE (((IncomeTrans.iname) = qname))" _
& " ORDER BY IncomeTrans.iDate desc, IncomeTrans.iid desc;"

Set RcC = CurrentDb.OpenRecordset(RCsql, dbOpenDynaset)


It gets to this point then error msg "Run-time error 3061 , too few parameter, expected 1"
What am I doing wrong?
I replaced the me.cboname in sql with a "name", it worked fine. but using cboname or forms!formname!cboname gives that error.
Please help.
Thanks.

 

[pbaldy]

You have to concatenate the variable into the string. Here's a FAQ:

Building SQL in VBA

 

[The_Doc_Man]

I'm with Paul here. The specific reason you got that error is because you don't realize that there are two environments in play here. I'll explain.

The first environment is the Access development environment, which includes forms and combo boxes and VBA code. The second environment is the SQL execution environment. The catch is, they rarely talk to each other very well. Points of contact include SQL string passage to recordsets or the currentdb.execute command, plus a few others.

That piece of your SQL string that looks like (((IncomeTrans.iname) = qname))" has the problem that you are treating qname like SQL has reason to know its value. But it doesn't. Your qname variable was given its value in the Access environment, not in the SQL environment. To SQL, that is just a name (which in that context might look like a parameter). Since your error says it was expecting 1 parameter, I'm guessing that it thinks qname is that parameter. Paul's suggestion involves learning how to substitute the value in place of qname at that point, because the string can carry the substituted value to SQL where it would be perfectly fine.

 

[Sameh101]

pbaldy, The_Doc_Man, Thank you so much. you are great. you guys are right, it was missing "'" before and after the variable.​

​

 

[pbaldy]

Happy to help!