command button to sort order

[Kathy R.]

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately,
know enough about VBA to know that something can be done, but struggle
with how to get it to work. Could someone help me out with the code
that I need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

 

[Stuart McCall]

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately, know
enough about VBA to know that something can be done, but struggle with how
to get it to work. Could someone help me out with the code that I need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

When you set the form's OrderBy property, follow that with:

Me.OrderByOn = True

 

[Kathy R.]

The button works to reorder the continuous form. But if I add a new
name that isn't in order, it immediately reorders the list of names.
So, if you have a list of 100 names, and a memory like mine, you can't
remember if you added that last name, and you can no longer find it at
the bottom of the list. Is there a way to make it reorder the
continuous form only when you ask it to (by clicking the button), and
then not after that until you click the button again?

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately, know
enough about VBA to know that something can be done, but struggle with how
to get it to work. Could someone help me out with the code that I need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

When you set the form's OrderBy property, follow that with:

Me.OrderByOn = True

 

[Kathy R.]

The button works to reorder the continuous form. But if I add a new
name that isn't in order, it immediately reorders the list of names.
So, if you have a list of 100 names, and a memory like mine, you can't
remember if you added that last name, and you can no longer find it at
the bottom of the list. Is there a way to make it reorder the
continuous form only when you ask it to (by clicking the button), and
then not after that until you click the button again?

Kathy R.

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately, know
enough about VBA to know that something can be done, but struggle with how
to get it to work. Could someone help me out with the code that I need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

When you set the form's OrderBy property, follow that with:

Me.OrderByOn = True

 

[Kathy R.]

The button works to reorder the continuous form. But if I add a new
name that isn't in order, it immediately reorders the list of names.
So, if you have a list of 100 names, and a memory like mine, you can't
remember if you added that last name, and you can no longer find it at
the bottom of the list. Is there a way to make it reorder the
continuous form only when you ask it to (by clicking the button), and
then not after that until you click the button again?

Kathy R.

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately, know
enough about VBA to know that something can be done, but struggle with how
to get it to work. Could someone help me out with the code that I need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

When you set the form's OrderBy property, follow that with:

Me.OrderByOn = True

 

[Stuart McCall]

The button works to reorder the continuous form. But if I add a new name
that isn't in order, it immediately reorders the list of names. So, if you
have a list of 100 names, and a memory like mine, you can't remember if
you added that last name, and you can no longer find it at the bottom of
the list. Is there a way to make it reorder the continuous form only when
you ask it to (by clicking the button), and then not after that until you
click the button again?

Kathy R.

Hi Folks!

I have a subform that's a continuous form that I use for attendance
taking. I would like to add a command button that, on click, sorts the
order of the attendees in a custom sort order. I have tried OrderBy and
DoCmd RunSQL but am obviously missing something. I, unfortunately, know
enough about VBA to know that something can be done, but struggle with
how to get it to work. Could someone help me out with the code that I
need?

Thank you so much for your help!
Kathy R.

Main Form - frmAttendance
Sub Form - sfrAttendee
Custom Sort Order - ORDER BY tblFamily.FamLastName, tblFamily.FamID,
IIf([ContactStatus]="Primary Contact",1,IIf([ContactStatus]="Secondary
Contact",2,IIf([ContactStatus]="Other
Adult",3,IIf([ContactStatus]="Child",4,5)))), tblIndividual.FirstName;

When you set the form's OrderBy property, follow that with:

Me.OrderByOn = True

When you don't want the form to re-sort, use Me.OrderByOn = False, then when
you want it to recommence sorting, use Me.OrderByOn = True.

Simple as that.

 

[Stuart McCall]

The button works to reorder the continuous form. But if I add a new name
that isn't in order, it immediately reorders the list of names. So, if you
have a list of 100 names, and a memory like mine, you can't remember if
you added that last name, and you can no longer find it at the bottom of
the list. Is there a way to make it reorder the continuous form only when
you ask it to (by clicking the button), and then not after that until you
click the button again?

I think this should work:

In the form's BeforeInsert event, switch off ordering with:

Me.OrderByOn = False

The BeforeInsert event fires as you 'dirty' the new record. Then in your
button's Click event, switch ordering back on when you're ready:

Me.OrderByOn = True

This is untested, but I can't see a reason it shouldn't work.

HTH

 

[Kathy R.]

Thank you for the reply, Stuart.

I tried putting putting the Me.OrderByOn = False in the Before Insert
event of the subform, but when I tried to enter a new record, the cursor
jumped to the first record of the continuous subform where, if I typed
anything, it would change that record.

I have, however, come up with a solution, which I think will be better
for my users anyways. I now have two buttons, clearly labeled with
"Sort Alphabetical" and "Sort as Entered." The first using Me.OrderByOn
= True and the second =False. This way they can click exactly what
they want, and easily switch back and forth between the two.

I really appreciate your help and suggestions. The = false/true was the
part I was missing. And seeing different ways to do things can only
help me to become better at this. Thank you again!

Kathy R.

 

[Stuart McCall]

Thank you for the reply, Stuart.

I tried putting putting the Me.OrderByOn = False in the Before Insert
event of the subform, but when I tried to enter a new record, the cursor
jumped to the first record of the continuous subform where, if I typed
anything, it would change that record.

I have, however, come up with a solution, which I think will be better for
my users anyways. I now have two buttons, clearly labeled with "Sort
Alphabetical" and "Sort as Entered." The first using Me.OrderByOn = True
and the second =False. This way they can click exactly what they want,
and easily switch back and forth between the two.

I really appreciate your help and suggestions. The = false/true was the
part I was missing. And seeing different ways to do things can only help
me to become better at this. Thank you again!

Kathy R.

You're welcome. Glad you got it worked out.