I have a report in Access 2010 that is supposed to show an alphabetical list of members who have paid their dues for the year. The RecordSource for the report is an SQL statement as follows:
SELECT MEMBERS.LastFirst, DUES.DuesDate, DUES.YearID
FROM MEMBERS
INNER JOIN DUES ON MEMBERS.MemberID = DUES.MemberID
ORDER BY MEMBERS.LastFirst;
The OnOpen event for the report produces a form that allows the user to select a year from a combobox. The OnClick event for a button on the form changes the SQL statement to:
SELECT MEMBERS.LastFirst, DUES.DuesDate, DUES.YearID
FROM MEMBERS
INNER JOIN DUES ON MEMBERS.MemberID = DUES.MemberID
WHERE (DUES.YearID = “2013”)
ORDER BY MEMBERS.LastFirst;
The underlined text being added at the form level (assuming the user selects “2013” from the combobox.). The revised RecordSource is placed in a hidden text box on the form.
The report lists members randomly. In the example, the members shown on the report are all the members who have paid for 2013 but their names appear at random. The WHERE clause works but not the ORDER BY.
If I run the report without calling the form, either by using the existing RecordSource or including a WHERE clause in the RecordSource, the list is ascending as you would expect. I’ve even tried setting the OrderBy property of the report to MEMBERS.LastFirst and making OrderByOn = True, both before and after changing the RecordSource,all to no avail: I still get a random list of names. I've checked and rechecked the RecordSource for the report after it changes and it is as shown above.
Here’s the code in the OnOpen event of the report:
On Error GoTo Error_Handler
Me.Caption = "Dues Paid Report"
DoCmd.OpenForm FormName:="frmPaidMembers", windowmode:=acDialog
' Cancel the report if 'cancel' button pressed
' Cancel button sends "no" to the txtContinue control on the form
If Forms!frmPaidMembers!txtContinue = "no" Then
Cancel = True
GoTo Exit_Procedure
End If
Me.OrderBy = "Members.LastFirst"
Me.OrderByOn = True
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, Forms!frmPaidMembers!txtWhereClause)
Exit_Procedure:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume
I'm obviously overlooking something but I can't see it. Thanks for your help.
SELECT MEMBERS.LastFirst, DUES.DuesDate, DUES.YearID
FROM MEMBERS
INNER JOIN DUES ON MEMBERS.MemberID = DUES.MemberID
ORDER BY MEMBERS.LastFirst;
The OnOpen event for the report produces a form that allows the user to select a year from a combobox. The OnClick event for a button on the form changes the SQL statement to:
SELECT MEMBERS.LastFirst, DUES.DuesDate, DUES.YearID
FROM MEMBERS
INNER JOIN DUES ON MEMBERS.MemberID = DUES.MemberID
WHERE (DUES.YearID = “2013”)
ORDER BY MEMBERS.LastFirst;
The underlined text being added at the form level (assuming the user selects “2013” from the combobox.). The revised RecordSource is placed in a hidden text box on the form.
The report lists members randomly. In the example, the members shown on the report are all the members who have paid for 2013 but their names appear at random. The WHERE clause works but not the ORDER BY.
If I run the report without calling the form, either by using the existing RecordSource or including a WHERE clause in the RecordSource, the list is ascending as you would expect. I’ve even tried setting the OrderBy property of the report to MEMBERS.LastFirst and making OrderByOn = True, both before and after changing the RecordSource,all to no avail: I still get a random list of names. I've checked and rechecked the RecordSource for the report after it changes and it is as shown above.
Here’s the code in the OnOpen event of the report:
On Error GoTo Error_Handler
Me.Caption = "Dues Paid Report"
DoCmd.OpenForm FormName:="frmPaidMembers", windowmode:=acDialog
' Cancel the report if 'cancel' button pressed
' Cancel button sends "no" to the txtContinue control on the form
If Forms!frmPaidMembers!txtContinue = "no" Then
Cancel = True
GoTo Exit_Procedure
End If
Me.OrderBy = "Members.LastFirst"
Me.OrderByOn = True
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, Forms!frmPaidMembers!txtWhereClause)
Exit_Procedure:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume
I'm obviously overlooking something but I can't see it. Thanks for your help.
