Sort Order on OpenForm

[austin3515]

Trying to open a form and sort it as I've indicated below. I want to sort using the open args of this command because depending on which line of code I use to open the form, a different sort order applies. The form is based on a query that joins two tables together by a common field.


DoCmd.OpenForm "frm1099Data", , , "Reviewer = '" & Me.cmbReviewer & "'", , , "ORDER BY qry1099Log.fldLocked, qry1099Log.fldReviewer, qry1099Log.[Plan Name]"

The previous code does NOT apply any sorting. Also, when I look at the Data properties of the form, the Order Property is blank.

 

[boblarson]

Sorry but your code is way off. You can't refer to a query like that. You would need to use a set of DLookups if you want to use info in a query and the query must return ONE record only.

The code to filter would be something like:

Dim strOrderBy As String
 
DoCmd.OpenForm "frm1099Data", , , "Reviewer = '" & Me.cmbReviewer & "'"
 
strOrderBy = DLookup("fldLocked", "qry1099Log") & "," & DLookup("fldReviewer", "qry1099Log") & "," & DLookup("Plan Name", "qry1099Log")
 
Forms!frm1099Data.OrderBy = strOrderBy
Forms!frm1099Data.OrderOn = True

But the DLookups would need to be returning FIELD names. If those ARE the field names then you would use:

Dim strOrderBy As String
 
DoCmd.OpenForm "frm1099Data", , , "Reviewer = '" & Me.cmbReviewer & "'" 
 
strOrderBy = "fldLocked, fldReviewer, [Plan Name]"
 
Forms!frm1099Data.OrderBy = strOrderBy
Forms!frm1099Data.OrderOn = True

 

[austin3515]

Sorry?? No apologies necessary! There's only 2 kinds of code, code that works and code that don't - I knew I had the latter

THANKS!!

 

[austin3515]

Still not working...

I did not have an "OrderOn" property, I assume because I'm in 2003 and that is a 2007 feature? But my Order By property is filled out but form just won't sort that way. Even when I just manually type my sorts into the ORder By property of the form, it still doesn't sort (even if I close and reopen the form). What the heck is going wrong??

 

[boblarson]

Still not working...

I did not have an "OrderOn" property, I assume because I'm in 2003 and that is a 2007 feature? But my Order By property is filled out but form just won't sort that way. Even when I just manually type my sorts into the ORder By property of the form, it still doesn't sort (even if I close and reopen the form). What the heck is going wrong??

I put a typo in there - it should be

Forms!frm1099Data.OrderByOn = True

As for manually typing it in, what does your entry look like?

 

[austin3515]

I tried typing this: [Plan Name]
and this: qry1099Log.[Plan Name]

in the Order By property

I figured I would try something simple (i.e., sort by one field first)

 

[boblarson]

The value would be:

"[Plan Name]"

just like that with the quotes and WITHOUT the qry1099Log (unless you have the same name from multiple tables). And then it would be

"qry1099Log.[Plan Name]"