Populate Listbox base on Combo Box Selection.

[Olu Solaru]

Scenario - I have a table called tblBPRnumbers - PK is BPR number field, and
the other fields are labelled Sop01 Through Sop30.
I have a form with a combo box and a listbox. The combo box contains the
values from the BPRNumber field.

What I am trying to do is that once I select a value from the combo box
(BPrNumber,). I would like the fields Sop01 through Sop30 to populate in the
Listbox. The way I have it working now, actually displays the whole row. But
what I want is for the listbox to have just one column , populated with the
values of Sop01 through Sop30.
Am I using some sort of Array, here?
.. - Access 2003 ; VBA; VB.NET

Current code sample

Private Sub cboBpr_AfterUpdate()

On Error Resume Next

cboBpr.SetFocus

Dim db As DAO.Database
Dim strSQL As String
Dim strWhere As String
Dim rs As DAO.Recordset
Dim intFields As Integer
Dim intCounter As Integer

strWhere = "WHERE BPRNumber = '" & cboBpr & "'"
strSQL = "SELECT * FROM tblBPRNumber " & strWhere


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


If Not rs.BOF Then
Me.txtArea = rs("Area")
Me.lstSops.RowSource = strSQL
End If

rs.Close
Set rs = Nothing
db.Close
End Sub


listbox Properties

RowSource Type - table/query
Row Source Select tblPRNumber.BPRNumber, tblBPRNumber.Sop01
.........tblBPRNumber.Sop20

 

[Jeff Boyce]

Olu

So, are you saying that you have a table with fields named "Sop01, Sop02,
.... Sop30"? If so, this may be a "repeating fields" construction that is
necessary for spreadsheets, but causes considerable headaches when used in
an Access relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Olu Solaru]

Fields are repeating but not within the same record.

 

[Jeff Boyce]

I suspect you are referring to values, but your table structure seems to
have "repeating fields", neither necessary nor a good idea in a relational
database.

Regards

Jeff Boyce
Microsoft Office/Access MVP