Using variables to access recordset values 1

[slames]

Hi, I hope someone will be able to help with on this. I have a dao recordset for which the sql is created on the fly dependent on certain criteria like this:

sqlStringIssueCode = "Select [" & IssueID & "],[" & issueselect & "] from [" & TableName & "] where [" & IssueID & "] = " & graceStartIssue


where IssueID etc are variables equating to the column names.

I now want to access the one of the recordset objects but I do not know the name of the column, as this is contained in a variable and will change each time the code is run.

Is there a way to use the variable in conjunction with the rs!column name command or another way of accessing the value in this column, as I can't see a way of getting to it at the moment.

Thanks in advance.

Stef

 

[RoyVidar]

Try the following syntax:

[tt]rs.fields(IssueID).value
rs.fields(issueselect).value
...[/tt]

Roy-Vidar

 

[slames]

Hi, thanks for your suggestion, but using this syntax gives me the following error:

method value of object '_Field' failed.

Any ideas what might be causing this?

Thanks

Stef

 

[slames]

Hi Again - when I look in the object browser at the item recordset, and then select fields, value is not a member of fields, the only choices are append, count, delete or refresh, am I missing a reference or something?

Thanks

Stef

 

[RoyVidar]

When you reference the fields collection through either an index (ordinal position) or the litteral column name, you are referring a field object. The field object properties does include a .Value property, though, since it is default, not all developers use it.

Copy this into a module, set a reference to Microsoft DAO 3.# Object Library (in VBE - Tools | References), if it's not alredy set (correct any typos;-)), replace <sometable> with the table name of one of your tables, study the results in the immediate pane (ctrl+g)

[tt]dim rs as dao.recordset
dim lngCounter as long
set rs=currentdb.openrecordset(<sometable>)
if ((not rs.bof) and (not rs.eof)) then
for lngCounter = 0 to rs.fields.count-1
debug.print rs.fields(lngCounter).name
next lngCounter
do while not rs.eof
for lngCounter = 0 to rs.fields.count-1
debug.print rs.fields(lngCounter).value
next lngCounter
rs.movenext
loop
end if
rs.close
set rs=nothing[/tt]

If you're using special characters, spaces and other not encouraged stuff as part of field names, you may have to use brackets.

[tt]rs.fields("[" & IssueID & "]").value
rs.fields("[" & issueselect & "]").value[/tt]

Roy-Vidar

 

[slames]

Thanks for all your help on this, I simplified my request to read

rs(variable) and it worked fine, I didn't realise it was the default.

Thanks

Steph