looping through recordset

[Mark]

Hi all,

What would be an efficient way within a function to loop through a recordset
of distinct text values (record 1 = text1, record 2 = text2, etc.) with the
result displayed as follows:

If only one record:
fncMyFunction = "text1." (that's text1 followed by a period)

If multiple records and rst.recordcount = n:
fncMyFunction = "text1, text2, ... , and text-n." (commas separate each
record except for the last which is preceded with the word "and" and is
followed by a period).

[the function produces a string which can be incorporated elsewhere]

Thanks for any advice.
Mark

 

[Jeff Boyce]

Mark

"looping through a recordset" is a very compute-intensive way to get things
done... sometimes the only way, but then again ...

Have you looked into using a query to process the recordset as a set?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JimBurke via AccessMonster.com]

I don;t know if you want to pass the recordset to the function or just open
in the function. I'll code this to open it in the function. I'm setting it up
for ADO - the OpenKeyset parm allows you to access the record count.
Depending on how you open a recordset, you may or may not be able to get a
valid recordcount. This can also be done using a DAO recordset, but I'm not
sure about how you get the recordcount with DAO. I'm calling it
'YourFunction' - rename it to what you want and replace 'YourFunction' with
the name you give it. Also make sure you fill in the field name that has the
text value where noted. Something like this should work:

Public Function YourFunction() as string
on error goto HandleError

dim rst as ADODB.Recordset
dim myRecordCount as long
dim recordNumber as long

YourFunction = vbnullstring
set rst = New ADODB.Recordset
rst.Open "YourTableName", cn, adOpenKeyset, adLockOptimistic
if rst.eof then
msgbox "No records were found. - or whatever message you'd want here"
set rst = nothing
exit function
end if

rst.movelast
rst.movefirst
myCount = rst.recordcount
recordNumber = 0

while not rst.eof
recordNumber = recordNumber + 1
if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "
end if
end if
YourFunction = YourFunction & rst!FieldName ' change this to the
field name!
rst.movenext
wend
rst.close
set rst = nothing
exit function

HandleError:
' do whatever you want inc ase of an error here - display msgbox, etc

End Function

Hi all,

What would be an efficient way within a function to loop through a recordset
of distinct text values (record 1 = text1, record 2 = text2, etc.) with the
result displayed as follows:

