Using a Label to Sort channell 12/10/2008 11:21:01 AM I have the following code for my label to sort: Private Sub txtLabel124_Click() If Me.OrderBy = "[LastNameFirstName]" Then Me.OrderBy = "[LastNameFirstName] DESC" Else Me.OrderBy = "[LastNameFirstName]" End If End Sub The code works just fine, but here is my problem: The Form which contains this code is based on a Query which requires certain criteria (Parameter Query). When I click the label to sort the information present, the Parameter Box reappears asking me to put in the date. When I do, it sorts it like it should. I need to know if there is a way to bypass re-entering the same data (Date) in the parameter box. Thank you very much in advance!

Re: Using a Label to Sort "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/10/2008 12:47:06 PM Parameters are really not a very good interface. You have just given one example of where they are too frustrating. A better approach might be to add an unbound text box to your form (e.g. in the Form Header), and use its AfterUpdate event procedure to apply a filter to the form. This will avoid the parameter re-request problem. You may also need to set: Me.OrderByOn = True -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "channell" <channell[ at ]discussions.microsoft.com> wrote in message news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... [Quoted Text] >I have the following code for my label to sort: > > Private Sub txtLabel124_Click() > If Me.OrderBy = "[LastNameFirstName]" Then > Me.OrderBy = "[LastNameFirstName] DESC" > Else > Me.OrderBy = "[LastNameFirstName]" > End If > End Sub > > The code works just fine, but here is my problem: > > The Form which contains this code is based on a Query which requires > certain > criteria (Parameter Query). When I click the label to sort the > information > present, the Parameter Box reappears asking me to put in the date. When I > do, it sorts it like it should. I need to know if there is a way to > bypass > re-entering the same data (Date) in the parameter box. Thank you very > much > in advance!

Re: Using a Label to Sort channell 12/10/2008 12:57:00 PM Allen, I realized this just after I posted this. I then posted another question asking how I would go about putting an unbound text box on the form with an "OK" button to press after entering the date. Could you possibly help me out? Thanks a lot! "Allen Browne" wrote: [Quoted Text] > Parameters are really not a very good interface. You have just given one > example of where they are too frustrating. > > A better approach might be to add an unbound text box to your form (e.g. in > the Form Header), and use its AfterUpdate event procedure to apply a filter > to the form. This will avoid the parameter re-request problem. > > You may also need to set: > Me.OrderByOn = True > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "channell" <channell[ at ]discussions.microsoft.com> wrote in message > news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... > >I have the following code for my label to sort: > > > > Private Sub txtLabel124_Click() > > If Me.OrderBy = "[LastNameFirstName]" Then > > Me.OrderBy = "[LastNameFirstName] DESC" > > Else > > Me.OrderBy = "[LastNameFirstName]" > > End If > > End Sub > > > > The code works just fine, but here is my problem: > > > > The Form which contains this code is based on a Query which requires > > certain > > criteria (Parameter Query). When I click the label to sort the > > information > > present, the Parameter Box reappears asking me to put in the date. When I > > do, it sorts it like it should. I need to know if there is a way to > > bypass > > re-entering the same data (Date) in the parameter box. Thank you very > > much > > in advance! > >

Re: Using a Label to Sort channell 12/10/2008 1:02:01 PM Allen, I realized this just after I posted my question. I then posted another question asking how to put an unbound textbox and an "OK" button next to it. That way, when I put in the date in the Unbound Textbox and then Press "OK", it will return the desired results. Could you help me with this? Thank you so much! "Allen Browne" wrote: [Quoted Text] > Parameters are really not a very good interface. You have just given one > example of where they are too frustrating. > > A better approach might be to add an unbound text box to your form (e.g. in > the Form Header), and use its AfterUpdate event procedure to apply a filter > to the form. This will avoid the parameter re-request problem. > > You may also need to set: > Me.OrderByOn = True > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "channell" <channell[ at ]discussions.microsoft.com> wrote in message > news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... > >I have the following code for my label to sort: > > > > Private Sub txtLabel124_Click() > > If Me.OrderBy = "[LastNameFirstName]" Then > > Me.OrderBy = "[LastNameFirstName] DESC" > > Else > > Me.OrderBy = "[LastNameFirstName]" > > End If > > End Sub > > > > The code works just fine, but here is my problem: > > > > The Form which contains this code is based on a Query which requires > > certain > > criteria (Parameter Query). When I click the label to sort the > > information > > present, the Parameter Box reappears asking me to put in the date. When I > > do, it sorts it like it should. I need to know if there is a way to > > bypass > > re-entering the same data (Date) in the parameter box. Thank you very > > much > > in advance! > >

