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):
CaseIDRICBMI AusBMI NZBMI Hong KongAsia 50Asia All StarsSEA 40WaterCavamCavamKVietnam 1035PPT.AXTRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSEFALSE36DXS.AXTRUEFALSEFALSETRUEFALSEFALSETRUEFALSEFALSETRUE42QBE.AXTRUEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSE44WYL.AXTRUEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSE48ARA.AXFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE50JHX.AXTRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSEFALSE53RIO.AXTRUEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSE56SGP.AXTRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
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
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):
CaseIDRICBMI AusBMI NZBMI Hong KongAsia 50Asia All StarsSEA 40WaterCavamCavamKVietnam 1035PPT.AXTRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSEFALSE36DXS.AXTRUEFALSEFALSETRUEFALSEFALSETRUEFALSEFALSETRUE42QBE.AXTRUEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSE44WYL.AXTRUEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSE48ARA.AXFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE50JHX.AXTRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSEFALSE53RIO.AXTRUEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSE56SGP.AXTRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
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