Access VBA - Loop through table fields -> update Listbox

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hi there,

I have an Access 2003 database. In 2 of my forms i have a listbox which I
want to update/populate using VBA code.

Basically, i want to lookup the current record in a table and loop through
the fields. Whenever a field = TRUE, i want to add the relevant field name to
the List box

this is an example of the table (its called constituent details, sorry if it
doesn't come out clearly):


CaseID RIC BMI Aus BMI NZ BMI Hong Kong Asia 50 Asia All Stars SEA 40 Water
Cavam CavamK Vietnam 10
35 PPT.AX TRUE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE
36 DXS.AX TRUE FALSE FALSE TRUE FALSE FALSE TRUE FALSE FALSE TRUE
42 QBE.AX TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE
44 WYL.AX TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE
48 ARA.AX FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
50 JHX.AX TRUE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE
53 RIO.AX TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
56 SGP.AX TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE


i.e. if the current record is CaseID = 35 then the List box should populate
with 'BMI Aus', 'Asia 50' and 'Cavam'



THis is the code i have thought about. If would be great if anyone could
tell me where i'm going wrong as this does not seem to be working...







Public Sub updIndex1(lst As ListBox, strFormName As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT CaseID FROM ConstituentDetails WHERE CaseID = [Forms].[" &
strFormName & "].[CaseID] ""

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
With rst
For i = 0 To .Fields.count - 1
If .Fields(i).value = True Then
lst.AddItem .fields(i).Name
End If
Next

End With

End Sub
 
Probably not what you want to hear, but your table isn't normalized. You've
got a repeating group, which is why you're having problems.

You've got a many-to-many relationship (one case can have many constituents,
one constituent can have many cases): you should have three tables: one that
has a single row for each case, one that has a single row for each
constituent and one that resolves the many-to-many by containing the primary
keys for the other two tables.

By modelling the data correctly, your problem becomes trivial.
 
Back
Top