Change Form Record Source 1

[tbac]

I have a form that shows 112 records (based on a query) but I want the user to be able to 'view all records' or just the normal 112 records. Is it best to use a button? and what is the code I use for the button event?

 

[TheAceMan1]

How are ya tbac . . .

[blue]Is it best to use a button? . . .[/blue]

No one can say if it's best to use a button except you! (you know the navigation you seek ... not Us!) However it can be done with a button.

[blue]Requirements:
Two Queries:
a) one shows the 112 records (by default, [purple]criteria dependent[/purple])
b) the alternate that shows all! ([purple]non-criteria dependent![/purple])[/blue]

Understand ... the above means, [blue]the 112 records[/blue] requires a [purple]where clause[/purple], [blue]all records[/blue] [purple]doesn't![/purple] This translates you button code to look like:

[blue]    If InStr(1, Me.RecordSource, "WHERE") > 0 Then
        Me.RecordSource = "qryAll"
        Me![purple][B][I]ButtonName[/I][/B][/purple].Caption = "[purple][b]Show 112 Records![/b][/purple]"
    Else
        Me.RecordSource = "qry112"
        Me![purple][B][I]ButtonName[/I][/B][/purple].Caption = "[purple][b]Show All Records![/b][/purple]"
    End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JBinQLD]

G'day.

I suspect that Acemans button will only work once, and not act as a "toggle" - at least in Access 2003 or lower However, a simple mod will change that:

    If Me.RecordSource= "qry112"
        Me.RecordSource = "qryAll"
        Me!ButtonName.Caption = "Show 112 Records!"
    Else
        Me.RecordSource = "qry112"
        Me!ButtonName.Caption = "Show All Records!"
    End If

    Me.Requery

Have a great weekend.

 

[PHV]

One way is a button with code playing with the FilterOn property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

tbac . . .

Going over my code I find I'll have to agree with [blue]JBinQLD[/blue]. Chances are hi the 112 records query will be set by the Select [blue]Top[/blue] predicate and not a [blue]WHERE Clause[/blue]. In any case, using the two specific [blue]query names[/blue] is more plausable than hunting for something in the queries SQL!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886