Populating Listbox based on Combo box selection.

[olushow]

I am attempting to populate my Listbox with data, based on a selection in a combo box.
What I am finding is that the Listbox is being populated with the actual select statement.
Obviously, I'm doing something wrong, just don't know what it is.

Should I also be putting some kind of code in the Form_Load event? I don't
think I should, because the contents of the Listbox won't be determined until the after the combo box selection right?

Sample Code - For the after_update event of the combo box.

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

strWhere = "WHERE BPRNumber = '" & cboBpr & "'"
strSQL = "SELECT BPRNumber FROM tblBPRNumber" & strWhere
strSQL = strSQL & "ORDER BY BPRNumber;"

Me.txtArea = rs("Area")
Me.lstSops.RowSource = strSQL

End Sub

----------ListBox properties----
Rowsource type - Value list
MultiSelect - Extend.

Nothing really significant in the listbox properties after that.

 

[Remou]

You need to set RowSource type to Table/Query. You seem to be missing spaces in the SQL statement:
strSQL = "SELECT BPRNumber FROM tblBPRNumber " & strWhere
strSQL = strSQL & " ORDER BY BPRNumber;"

 

[Zion7]

Yes, I agree thast your syntax(spaces), are incorrect,
as Remou showed but also,
Why the recordset?
This MUST ERROR? Me.txtArea = rs("Area")
Why set the focus, when on the AfterUpdate event, it
ALREADY has it?

You could leave your list box as a Value List, and use
the AddItem/RemoveItem method of your list box?(VBA help)

 

[olushow]

I still get get it to work, now it populates the list box with just the BPR Value instead of all the associated records.

 

[Remou]

I am afraid I did not quite catch that. Can you show a sample of what you would like to see and what you have?

 

[olushow]

Scenario.

You have a drop down menu that contains all employees's last names. Once you select a employee's last name it displays all the Certification exams the employee has taken in the list box. I also want to be able to refresh the listbox.

 

[olushow]

All the data in question will be retrieved from the tblBPRNumber. What I am trying to do is that, once the listbox is populated, I will then append the contents in the listbox, and also the contents of three other text boxes within the same form, to anothe table. - This I can do, but I just need assistance in getting the listbox populated.

 

[Remou]

What I am failing to unserstand is your comment that it shows:
"BPR Value instead of all the associated records"

Your SQL does not show value, it shows number:

[tt]strWhere = "WHERE BPRNumber = '" & cboBpr & "'"
strSQL = "SELECT BPRNumber FROM tblBPRNumber " & strWhere
strSQL = strSQL & " ORDER BY BPRNumber;"[/tt]

Also, it is limited by a Where statement. I thought the easiest way to discuss some of these matters would be with a small section of the data you wished to show. The SQL statement above is only selecting one field from the table, did you, perhaps, wish to select two? If so, do you wish to show both? And so on.

 

[olushow]

What I am trying to do is eliminate the need for forty text boxes on a form. In previous posts, I have had to deal with constant reminders that my database is lacking sufficient normalization but we can talk about that another time.

Scenario

I have a table called tblBPRNumber, the PK is BPRnumber, and also has other fields lablelled SOP01 through SOP35.

What I am trying to do on the form is have the combo box populated with the BPRNumber field values;(...That's the easy part), then have the other fields SOP1 thorough SOP35 appear in the listbox.

And then I can pretty much take care of things from that point.

NB - BPRNumber field is alphanumerical.

 

[Remou]

Try:
[tt]strWhere = "WHERE BPRNumber = '" & cboBpr & "'"
strSQL = "SELECT * FROM tblBPRNumber " & strWhere
strSQL = strSQL & " ORDER BY BPRNumber;"

Listbox Properties:
RowSourceType : Table/Query
RowSource : SELECT ... FROM tblBPRNumber;
ColumnCount : 36 '35 S--- fields & the BR Number field
ColumnWidths : 0;2;2;2 ... etc, or other suitable value
BoundColumn : 1[/tt]

I think the easiest way is to build the listbox the way you want it, and then fiddle with the code.

 

[olushow]

That worked, but how do I get to display the data in one column,? What I currently have is the listbox displaying the data in a row.

 

[Remou]

Ok. Though a sample would have been nice.

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

set rs=CurrentDB.OpenRecordset(strSQL)
If rs.eof Then
  'No data
   Exit sub
Else
   rs.MoveLast
   If rs.RecordCount<>1 Then
      'Too much data
      Exit sub
   Else
      For i = 0 To rs.Fields.count - 1
         strValues= strValues & rs.Fields(i) & ";"
      Next
      strValues=Left(strValues,Len(strValues)-1)
   End If
End If

Me.List0.RowSource = strValues

[tt]Listbox Properties:
RowSourceType : Value List
RowSource : What;What;What
ColumnCount : 1
ColumnWidths : 2
BoundColumn : 1[/tt]

 

[olushow]

Thanks! It worked..
FYI, - I did not set anything in the RowSource property of the Listbox

 

[olushow]

I am getting the following error message: Runt tim error 3265. Item Not found in this collection.

When I attempt to append the contents of my listbox to a table -tblLotNumbers.

Here is my Sample Code.

Private Sub btnSubmit_Click()
Set rs = CurrentDb.OpenRecordset("tblLotNumber")

rs.AddNew
rs!ProcessDate = Me.ProcessDate
rs!LotNumber = Me.txtLotNumber
For intCount = 1 To lstSops.ListCount
**Problem Line***rs.Fields("Sop" & Format(intCount, "00")) = Me.lstSops.ItemData(intCount - 1)***Problem line***

Next intCount
rs.Update
MsgBox ("Lot Number " & Me.txtLotNumber & " was successfully added")
End Sub

I used the same code in another form, but in that instance, all the values in the listbox were Sop01......Sop25.

This code allows me to append data to fields with that same name type (Sop##). But in this instance I have other field names like BPRNumber, and Area. Is there a way that I can limit the contents of the Listbox to just show all the values for Sop1 ........ Sop20