rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

[psyduck1085]

Hi All,

Please help, I get a run-time error 2147217904(80040e10) stated no value given for one or more required parameters.

Below are the VBA that i get to add new fuction in ms access.

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = New CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function


And my queries are as follow;

Field 1: Conc("T24 Reference","Counterparty",[Counterparty],"X-4_Reporting_Today")

And the error code point to
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Can someone pls help to solve my issues!

 

[namliam]

You cant run parameter queries in code, your sql becomes one thus causing the error.
Try adding a
debug.print SQL
or
msgbox sql

before the rs.open to see the sql and find your error

edit: p.s. when posting code please wrap the code in the code tags:
[ code ]
Code here
more code
[ /code ]
wihtout the spaces to make it work

 

[psyduck1085]

After all, I get a new error call compile error... :banghead: And i was trying to select tools > preference look for any "Missing" but can't find.... So I unchecked some existing preference and close it... but the error still occured!

Please help! I'm using MS 2010!

 

[namliam]

you probably just need to (re)add your default ones, just create a new database and find the ones you are missing.