Modifying Report Properties 2

[DBEngine]

Generically speaking, I want to set SortBy and SortByOn in an Access 97 report from an option box on a form. How do I do that without first opening the report? Everything works except I get a message that the report name is either closed or nonexistant. I can't see how to change the properties without running the report! Also, when I set the properties manually and run the report, it seems to work on some fields but not others. The recordsource for the report is a table. Any ideas?

DB Engine

 

[lameid]

I beleive you have to open the report for design to set the properties. Look into the documents collection. You may also consider setting the properties on the reports on Open event.

 

[PaulF]

to expand on lameid said, you can create a table to store the results of the OptionBox on the form, then check the value in the table as part of the report's OnOpen Event and set the Form's OrderByOn property. This example uses a table with the name tblOrderBy which has one field named UseOrderBy. When you provide code to Update this table with the OptionBox value, ensure that you are always updating UseOrderBy in the first record, because the code below will only check the first record of tblOrderBy.

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblOrderBy&quot
With rst
.MoveFirst
If rst!UseOrderBy = True Then
Me.OrderByOn = True
Else
Me.OrderByOn = False
End If
End With

Set rst = Nothing
Set db = Nothing

End Sub

PaulF

 

[DBEngine]

Thanks, lameid and Paul. I used the On Open in the report and referenced back to the option group on the form to get the data. Still - some fields sort and others seem to ignore the SortBy. Here's the code I used.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_rptCustomers

Dim strOrderBy As String

If IsLoaded("frmCustomers&quot Then
Select Case Forms!frmCustomers.opgCustomers.Value
Case 1
strOrderBy = "CUST_ID"
Case 2
strOrderBy = "Last_Name"
End Select
Me.OrderBy = strOrderBy
Me.OrderByOn = True
Else
Me.OrderBy = ""
Me.OrderBy = False
End If

Exit_rptCustomers:
Exit Sub

Err_rptCustomers:
MsgBox Err.Description
Resume Exit_rptCustomers

End Sub

THANKS again, guys!

BTW, I'm using a similar routine with several other forms/reports. Some fields sort OK but others seem to simply ignore the SortBy. What's up with that?

DBEngine

 

[PaulF]

try enclosing the Field Names in brackets.

"[CUST_ID]"
"[Last_Name]"

sometimes this helps, and sometimes its only because today is xxxday, and Access doesn't want to work right on xxxday. (Replace xxx with Mon, Tues, Wednes, Thurs, Fri, Satur, Sun as appropriate)

PaulF