Going Crazy! Form Datasheet does not reflect changes to SQL query

[Caitlin Anstee]

Hi everyone,

I've tried everything and I just can't seem to wrap my head around
what i'm doing wrong!

This was my first attempt at getting the printed version of a form to
reflect the current sort order that as user has created:


Public Sub testPrint()
CurrentDb.QueryDefs("PrintQuery").SQL = Left((CurrentDb.QueryDefs
("PrintQuery").SQL), 187) + "ORDER BY " + orderByString
DoCmd.SelectObject acForm, "PrintView", True
'DoCmd.PrintOut
DoCmd.SelectObject acForm, "Main", False
End Sub



No luck. The printed form did not reflect sort order at all. Strange.
Okay, here's attempt number two where I open the form, explicitely
restate the recordsource, save it and close it.





Public Sub testPrint()
CurrentDb.QueryDefs("PrintQuery").SQL = Left((CurrentDb.QueryDefs
("PrintQuery").SQL), 187) + "ORDER BY " + orderByString
DoCmd.OpenForm "PrintView", acFormDS
Forms("PrintView").RecordSource = "PrintQuery"
DoCmd.Save acForm, "PrintView"
DoCmd.Close acForm, "PrintView"
DoCmd.SelectObject acForm, "PrintView", True
'DoCmd.PrintOut
DoCmd.SelectObject acForm, "Main", False
End Sub




But it still doesn't work!!!!




My query reflects the new orderby clause perfectly. If I open it in
design view it's there. If I look at the sql it is the exact string I
created, If I open the query the datasheet is exactly the way I wanted
it to be with my data sorted accordingly. At the very least I know
I've done that part right. I haven't made a syntax error or anything
simple like that.

But I cannot make this form print properly at all. It always retains
its previous sort order -- the default sort order as the inital state
of the form doesn't even have a sort order defined. It never respects
the orderby clause. It doesn't read it at all.

I need my printed form to reflect the sort order that the user has
selected which is stored in orderByString.

Has anybody encountered this before? Is it not possible for a
datasheet to reflect the contents of an orderby clause from sql?

Thanks for even reading this.

C.

 

[John W. Vinson]

Hi everyone,

I've tried everything and I just can't seem to wrap my head around
what i'm doing wrong!

This was my first attempt at getting the printed version of a form to
reflect the current sort order that as user has created:

First off... Forms are designed for onscreen viewing. Reports are designed for
printing.

Secondly... you can create a Report using PrintQuery as its Recordsource, and
set the desired sort order in the report's Sorting and Grouping dialog.

 

[Caitlin Anstee]

First off... Forms are designed for onscreen viewing. Reports are designed for
printing.

Secondly... you can create a Report using PrintQuery as its Recordsource,and
set the desired sort order in the report's Sorting and Grouping dialog.

I appreciate the advice John, and likely I will attempt to use a
report for my needs, but I worry that the report will act in the same
way. I'll give it a shot and post here again.

The point is, why when I change a query and specify an order by
clause, does the form not respect that clause? Is this just a flaw
with access? I can imagine a situation where I would want to
dynamcally specify a recordsource for onscreen viewing and I don't
understand why my method doesn't work. The sql statement is fine. The
form should read it in its entirety and populate the datasheet.

Anyway, thanks again for replying.

C.

 

[John W. Vinson]

The point is, why when I change a query and specify an order by
clause, does the form not respect that clause?

It should... UNLESS the Form has an Order By property or a Filter property
(and FilterOn is true). These will override the sort order in the query.

Oddly, a Report totally ignores the sort order specified in the query; only
the report's Sorting and Grouping dialog controls the report's order.

 

[Caitlin Anstee]

It should... UNLESS the Form has an Order By property or a Filter property
(and FilterOn is true). These will override the sort order in the query.

Oddly, a Report totally ignores the sort order specified in the query; only
the report's Sorting and Grouping dialog controls the report's order.

Got it working. It was my own (stupid) mistake.

I had a form_load event for PrintView where I set the Order By clause
just to give my user a logical initial view of the data.

I guess when you select an object and then print it, access actually
*opens* the document in the background.

DoCmd.SelectObject acForm, "PrintView", True
'DoCmd.PrintOut

Once I moved that intital order by clause to the outer form (PrintView
was loading as a subform) the problem resolved and the form prints
according to the sql statement.

Thanks again John.