If only one record:
fncMyFunction = "text1." (that's text1 followed by a period)

If multiple records and rst.recordcount = n:
fncMyFunction = "text1, text2, ... , and text-n." (commas separate each
record except for the last which is preceded with the word "and" and is
followed by a period).

[the function produces a string which can be incorporated elsewhere]

Thanks for any advice.
Mark

 

[Mark]

Thanks, Jim.
I'll have to work through this. I haven't used 'Wend' before, but I would
have expected the line:

YourFunction = YourFunction & rst!FieldName

to have come earlier in the procedure. For example, I can't see where
YourFunction has been changed from "vbnullstring" in the case where there's
only one record --

if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "

.... where does the value of YourFunction come from on the last line above?

Thanks for your help.
P. S. I'm using DAO (Access97 & 2002), but I can adjust as needed.
Mark

I don;t know if you want to pass the recordset to the function or just open
in the function. I'll code this to open it in the function. I'm setting it up
for ADO - the OpenKeyset parm allows you to access the record count.
Depending on how you open a recordset, you may or may not be able to get a
valid recordcount. This can also be done using a DAO recordset, but I'm not
sure about how you get the recordcount with DAO. I'm calling it
'YourFunction' - rename it to what you want and replace 'YourFunction' with
the name you give it. Also make sure you fill in the field name that has the
text value where noted. Something like this should work:

Public Function YourFunction() as string
on error goto HandleError

dim rst as ADODB.Recordset
dim myRecordCount as long
dim recordNumber as long

YourFunction = vbnullstring
set rst = New ADODB.Recordset
rst.Open "YourTableName", cn, adOpenKeyset, adLockOptimistic
if rst.eof then
msgbox "No records were found. - or whatever message you'd want here"
set rst = nothing
exit function
end if

rst.movelast
rst.movefirst
myCount = rst.recordcount
recordNumber = 0

while not rst.eof
recordNumber = recordNumber + 1
if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "
end if
end if
YourFunction = YourFunction & rst!FieldName ' change this to the
field name!
rst.movenext
wend
rst.close
set rst = nothing
exit function

HandleError:
' do whatever you want inc ase of an error here - display msgbox, etc

End Function

Hi all,

What would be an efficient way within a function to loop through a recordset
of distinct text values (record 1 = text1, record 2 = text2, etc.) with the
result displayed as follows:

If only one record:
fncMyFunction = "text1." (that's text1 followed by a period)

If multiple records and rst.recordcount = n:
fncMyFunction = "text1, text2, ... , and text-n." (commas separate each
record except for the last which is preceded with the word "and" and is
followed by a period).

[the function produces a string which can be incorporated elsewhere]

Thanks for any advice.
Mark

 

[Mark]

Jeff,
Not sure I understand the question. Are you suggesting an expression in a
query? Maybe I've used the wrong language -- "recordset", or just didn't
explain very well what I'm trying to do.
Mark

Mark

"looping through a recordset" is a very compute-intensive way to get things
done... sometimes the only way, but then again ...

Have you looked into using a query to process the recordset as a set?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

What would be an efficient way within a function to loop through a
recordset
of distinct text values (record 1 = text1, record 2 = text2, etc.) with
the
result displayed as follows:

If only one record:
fncMyFunction = "text1." (that's text1 followed by a period)

If multiple records and rst.recordcount = n:
fncMyFunction = "text1, text2, ... , and text-n." (commas separate each
record except for the last which is preceded with the word "and" and is
followed by a period).

[the function produces a string which can be incorporated elsewhere]

Thanks for any advice.
Mark

 

[JimBurke via AccessMonster.com]

Wend is just the statement that ends a While loop.

The line

YourFunction = YourFunction & rst!FieldName

can't come until after you see whether a ',' or 'and' is needed. It is
executed every time thru the loop, regardless. It is after the If-Then-Else
that checks recordNumber. If there is only one record, then the function just
returns the single value that is in that one record. The If-The-Else isn't
executed unless there is more than one record - if there is only one, you
don't want a ',' or 'and'. You must be reading the code wrong.

Thanks, Jim.
I'll have to work through this. I haven't used 'Wend' before, but I would
have expected the line:

YourFunction = YourFunction & rst!FieldName

to have come earlier in the procedure. For example, I can't see where
YourFunction has been changed from "vbnullstring" in the case where there's
only one record --

if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "

... where does the value of YourFunction come from on the last line above?

Thanks for your help.
P. S. I'm using DAO (Access97 & 2002), but I can adjust as needed.
Mark

I don;t know if you want to pass the recordset to the function or just open
in the function. I'll code this to open it in the function. I'm setting it up

[quoted text clipped - 67 lines]

 

[JimBurke via AccessMonster.com]

One thing I did forget - right after the Wend you need this:

YourFunction = YourFunction & "."

to add the period at the end

Thanks, Jim.
I'll have to work through this. I haven't used 'Wend' before, but I would
have expected the line:

YourFunction = YourFunction & rst!FieldName

to have come earlier in the procedure. For example, I can't see where
YourFunction has been changed from "vbnullstring" in the case where there's
only one record --

if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "

... where does the value of YourFunction come from on the last line above?

Thanks for your help.
P. S. I'm using DAO (Access97 & 2002), but I can adjust as needed.
Mark

I don;t know if you want to pass the recordset to the function or just open
in the function. I'll code this to open it in the function. I'm setting it up

[quoted text clipped - 67 lines]

 

[Jeff Boyce]

Mark

"how" depends on "what", and I don't understand what (data, or data/table
structure) you are working with.

I asked whether you considered using a query to see if you had considered
using a query ... and I don't know if it is appropriate because I don't know
enough about your situation yet.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Not sure I understand the question. Are you suggesting an expression in a
query? Maybe I've used the wrong language -- "recordset", or just didn't
explain very well what I'm trying to do.
Mark

Mark

"looping through a recordset" is a very compute-intensive way to get things
done... sometimes the only way, but then again ...

Have you looked into using a query to process the recordset as a set?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

What would be an efficient way within a function to loop through a
recordset
of distinct text values (record 1 = text1, record 2 = text2, etc.) with
the
result displayed as follows:

If only one record:
fncMyFunction = "text1." (that's text1 followed by a period)

If multiple records and rst.recordcount = n:
fncMyFunction = "text1, text2, ... , and text-n." (commas separate each
record except for the last which is preceded with the word "and" and is
followed by a period).

[the function produces a string which can be incorporated elsewhere]

Thanks for any advice.
Mark

 

[FPS, Romney]

Oops, my apologies, Jim ... I did misread the code. Thank you for your help!
Mark

One thing I did forget - right after the Wend you need this:

YourFunction = YourFunction & "."

to add the period at the end

Thanks, Jim.
I'll have to work through this. I haven't used 'Wend' before, but I would
have expected the line:

YourFunction = YourFunction & rst!FieldName

to have come earlier in the procedure. For example, I can't see where
YourFunction has been changed from "vbnullstring" in the case where there's
only one record --

if recordNumber > 1 then
if recordNumber = myCOunt then
YourFunction = YourFunction & "and "
else
YourFunction = YourFunction & ", "

... where does the value of YourFunction come from on the last line above?

Thanks for your help.
P. S. I'm using DAO (Access97 & 2002), but I can adjust as needed.
Mark

I don;t know if you want to pass the recordset to the function or just open
in the function. I'll code this to open it in the function. I'm setting

it up
[quoted text clipped - 67 lines]

Thanks for any advice.
Mark