Re: Using a Label to Sort "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/10/2008 2:18:51 PM Here's an example of creating a filter based on a date range: http://allenbrowne.com/casu-08.html The example applies the filter to a report, but you build the filter string in exactly the same way for a form. In the end, you just use: Me.filter = strWhere Me.FilterOn = True instead of the OpenReport line. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "channell" <channell[ at ]discussions.microsoft.com> wrote in message news:CF730FC1-CCF8-40B2-899C-939B7D87FC44[ at ]microsoft.com... [Quoted Text] > Allen, I realized this just after I posted my question. I then posted > another question asking how to put an unbound textbox and an "OK" button > next > to it. That way, when I put in the date in the Unbound Textbox and then > Press "OK", it will return the desired results. Could you help me with > this? > Thank you so much! > > "Allen Browne" wrote: > >> Parameters are really not a very good interface. You have just given one >> example of where they are too frustrating. >> >> A better approach might be to add an unbound text box to your form (e.g. >> in >> the Form Header), and use its AfterUpdate event procedure to apply a >> filter >> to the form. This will avoid the parameter re-request problem. >> >> You may also need to set: >> Me.OrderByOn = True >> >> "channell" <channell[ at ]discussions.microsoft.com> wrote in message >> news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... >> >I have the following code for my label to sort: >> > >> > Private Sub txtLabel124_Click() >> > If Me.OrderBy = "[LastNameFirstName]" Then >> > Me.OrderBy = "[LastNameFirstName] DESC" >> > Else >> > Me.OrderBy = "[LastNameFirstName]" >> > End If >> > End Sub >> > >> > The code works just fine, but here is my problem: >> > >> > The Form which contains this code is based on a Query which requires >> > certain >> > criteria (Parameter Query). When I click the label to sort the >> > information >> > present, the Parameter Box reappears asking me to put in the date. >> > When I >> > do, it sorts it like it should. I need to know if there is a way to >> > bypass >> > re-entering the same data (Date) in the parameter box. Thank you very >> > much >> > in advance!

Re: Using a Label to Sort & Parameter query recordsource ericgj 12/21/2008 9:26:01 PM I had the same problem -- re-sorting on a form based on a parameter query -- and was dismayed to hear that "Parameters are really not a very good interface. " I use parameter queries quite a lot and do not want to have to rework my application to use form filters which seems like a step backwards. Especially since all I want to do is sort the data I've already loaded -- why does this require a re-query?! Luckily, I seem to have found a workaround, but I would like some advice if it is a reliable method or if there is a more efficient way. It involves bypassing the Form.OrderBy and OrderByOn properties entirely, and setting the Recordset.Sort property. So instead of: Me.OrderBy = "[LastNameFirstName]" Me.OrderByOn = True Use: Me.Recordset.Sort = "[LastNameFirstName]" Set Me.Recordset = Me.Recordset.OpenRecordset When you do this, it does not re-prompt you for parameters (perhaps because it does not re-query). It was surprising to me this worked, but it did. And it seemed fast. (I tried Set Me.Recordset = Me.Recordset.Clone, but apparently you only get the sorted recordset when you re-open.) Eric [Quoted Text] > >> "channell" <channell[ at ]discussions.microsoft.com> wrote in message > >> news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... > >> >I have the following code for my label to sort: > >> > > >> > Private Sub txtLabel124_Click() > >> > If Me.OrderBy = "[LastNameFirstName]" Then > >> > Me.OrderBy = "[LastNameFirstName] DESC" > >> > Else > >> > Me.OrderBy = "[LastNameFirstName]" > >> > End If > >> > End Sub > >> > > >> > The code works just fine, but here is my problem: > >> > > >> > The Form which contains this code is based on a Query which requires > >> > certain > >> > criteria (Parameter Query). When I click the label to sort the > >> > information > >> > present, the Parameter Box reappears asking me to put in the date. > >> > When I > >> > do, it sorts it like it should. I need to know if there is a way to > >> > bypass > >> > re-entering the same data (Date) in the parameter box. Thank you very > >> > much > >> > in advance! > > > "Allen Browne" wrote: > > > >> Parameters are really not a very good interface. You have just given one > >> example of where they are too frustrating. > >> > >> A better approach might be to add an unbound text box to your form (e.g. > >> in > >> the Form Header), and use its AfterUpdate event procedure to apply a > >> filter > >> to the form. This will avoid the parameter re-request problem. > >> > >> You may also need to set: > >> Me.OrderByOn = True > >>

