Listbox show all records unless comboboxes populated

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

I have a form which contains two comboboxes, a textbox and a listbox. The intent of the listbox is to show all records in the table tied to the form unless one or both of the comboboxes are populated in which case it will then filter the records displayed in the listbox to only those which contain the values in the comboboxes. The problem that I am running into is that when I open up the form, no records are displayed in the listbox and the listbox only displays records when both comboboxes are populated. The query behind the listbox displays all records though when the comboboxes are empty but not the listbox itself. The SQL code for the listbox is the following:

Code: Select all

SELECT tblTubeSerial.TubeSerialID, tblTubeSerial.TubeSerial, tblTubeSerial.SupplierID, tblTubeSerial.BlockID
FROM tblTubeSerial
WHERE (((tblTubeSerial.SupplierID)=[forms]![frmTubeSerial]![cmbSupplier]) AND ((tblTubeSerial.BlockID)=[forms]![frmTubeSerial]![cmbBlock])) OR (((tblTubeSerial.BlockID)=[forms]![frmTubeSerial]![cmbBlock]) AND (([Forms]![frmTubeSerial]![cmbSupplier]) Is Null)) OR (((tblTubeSerial.SupplierID)=[forms]![frmTubeSerial]![cmbSupplier]) AND (([forms]![frmTubeSerial]![cmbBlock]) Is Null)) OR ((([Forms]![frmTubeSerial]![cmbSupplier]) Is Null) AND (([forms]![frmTubeSerial]![cmbBlock]) Is Null));
and the VBA to requery the listbox based on the combobox values is the following:

Code: Select all

Private Sub cmbSupplier_AfterUpdate()
    'requeries list to only show tube serials for selected supplier amongst all blocks or selected block (if already populated)
    Me.lstSupplierSerials.Requery
End Sub
 
Private Sub cmbBlock_AfterUpdate()
    'requeries list to only show tube serials for selected block
    Me.lstSupplierSerials.Requery
End Sub
I've also tried adding textboxes which take the SupplierID and BlockID from the comboboxes and then use those to requery the listbox but again it only shows records if both comboboxes are populated and nothing if one or both of them are empty.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Change the Row Source of the list box to

Code: Select all

SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial
And change the code behind the combo boxes as follows:

Code: Select all

Private Sub cmbSupplier_AfterUpdate()
    UpdateListBox
End Sub
 
Private Sub cmbBlock_AfterUpdate()
    UpdateListBox
End Sub

Private Sub UpdateListBox
    Dim strSQL As String
    Dim strWhere As String
    strWhere = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial"
    If Not IsNull(Me.cmbSupplier) Then
        strWhere = " AND SupplierID=" & Me.cmbSupplier
    End If
    If Not IsNull(Me.cmbBlock) Then
        strWhere = strWhere & " AND BlockID=" & Me.cmbBlock
    End If
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & Mid(strWhere, 6)
    End If
    Me.lstSupplierSerials.RowSource = strSQL
End Sub
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

Hans, thank you for your quick response. I incorporated the changes that you suggested but it still isn't working right. Actually, now with those changes, not only does the listbox not populate when I have nothing in either combobox, but it doesn't show any records when one or both of the comboboxes are populated.

One other thing I noticed. First, when the form first loads it loads to a new record and when it runs the UpdateListBox code to show all records (I know you didn't mention doing this but I wasn't sure if I need to), VBA is showing the combobox values as zero not null.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Are the combo boxes bound to a field in the record source of the form?
Or has their Default Value been set to 0?
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

the control source is SupplierID and BlockID from tblSupplier and tblBlocks, respectively. Neither combobox has a default value set when I look at the property sheet yet even when I populate one or the other comboboxes just to make a dummy record in tblTubeSerials, it is storing the empty combobox as a zero rather than a null value.

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

MHMMM this could be it!! I just went back and looked at the design view of tblTubeSerials and because SupplierID and BlockID are numbers, the default value is set there as 0.

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

my apologies, that was supposed to say control source from tblTubeSerials. Row source is from tblSupplier and tblBlocks.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Shouldn't the combo boxes be unbound, i.e. with a blank Control Source? If they are bound, selecting a value from the combo box will modify the current record!
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

