Creating a collection from an existing recordset or table

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Why do you need to create your own collection? If you want to move through
the records in a recordset, jist do this:
Set rs1 = db1.OpenRecordset("DBUsers")
Click to expand...
with rs1
if .eof and .bof ' a much better way to test for this.
' empty.
else
while not .eof
sgbox ![ThisField] & " " & ![ThatField]
.movenext
wend
endif
end with

If you really want to create your own collection, create it using:

dim TmpCollection as collection
set TmpCollection = new colection

then add elements to it using the Add method of the Cllection object. This
is described in online help.

HTH,
TC
(off for the day)
 
I wish to index through a collection using the For Each
Next method. I can create a record set from an existing
table I can not get the collection to display all the
records that are in the existing recordset. In the
section of code below rs1 will capture all of the records
from the table DBUsers, but I cannot transfer that
recordset to a collection to use in the For Each Next
statement. If I do not dimension the Tempcollection, I do
not get any "mismatch" errors in the For Next line, but I
only get the first record of the collection.

Any help is appreciated in advance.

Dim db1 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim Response As String
Dim tempuser As String
Dim TempChanges As String
Dim Index1, tempcount As Integer
Dim Tempcollection


UserName = Environ("USERNAME")
If IsNull(UserName) Then
UserName = "Unknown"
End If

Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("DBUsers") 'DBUsers is the
table with user names
tempcount = rs1.RecordCount 'Check to see if I have
got all the records
Set Tempcollection = rs1

For Each user In Tempcollection
tempuser = user
Set rs2 = db1.OpenRecordset("SELECT * FROM
Change_Tracker WHERE mslink like '" & Me!EF100 & "' AND
User like '" & tempuser & "'")
TempChanges = CurrentChanges
If rs2.RecordCount = 0 Then
 
I just use
if rst.eof then 'empty

as long it is just after the rst is opened, eof will only be true if
there are no records.
Of course its not valid if you have moved the pointer.


Why do you need to create your own collection? If you want to move through
the records in a recordset, jist do this:
Set rs1 = db1.OpenRecordset("DBUsers")
Click to expand...
with rs1
if .eof and .bof ' a much better way to test for this.
' empty.
else
while not .eof
sgbox ![ThisField] & " " & ![ThatField]
.movenext
wend
endif
end with

If you really want to create your own collection, create it using:

dim TmpCollection as collection
set TmpCollection = new colection

then add elements to it using the Add method of the Cllection object. This
is described in online help.

HTH,
TC
(off for the day)


Victor Mills said:
I wish to index through a collection using the For Each
Next method. I can create a record set from an existing
table I can not get the collection to display all the
records that are in the existing recordset. In the
section of code below rs1 will capture all of the records
from the table DBUsers, but I cannot transfer that
recordset to a collection to use in the For Each Next
statement. If I do not dimension the Tempcollection, I do
not get any "mismatch" errors in the For Next line, but I
only get the first record of the collection.

Any help is appreciated in advance.

Dim db1 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim Response As String
Dim tempuser As String
Dim TempChanges As String
Dim Index1, tempcount As Integer
Dim Tempcollection


UserName = Environ("USERNAME")
If IsNull(UserName) Then
UserName = "Unknown"
End If

Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("DBUsers") 'DBUsers is the
table with user names
tempcount = rs1.RecordCount 'Check to see if I have
got all the records
Set Tempcollection = rs1

For Each user In Tempcollection
tempuser = user
Set rs2 = db1.OpenRecordset("SELECT * FROM
Change_Tracker WHERE mslink like '" & Me!EF100 & "' AND
User like '" & tempuser & "'")
TempChanges = CurrentChanges
If rs2.RecordCount = 0 Then
Click to expand...
Click to expand...
 
Yes, agreed. I actually do it that way myself, also! But I've started to
change, because the bof/eof method is a bit more bulletproof. There is
nothing the programmer can do accidentally, get bof/eof both true on a
non-empty< recordset. But there >are< things the programmer can do
Click to expand...
accidentally, to get eof true on a non-empty recordset.

Cheers,
TC



Greg Kraushaar said:
I just use
if rst.eof then 'empty

as long it is just after the rst is opened, eof will only be true if
there are no records.
Of course its not valid if you have moved the pointer.


Why do you need to create your own collection? If you want to move through
the records in a recordset, jist do this:
Set rs1 = db1.OpenRecordset("DBUsers")
Click to expand...
with rs1
if .eof and .bof ' a much better way to test for this.
' empty.
else
while not .eof
sgbox ![ThisField] & " " & ![ThatField]
.movenext
wend
endif
end with

If you really want to create your own collection, create it using:

dim TmpCollection as collection
set TmpCollection = new colection

then add elements to it using the Add method of the Cllection object. This
is described in online help.

HTH,
TC
(off for the day)


Victor Mills said:
I wish to index through a collection using the For Each
Next method. I can create a record set from an existing
table I can not get the collection to display all the
records that are in the existing recordset. In the
section of code below rs1 will capture all of the records
from the table DBUsers, but I cannot transfer that
recordset to a collection to use in the For Each Next
statement. If I do not dimension the Tempcollection, I do
not get any "mismatch" errors in the For Next line, but I
only get the first record of the collection.

Any help is appreciated in advance.

Dim db1 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim Response As String
Dim tempuser As String
Dim TempChanges As String
Dim Index1, tempcount As Integer
Dim Tempcollection


UserName = Environ("USERNAME")
If IsNull(UserName) Then
UserName = "Unknown"
End If

Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("DBUsers") 'DBUsers is the
table with user names
tempcount = rs1.RecordCount 'Check to see if I have
got all the records
Set Tempcollection = rs1

For Each user In Tempcollection
tempuser = user
Set rs2 = db1.OpenRecordset("SELECT * FROM
Change_Tracker WHERE mslink like '" & Me!EF100 & "' AND
User like '" & tempuser & "'")
TempChanges = CurrentChanges
If rs2.RecordCount = 0 Then
Click to expand...
Click to expand...
Click to expand...
 
Back
Top