SQL with variables in currentDb.OpenRecordset (1 Viewer)

[Chrisopia]

I am trying to create a function which will use variables to extract which fields are needed from an SQL statement and therefore retrieved via OpenRecordSet.

Public Function DoSomething(pName As String)
Dim PrtSthg As String
Dim pr As DAO.Recordset
Dim tray As String

PrtSthg = "SELECT ID, Branch, " & pName & " FROM tblBranch WHERE ID=" & BranchhID & ";"
Set pr = CurrentDb.OpenRecordset(PrtSthg)

tray = pr!pName
msgBox tray

I know usually, with recordset, it's as easy as pointing to the field using '!'
For example, if I used the function:

DoSomething("truck")

It should somehow retrieve 'pr!truck' and message box me the value.

with pr!pName - I get "Item not found in this collection"
with pr! & pName - I get "Type-declaration character foes not match declared data type"

How can I string the variable with the RecordSet pointer?

 

[JHB]

Try the below, (not tested).

tray = pr(pName)

 

[namliam]

Obviously pName isnt a column in this query, you are replacing in its value and its probably assigned a (semi-)random column name since you are not giving it an alias.

Try: pr.Fields(3)
As your field reference, this should work regardless of column name... or try assigning a column alias.

 

[Chrisopia]

pr(pName) - didn't work JHB, and neither did pr.Fields(3) namliam...

However, if I combined the two, I got pr.Fields(prName) ... and now it does

Maybe you two should become crime solving partners...

 

[pr2-eugin]

namliam's solution would have worked, provided the 3 was replaced with 2.. As the Field names referred by the DAO object are ZERO based.. So Field 1 would be referred as 0 and Field 2 as 1 and so on..

Apart from that, why not simply use DLookUp instead of opening Recordsets? That would be more neat in your situation?

 

[namliam]

I always fall for the ZERO based nonsense !