Re: Using a Label to Sort & Parameter query recordsource "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 12/22/2008 2:41:14 AM That's creative: not a bad idea. Examples of cases where you might like to test if this sorting gets lost: - Does the sorting get lost if something reloads the form? E.g. after a Requery, or after a FilterByForm? - If you do this in a subform, does it get lost when you move record in the main form? (This reloads the subform.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ericgj" <ericgj[ at ]discussions.microsoft.com> wrote in message news:4A4E3123-034B-46E6-B45C-BE0E194B3F2D[ at ]microsoft.com... [Quoted Text] >I had the same problem -- re-sorting on a form based on a parameter >query -- > and was dismayed to hear that "Parameters are really not a very good > interface. " > I use parameter queries quite a lot and do not want to have to rework my > application to use form filters which seems like a step backwards. > Especially since all I want to do is sort the data I've already loaded -- > why > does this require a re-query?! > > Luckily, I seem to have found a workaround, but I would like some advice > if > it is a reliable method or if there is a more efficient way. It involves > bypassing the Form.OrderBy and OrderByOn properties entirely, and setting > the > Recordset.Sort property. > > So instead of: > Me.OrderBy = "[LastNameFirstName]" > Me.OrderByOn = True > > Use: > Me.Recordset.Sort = "[LastNameFirstName]" > Set Me.Recordset = Me.Recordset.OpenRecordset > > When you do this, it does not re-prompt you for parameters (perhaps > because > it does not re-query). It was surprising to me this worked, but it did. > And > it seemed fast. > > (I tried Set Me.Recordset = Me.Recordset.Clone, but apparently you only > get > the sorted recordset when you re-open.) > > Eric > >> >> "channell" <channell[ at ]discussions.microsoft.com> wrote in message >> >> news:9BF1742E-02CA-4C1F-B2B4-871BD6B1A5DA[ at ]microsoft.com... >> >> >I have the following code for my label to sort: >> >> > >> >> > Private Sub txtLabel124_Click() >> >> > If Me.OrderBy = "[LastNameFirstName]" Then >> >> > Me.OrderBy = "[LastNameFirstName] DESC" >> >> > Else >> >> > Me.OrderBy = "[LastNameFirstName]" >> >> > End If >> >> > End Sub >> >> > >> >> > The code works just fine, but here is my problem: >> >> > >> >> > The Form which contains this code is based on a Query which requires >> >> > certain >> >> > criteria (Parameter Query). When I click the label to sort the >> >> > information >> >> > present, the Parameter Box reappears asking me to put in the date. >> >> > When I >> >> > do, it sorts it like it should. I need to know if there is a way to >> >> > bypass >> >> > re-entering the same data (Date) in the parameter box. Thank you >> >> > very >> >> > much >> >> > in advance! >> >> > "Allen Browne" wrote: >> > >> >> Parameters are really not a very good interface. You have just given >> >> one >> >> example of where they are too frustrating. >> >> >> >> A better approach might be to add an unbound text box to your form >> >> (e.g. >> >> in >> >> the Form Header), and use its AfterUpdate event procedure to apply a >> >> filter >> >> to the form. This will avoid the parameter re-request problem. >> >> >> >> You may also need to set: >> >> Me.OrderByOn = True >> >>