if the are not bound, how would I modify the existing record if necessary?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

OK, so it's intentional; I didn't know that. Change the code of UpdateListBox as follows:

Code: Select all

Private Sub UpdateListBox
    Dim strSQL As String
    Dim strWhere As String
    strWhere = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial"
    If Me.cmbSupplier <> 0 Then
        strWhere = " AND SupplierID=" & Me.cmbSupplier
    End If
    If Me.cmbBlock <> 0 Then
        strWhere = strWhere & " AND BlockID=" & Me.cmbBlock
    End If
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & Mid(strWhere, 6)
    End If
    Me.lstSupplierSerials.RowSource = strSQL
End Sub
You may also want to add an On Current event procedure for the form:

Code: Select all

Private Sub Form_Current()
    UpdateListBox
End Sub
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

I think I just noticed something...is there a reason you didn't specify anything for strSQL until the last line before the subroutine ends? Did you intend to put strSQL = after the Dim callouts?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Aargh - my mistake! Thanks for pointing it out.

Code: Select all

Private Sub UpdateListBox
    Dim strSQL As String
    Dim strWhere As String
    strSQL = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial"
    If Me.cmbSupplier <> 0 Then
        strWhere = " AND SupplierID=" & Me.cmbSupplier
    End If
    If Me.cmbBlock <> 0 Then
        strWhere = strWhere & " AND BlockID=" & Me.cmbBlock
    End If
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & Mid(strWhere, 6)
    End If
    Me.lstSupplierSerials.RowSource = strSQL
End Sub
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Or perhaps

Code: Select all

Private Sub UpdateListBox
    Dim strSQL As String
    Dim strWhere As String
    strSQL = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial"
    If Val(Nz(Me.cmbSupplier, 0)) <> 0 Then
        strWhere = " AND SupplierID=" & Me.cmbSupplier
    End If
    If Val(Nz(Me.cmbBlock, 0)) <> 0 Then
        strWhere = strWhere & " AND BlockID=" & Me.cmbBlock
    End If
    If strWhere <> "" Then
        strSQL = strSQL & " WHERE " & Mid(strWhere, 6)
    End If
    Me.lstSupplierSerials.RowSource = strSQL
End Sub
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

Ok well I think I'm doing something wrong...I have attached a copy of the test database that I'm trying this method out on so you can see what I've done. Based on adding a breakpoint at the beginning of UpdateListBox so I can try and debug the code, looks like it's only running on the On Load event for the form and not on the AfterUpdate events for the comboboxes (tried it with just selecting an option from the combobox as well as tabbing to the next field on the form). I also changed part of your code where it defines the strSQL at the end because I think you meant to say Mid(strWhere, 4) not Mid(strWhere, 6) because strWhere starts with " AND ". So my method with give you "strSQL = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial" WHERE SupplierID..." whereas Mid(strWhere, 6) would give you "strSQL = "SELECT TubeSerialID, TubeSerial, SupplierID, BlockID FROM tblTubeSerial" WHERE upplierID..."
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

There is something strange going on, but I can't get my finger behind it yet.
I have to do some other things now, but I'll get back to you later today.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

OK, I found the cause of the problem: the table is named tblTubeSerials instead of tblTubeSerial, and its first field is named SerialID instead of TubeSerialID.
See the attached version.

TestSerials.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Listbox show all records unless comboboxes populated

Post by EnginerdUNH »

There is still nothing happening after the comboboxes are updated...cmbSupplier_AfterUpdate should run the UpdateListBox subroutine and set focus to cmbBlock, cmbBlock_AfterUpdate should run UpdateListBox and set focus to txtTubeSerial.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Listbox show all records unless comboboxes populated

Post by HansV »

Sorry, I missed that. You had created cmbSupplierID_AfterUpdate() and Private Sub cmbBlockID_AfterUpdate()
It should have been Private Sub cmbSupplier_AfterUpdate() and Private Sub cmbBlock_AfterUpdate()

You have to be careful to use the correct names!

TestSerials.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans