Variable Recordset Fields

[mkjets]

I would be grateful if someone could give me a direction in solving
this problem. Here is the simplified setup for the code that I am
trying to run in an Access form. STRFLD is an array that I assigned
earlier. I am trying to access for example rs!Fields!AREG or
rs!fields!ASER depending on what STRFLD was supplied earlier in the
code. To save space I shortened my SQL and code quite a bit, but I hope
this will give you a picture of what I am trying to do.

Dim db As Database
Dim rs As Recordset

STRSQL = "SELECT mms.[LINKNUM], TableA.[SER] AS ASER, TableB.[SER] AS
BSER FROM TableA LEFT JOIN TableB ON TableB.LINKNUM = TableA.LINKNUM
ORDER BY TableA.SER;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(STRSQL, dbOpenDynaset)
icol = 1
I have tried the following

strdat = rs!Fields("A" & STRFLD(icol)) ' I get a run-time error
3265 Item not found

strdat = rs.fields(fldnm) ' where fldnm = "A" & STRFLD(icol) again
I get error 3265

strdat = Controls("rs!Fields(" & fldnm & ")") ' Get a error 2465 can't
find field 'rs!Fields(AREG)'

I have even tried CallByName and countless other combinations of the
above examples. I know the field exists in the database. I know
STRFLD(icol) was set right because on debug I can see it has the right
value. I have searched help as well as many newsgroup postings and
have not found my solution. Have I overlooked something? Is it
possible to have a dynamic field in a recordset?

Thank you very much!

-Matt

[Rich P]

Hi Matt,

not completely clear on what you are trying to do, but here is a shot in
the dark:

Dim arrFlds As Variant, DB As DAO.Database, RS As DAO.Recordset, i As
Integer, j As Integer, k As Integer, strSql As String

arrFlds = Array("fld1", "fld2", fld3",..."fld10")
if something then
i = somenum
j = anothernum
k = someothernum
End If

strSql = "Select t1." & arrFlds(i) & ", t1." _
& arrFlds(j) & ", t2." & arrFlds(k) & " From tbl1 t1 " _
& "Join tbl2 t2 On t1.ID = t2.ID Where..."

Set DB = CurrentDB
Set RS = DB.OpenRecordSet(strSql")
Do While Not RS.EOF
...
RS.MoveNext
Loop
Just remember that a sql string is just a string, and you can build it
any way that you want. You can concatenate whatever you want to your
string - just make sure that it is a valid sql string. Then you can
assign it as a recordsource to your recordset object. You build the
string first - dynamically - on the fly - whatever - then you assign it.

Note: if your string is a valid sql string, you can retrieve the formed
string by using Debug.Print strSql and copy it from the debug window.
Then you can paste it into the sql window of the query builder tool and
run the query. If it is correct, the query will run. If it is not
correct - well - the query won't run, but you could switch the sql
window to the query graphical view and that should/might tell you where
the problem is.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[mkjets]

Rich,

Thank you for your response! I followed your advice and rechecked the
SQL which I thought I did earlier. I found that when I tried to
simplify what I was doing I somehow undid a change i made previously to
the SQL variable that addressed one of the fields I was trying to
reference. The field I was referenceing in fact was left out of the
SQL. I fixed the SQL string and now the following works -

dim fldnm as string
fldnm = "A" & STRFLD(icol)
astring = rs.Fields(fldnm)

Thank you again for your response Rich, you got me to sit back and look
at what I was doing and recheck the obvious (that I wrongly assumed I
recently checked)! I will try to double check all of my code next time
before I pose a question! THANK YOU!

Best,

Matt