Query: 'Enter parameter value' puzzle

Giganews Newsgroups
Subject: Query: 'Enter parameter value' puzzle
Posted by:  internet.shoppi…@foobox.com
Date: Sat, 17 Mar 2018

I have a continuous form that displays 10 columns with the option to displa=
y an additional 6 columns.

For most uses displaying the 10 columns is sufficient.  I have a button tha=
t hides/shows the extra 6 columns and switches the recordsource.  The query=
that contains the extra 6 columns is slow because it needs to draw data fr=
om a  number of table and carry out some summing.

The button to hide/show has the event:

Private Sub showHidePaymentsButton_Click()
    If Me.showHidePaymentsButton.Caption =3D "Show values" Then
        Me.RecordSource =3D "InvoiceSummaryFullQuery"
        Me.showHidePaymentsButton.Caption =3D "Hide values"
        Me.RecordSource =3D "InvoiceSummaryReducedQuery"
        Me.showHidePaymentsButton.Caption =3D "Show values"
    End If
End sub

Everything displays correctly when I switch between the two record sources.

However a strange thing happens.  After switching recordsource from, say, I=
nvoiceSummaryFullQuery to InvoiceSummaryReducedQuery if I then right click =
in a column, say, PaymentDueDate to select 'sort newest to oldest' I get an=
error 'Enter parameter value InvoiceSummaryFullQuery.PaymentDueDate'

If I switch the other way from InvoiceSummaryReducedQuery to InvoiceSummary=
FullQuery and try to sort I get a similar message but 'Enter parameter valu=
e InvoiceSummaryReducedQuery.PaymentDueDate'

In each case it appears to be looking for the recordsource that is no longe=
r being used.

I am puzzled as to why this is happening.  Any ideas?