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

Groups > comp.databases.ms-access > #9872 > unrolled thread

How to Make Null Selection in Combo Box Return All Values

Back to article view | Back to comp.databases.ms-access

Contents

  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

#9872 — How to Make Null Selection in Combo Box Return All Values

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!

[toc] | [next] | [standalone]

#9873

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

[toc] | [prev] | [next] | [standalone]

#9874

Many thanks. Yours was very similar to other examples I'd reviewed, but you provided more step-by-step -- at least WHERE to put WHAT -- than they did! This made it so much easier to configure!

[toc] | [prev] | [next] | [standalone]

#9878

"Anita Arnold" <honolulalu@yahoo.com> wrote in message 
news:8c4a8ce0-7bee-408f-bea7-c374089bd4f6@googlegroups.com...
> 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!

Me.Filter = vbNullString
Me.FilterOn = False 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web