DISTINCT Records By Command Button

[vdanelia]

Hello I have a question About DISTINCT The Records by a command button. I searched but, can't find anything like that..

I have a Continuous form based on a query and I use "SELECT DISTINCT ........ it works perfectly DISTINCTS the records as needed, but i want to add on the form a command button and being clicked must remove distinct
I thought and decided to create two query for that form and by command button or toggle button change the destination record source. Is that possible or there is another way...

Thank you in advanced

 

[vbaInet]

What is the caption of the button? And you want to apply it on the current form right?

 

[vdanelia]

For Example I Can do:
Me.RecordSource = "QueryName"
Is it a good way?

 

[vbaInet]

Yes you can, but what is the Caption of your button?

 

[vdanelia]

Button Caption For Example Will be "DistRec" Being Clicked It will distinct the records (Will change to another query which is distinct)

 

[vbaInet]

So in the Click event of your button put this:

    If Me.Button.Caption = "DistRec" Then
        Me.RecordSource = Replace(Me.RecordSource, "SELECT", "SELECT DISTINCT", 1, 1, vbBinaryCompare)
        Me.Requery
        Me.Button.Caption = "NonDistRec"
    Else
        Me.RecordSource = Replace(Me.RecordSource, "DISTINCT ", "", 1, 1, vbBinaryCompare)
        Me.Requery
        Me.Button.Caption = "DistRec"
    End If

Also, you will need to:

1. use a SELECT statement as your record source
2. ensure that the word SELECT is in upper case (just like I wrote it)
3. If your command button reads "DistRec" in design view, make sure that DISTINCT is not in your SQL statement.

 

[vdanelia]

Hello VBAINET, Thanks for the quick response, it says that: Select Statement includes a reserved word....
I'll use another name for example QRY1 and QRY2

 

[vbaInet]

What is the record source of your form now?

 

[vdanelia]

I set QRY1 for the Record Source for the form (QRY1 is not with distinct)
when form being opened it must display everything without distinct, when clicked DistRec it must distinct the records

 

[vbaInet]

In my post #6, my first point was -->

1. use a SELECT statement as your record source

What I mean by this, the Record Source should be

SELECT qry1.* FROM qry1;

That is what you call a SELECT statement.

 

[vdanelia]

Greatest Thanks vbaInet
Works Perfectly
Does What I needed!
Super!

 

[vbaInet]

You're welcome!