Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9873

Re: How to Make Null Selection in Combo Box Return All Values

Show all headers | View raw

On 04/09/2014 15:49:40, Anita Arnold wrote:
> I am using a combo box in the HEADER of a form to filter the results
> displayed in the DETAIL section of the form.
> 
> On the AFTER_UPDATE event for the combo box, I have the following code:
> 
> Private Sub cboSelectTester_AfterUpdate()
> Me.Filter = "TesterName = '" & Me.cboSelectTester & "'"
> Me.FilterOn = True
> 
> What I need now is to accommodate NO SELECTION in that combo box. If a
> user selects NOTHING, I want ALL records displayed in the DETAIL section
> of the form.
> 
> I have read through answers at several other sites -- and I just do not
> understand what they are telling me to do.
> 
> Please, if anyone could assist with this, I would be extremely greatful.
> I've wasted all morning on trying to figure this out!
> 

I use something like
SELECT Comments.Comment, Comments.CommentID FROM Comments INNER JOIN
BoughtSold ON Comments.CommentID = BoughtSold.CommentID WHERE
(((BoughtSold.BoughtID) Is Not Null)) UNION SELECT "<All>" as Comment, 0 AS
CommentID FROM Comments ORDER BY Comments.Comment; as the RowSource for the
Combo Box

OK The relevent bit is to make it a Union Query with "<All>" or "<No
SELECTION> or whatever.

So let's guess
SELECT TesterName UNION SELECT "<All>" As TesterName From TesterNames ORDER
BY TesterName

Then on th AfterUpdate of the CboSelectTester you need
If CboTesterName = "<All>" then
    Me.Filter = ""
    Me.FilterOn = False       ' So there's no filter applied
Else
 Me.Filter = "TesterName = '" & Me.cboSelectTester & "'"
  Me.FilterOn = True
End If

As a small point, as I hate lots of inverted commas - they are confusing I
would use Chr$(34) insted of double inverted commas:-

 Me.Filter = "TesterName = " & Chr($34) & Me.cboSelectTester & Chr(34)
Phil

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

How to Make Null Selection in Combo Box Return All Values Anita Arnold <honolulalu@yahoo.com> - 2014-09-04 07:49 -0700
  Re: How to Make Null Selection in Combo Box Return All Values "Phil" <phil@stantonfamily.co.uk> - 2014-09-04 18:29 +0100
    Re: How to Make Null Selection in Combo Box Return All Values Anita Arnold <honolulalu@yahoo.com> - 2014-09-04 10:50 -0700
  Re: How to Make Null Selection in Combo Box Return All Values "Ron Paii" <None@none.com> - 2014-09-05 06:54 -0500

csiph-web