Need to sort records on 3 subforms when main form opens

[HockeyFan]

I have a main form which i call weeklystatusfrm. On that form, there are 3 sections/subforms. PartI, PartII, and PartIII.
There is a date field on each form. I would like to open the weeklystatusfrm and show the records on each subform sorted descending.
Does anyone know how to do this?

 

[MajP]

Each subform is based on a query sorted by date descending.

 

[HockeyFan]

it's kind of complicated to explain, but i have to be able to do it with code.
I would like to type the code into the main form. Upon a button click, I would like to to be able to reference the subforms, and sort on the date field of the subforms.

 

[MajP]

Private Sub Command17_Click()
  Dim subFrm1 As Access.Form
  Dim subfrm2 As Access.Form
  Set subFrm1 = Me.Customer_Orders_Subform1.Form
  Set subfrm2 = Me.Customer_Orders_Subform2.Form
  subFrm1.OrderBy = "RequiredDate"
  subfrm2.OrderBy = "UnitPrice"
  subFrm1.OrderByOn = True
  subfrm2.OrderByOn = True
  
End Sub

 

[HockeyFan]

in your code, i don't see anything that would do a sort descending

 

[HockeyFan]

also, when you have these two lines:
Set subFrm1 = Me.Customer_Orders_Subform1.Form
Set subfrm2 = Me.Customer_Orders_Subform2.Form

what does the me.customer_orders_subform1.form represent?

what i mean is, my form name is subfrm1. what should i replace 'the customer_orders_subform1' part as?

 

[MajP]

Private Sub Command17_Click()
  Dim subFrm1 As Access.Form
  Dim subfrm2 As Access.Form
  Set subFrm1 = Me.YourSubformControl'sName.Form
  Set subfrm2 = Me.YourOtherSubformControl'sName.Form
  subFrm1.OrderBy = "RequiredDate DESC"
  subfrm2.OrderBy = "UnitPrice DESC"
  subFrm1.OrderByOn = True
  subfrm2.OrderByOn = True

Try using the help file in vba it is full of useful and helpful information.
Look at:
orderby
orderbyon

 

[HockeyFan]

Ok, forgive me for sounding stupid, but isn't the SubformControl's name, the Record Source name?
Meaning, if I use a table for the record source, then it would be the table name?
And by the way, I really appreciate you helping me.

 

[HockeyFan]

If it is the Record Source, then I'm getting an error with the code. It says, 'Method or data member not found'.

 

[MajP]

SubformControl's name, the Record Source name?

No. By default it gives it the same name as the source object. But to see this drop the same subform on a form twice and look at the names it provides the two subform controls. This is very sloppy, I never design multiple objects with the same name.

A subform control has a form within it. Lets say my subform is called "subFrmEmployees" then I name my subform control "subFrmCtlEmployees". Now there is no confusion if I am referring to a subform or the container holding the subform. Also a subform control does not have a "record source" it has a "source object" (i.e the form). A form has a recordsource (the source of the records).

me.subFrmCtlEmployees
returns a reference to a subform control
me.subFrmCtlEmployees.form
returns a reference to a form with a subform contrl

 

[HockeyFan]

Ok, here's something bizarre. It seems to work somewhat, but the problem is that i have put the code into a button. When I click on the button, it sorts the records descending based upon date. that is correct. But it doesn't sort the date field on subform 2 and subform 3 at the same time. I have to click the button again, and then it sorts the other two subforms. Of course, I would like it to sort all 3 subforms based upon date at the same time.
here is the code I am using.

Dim PartISubfrm As Access.Form
Dim PartIISubfrm As Access.Form
Dim PartIIISubfrm As Access.Form
Set PartISubfrm = Me.PartISubfrm.Form
Set PartIISubfrm = Me.PartIISubfrm.Form
Set PartIIISubfrm = Me.PartIIISubfrm.Form
PartISubfrm.OrderBy = "currentDate DESC"
PartIISubfrm.OrderBy = "todaysdate DESC"
PartIIISubfrm.OrderBy = "WkEndDt DESC"
PartISubfrm.OrderByOn = True
PartIISubfrm.OrderByOn = True
PartIIISubfrm.OrderByOn = True

 

[HockeyFan]

as a note, the button is on my mail form. It has to be that way.
It sorts subform 1 the first time I click the button. And then it sorts the other two subforms on the second click of the button.

 

[HockeyFan]

sorry, that was supposed to say 'main' form. not mail form.

 

[HockeyFan]

Well I figured out what affects the order, but I'm not sure how to fix it. It appears that whatever order the '= True' is in, that's the order that is changes the date.
Whichever one is first in the list, it does that one first. Upon the second click of the button, then it does the other two. Can you tell me how to get them to all change at the same time?

PartIISubfrm.OrderByOn = True
PartIIISubfrm.OrderByOn = True
PartISubfrm.OrderByOn = True

 

[PHV]

What about this ?
PartISubfrm.OrderBy = "currentDate DESC"
PartISubfrm.OrderByOn = True
DoEvents
PartIISubfrm.OrderBy = "todaysdate DESC"
PartIISubfrm.OrderByOn = True
DoEvents
PartIIISubfrm.OrderBy = "WkEndDt DESC"
PartIIISubfrm.OrderByOn = True
DoEvents

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

 

[HockeyFan]

thanks. it worked.