Solved Error when sorting via DoCmd

[mdnuts]

Hello,

I have a report based on a query that i inserted into a form. The report has one visible field and two not visible fields. The report just lists identifying numbers (AP Acronym) and when a specific identifier is selected, the form displays all information relevant to that identifier. This works without issue. Fields being AP Acronym| Compliance Status | Test Result (AP Acronym being visible)

However if I try to sort the report to sort the identifiers based on the non-visible field Compliance Status - it pops a "Run-time error 3021 - No current Record". if i click end on the error, i can resume selecting identifiers without any further errors unless i reopen the form.

With the Report_Load event i have this.

DoCmd.SetOrderBy "Compliance Status ASC"

any thoughts?

 

[ebs17]

1) Field names with spaces and special characters are rubbish.
2) You should avoid messing up from the start.
3) In the worse case, such rubbish is masked with square brackets.

DoCmd.SetOrderBy "ComplianceStatus ASC"
DoCmd.SetOrderBy "[Compliance Status] ASC"

 

[mdnuts]

1) Field names with spaces and special characters are rubbish.
2) You should avoid messing up from the start.
3) In the worse case, such rubbish is masked with square brackets.

DoCmd.SetOrderBy "ComplianceStatus ASC"
DoCmd.SetOrderBy "[Compliance Status] ASC"

Thank you, I agree, it's a long story but sums up as being lazy.

When trying this

DoCmd.SetOrderBy "[Compliance Status] ASC"

I get the same error. Changing the field name to Compliance_Status gives me the same error. I verified (in VBA) that it reads as Compliance_Status as well.

DoCmd.SetOrderBy "[Compliance_Status] ASC"

 

[ebs17]

Primarily, I would also sort the RecordSource of the report directly, i.e. directly in the query, and not use DoCmd stuff like that.
I don't have extensive experience with the latter.

 

[mdnuts]

Hmm.

Primarily, I would also sort the RecordSource of the report directly, i.e. directly in the query, and not use DoCmd stuff like that.

I could never get the query's sort method to pass through to a report?

But in any case. I opened the form, selected the sub-report and set the order by field and it's working. Not sure why, I did this several times before i thought, although i may have just done that in the report itself, not from the main form viewing the properties of the sub-report (not sure why that'd make a difference).

 

[Minty]

A reports sort order and any grouping is set in the report design, not by the underlying query.
Query sort orders are ignored in reports as they effectively requery the data behind the scenes for the reports layout.

I would suspect you're correct about not having set it in the sub reports design.