Two tables and a collection

[kimtp]

Trying to gather data from two tables, place it into a collection and pass it to a flexgrid. Gather the data in class connected to a database. Here is the code for the db connection class:

Public Sub Display(ByRef mcol As Collection, byref aCol as Collection)
Dim displayMem As CMember
Dim displayAttend As New CAttending
sSQL = "SELECT Members.ID, Members.LastName, Members.Firstname, " & _
" Members.Address, Members.City, Members.State,Members.Zip, " & _
" Attending.Deceased FROM Members INNER JOIN Attending ON " & _
" Members.ID = Attending.ID ORDER BY Members.LastName"

With rsmembers
Do Until .EOF
set displayMem = New CMember
displayMem.ID = .Fields("ID&quot
displayMem.Lastname = .Fields("Lastname&quot
displayMem.Firstname = .Fields("Firstname&quot
displayMem.Address = .Fields("Address&quot
displayMem.City = .Fields("City&quot
displayMem.State = .Fields("State&quot
displayMem.Zip = .Fields("Zip&quot
mcol.Add displayMem
rsMembers.MoveNext
Loop
End If
End With

With rsAttending
Do Until rsAttending.EOF
Set displayAttend = New CAttending
displayAttend.Deceased = .Fields("Deceased&quot
mcol.Add displayAttend
.MoveNext
Loop
End With
end sub

On the display side or user interface portion:

For Each mMember In mcol
For Each mAttend In aCol
bDead = mAttend.Deceased
sName = mMember.Lastname & ", " & mMember.Firstname
sAddress = mMember.Address
sCity = IIf(mMember.City = "", "", mMember.City & ", " & mMember.State & " " & mMember.Zip)
sBuildString = sName & vbTab & sAddress & vbTab & sCity & vbTab & bDead
fgdMembers.AddItem sBuildString
MsgBox mMember.Lastname & " " & mAttend.Deceased
Next mAttend
Next mMember

This shows the same record twice, once false and once true for deceased. Once that riddle is solved I would like to display "D" or "".

Do I need two collections?

Thanx for your help.

 

[marcsoft]

on the flex grid, are you using the textarray feature to load data into each cell? msflexgrid.textarray(cell)=sbuildstring, where cell is an integer, starting with zero as the upper left cell, moving left to right, top to bottom. you can calculate the cell address, and along with the resize col property, you can put quite large strings in a cell and allow the user to resize a given col. dont forget about fixed cols and rows, so that you can use the top rows(s) and left col(s) for headers and etc. if you fix a row/col, you do have to take that into consideration when calculating the cell address. flexgrids can handle about 150k cells.

 

[BlackKnight]

Hi,
I would try something like this:

Public Sub Display(ByRef mcol As Collection, ByRef acol As Collection)

Dim displayMem As CMember
Dim displayAttend As New CAttending
Dim mmember As Object, mattend As Object
Dim bdead As String, sname As String, saddress As String, scity As String, sBuildString As String

sSQL = "SELECT Members.ID, Members.LastName, Members.Firstname, " & _
" Members.Address, Members.City, Members.State,Members.Zip, " & _
" Attending.Deceased FROM Members INNER JOIN Attending ON " & _
" Members.ID = Attending.ID ORDER BY Members.LastName"

If mcol Is Nothing Then Set mcol = New Collection
If acol Is Nothing Then Set acol = New Collection
With rsMembers
Do Until .EOF
Set displayMem = New CMember
displayMem.ID = .Fields("ID&quot
displayMem.Lastname = .Fields("Lastname&quot
displayMem.Firstname = .Fields("Firstname&quot
displayMem.Address = .Fields("Address&quot
displayMem.City = .Fields("City&quot
displayMem.State = .Fields("State&quot
displayMem.Zip = .Fields("Zip&quot
mcol.Add displayMem
rsMembers.MoveNext
Loop
End With
'Note that the Attending ID is being included in the acol
'collection as well.
With rsAttending
Do Until rsAttending.EOF
Set displayAttend = New CAttending
displayAttend.ID = .Fields("ID&quot
displayAttend.Deceased = .Fields("Deceased&quot
acol.Add displayAttend
.MoveNext
Loop
End With
'On the display side or user interface portion:
For Each mmember In mcol
For Each mattend In acol
If mmember.ID = mattend.ID Then
bdead = mattend.Deceased
sname = mmember.Lastname & ", " & mmember.Firstname
saddress = mmember.Address
scity = IIf(mmember.City = "", "", mmember.City & ", " & mmember.State & " " & mmember.Zip)
sBuildString = sname & vbTab & saddress & vbTab & scity & vbTab & bdead
fgdMembers.AddItem sBuildString
MsgBox mmember.Lastname & " " & mattend.Deceased
End If
Next mattend
Next mmember
End Sub

Have a good one!
BK