Storing a SQL String Result into a Recordset

[mathboy23]

strSql = "SELECT tblUsers.UserID FROM TBLUSERS WHERE tblUsers.UserID = strUSERID"

'Set the source query defInItIon to get the current records In curSourceQueryDef
Set rsResult = dbs.Execute(strSql, dbOpenDynaset)

The last line in this code is not working in VBA. Can anyone help me with this code? Thanks in advance!

 

[raskew]

Mathboy-

Check 'Execute method' in your help file. You'll find it's used to
run an Action query, when in fact what you're asking for
(a select query) is not an Action Query.

Here's an example of how you could achieve this, using Northwind's Orders table as a test-bed:

Function MathBoy(pstr As String)

Dim dbs As Database
Dim rsResult As Recordset
Dim intHold As Integer, n As Integer
Dim strSQL As String

Set dbs = CurrentDb
intHold = Val(pstr)
strSQL = "Select OrderID, CustomerID from Orders where OrderID = " & intHold & ";"
Set rsResult = dbs.OpenRecordset(strSQL)
With rsResult
.MoveLast
.MoveFirst
End With
n = rsResult.RecordCount
Debug.Print n
rsResult.Close
dbs.Close
Set dbs = Nothing

End Function


To test, from the debug window:

? mathboy("10268")
1

 

[taShi]

Raskew,

Thanks for your response but I still get the same error. I get error '3061' "too few parameters.expected 1"

it still errors out at the same line.

Set rsResult = dbs.OpenRecordset(strSql)

Any other ideas?????

 

[AccessMoron]

unless you are seaching for strUSERID

try

strSql = "SELECT tblUsers.UserID FROM TBLUSERS WHERE tblUsers.UserID = '" & strUSERID & "'"

 

[taShi]

Thanks *moron,

that worked for me!!!