DISTINCT Records By Command Button (1 Viewer)

vdanelia

Registered User.
Local time
Today, 03:43
Joined
Jan 29, 2011
Messages
215
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
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 10:43
Joined
Jan 22, 2010
Messages
26,374
What is the caption of the button? And you want to apply it on the current form right?
 

vdanelia

Registered User.
Local time
Today, 03:43
Joined
Jan 29, 2011
Messages
215
For Example I Can do:
Me.RecordSource = "QueryName"
Is it a good way?
 

vbaInet

AWF VIP
Local time
Today, 10:43
Joined
Jan 22, 2010
Messages
26,374
Yes you can, but what is the Caption of your button?
 

vdanelia

Registered User.
Local time
Today, 03:43
Joined
Jan 29, 2011
Messages
215
Button Caption For Example Will be "DistRec" Being Clicked It will distinct the records (Will change to another query which is distinct)
 

vbaInet

AWF VIP
Local time
Today, 10:43
Joined
Jan 22, 2010
Messages
26,374
So in the Click event of your button put this:
Code:
    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

Registered User.
Local time
Today, 03:43
Joined
Jan 29, 2011
Messages
215
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
 

vdanelia

Registered User.
Local time
Today, 03:43
Joined
Jan 29, 2011
Messages
215
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

AWF VIP
Local time
Today, 10:43
Joined
Jan 22, 2010
Messages
26,374
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
Code:
SELECT qry1.* FROM qry1;
That is what you call a SELECT statement.
 

Users who are viewing this thread

Top Bottom