Sort and Filters Not Working in Continuous Forms - Microsoft Access Forum


	Back to Access Forum Comments List

Sort and Filters Not Working in Continuous Forms

Andrew Watson

28 hours ago

Hi Richard and All,

I created an database from your Access Search Template video + integration instructions for logging work-request tickets. All worked fine! Record source for continuous forms:
SELECT * FROM Tickets ORDER BY NOT IsNull(Ticket_ID), Ticket_ID;

However, as the continuous forms display of tickets grew, I tried to make a copy that doesn't include closed tickets, TicketStatusID = 1 in my TicketStatus table

Record source for continuous forms:
SELECT Tickets.[Ticket_ID], Tickets.[Date_logged], Tickets.[Logger_ID], Tickets.[Brief_description], Tickets.[Source_ID], Tickets.[Stakeholder_ID], Tickets.[Date_of_response], Tickets.[Assignee_ID], Tickets.[TicketStatus_ID], Tickets.[Status_date] FROM Tickets WHERE (((Tickets.[TicketStatus_ID])<>1)) ORDER BY NOT IsNull(Ticket_ID), Ticket_ID

The display is fine - no closed tickets - but the search and filter buttons no longer work. Is something wrong in this source code? Should the source be a query instead?

Thanks, Andrew

Debbie Heaney @Reply

25 hours ago

Are you using your own search and filter buttons or the built in ones.
I've just checked one of my databases. One of the continues forms I can't use the built in stuff but another I can. I need to study that and try to figure out the differences.

as a side note you could maybe tidy up your code there by removing "Tickets.[" then all the "]".
Everything is all from the same table, you only really need to specify the table when 2 or more tables are involved.
Something more like

SELECT Ticket_ID, Date_logged, Logger_ID, Brief_description, Source_ID, Stakeholder_ID, Date_of_response, Assignee_ID, TicketStatus_ID, Status_date FROM Tickets WHERE (((TicketStatus_ID)<>1)) ORDER BY NOT IsNull(Ticket_ID), Ticket_ID

	Debbie Heaney @Reply 25 hours ago
I can't see any, obvious, difference between my forms. They are all reading straight from my tables. The built in search and filter stuff is inconsistent on availability as i move between forms. Sometimes they are available, sometimes they aren't.

Richard Rost @Reply

24 hours ago

Andrew, assuming you mean the Search Template's own controls, the SQL you posted looks reasonable. The table prefixes and brackets aren't the problem. The first place I'd check is RequeryForm, because that procedure rebuilds the form's Record Source whenever you sort or filter.

A saved query is a good option here. Create OpenTicketsQ with the fields you need from Tickets and the criterion TicketStatus_ID <> 1. Leave sorting to the template. Then change the starting SQL inside RequeryForm to:

S = "SELECT * FROM OpenTicketsQ"

Keep the existing lines that append WhereStr and OrderByStr. The query excludes closed tickets, and the template adds the user's search and sort choices. Changing only the form's Record Source property won't change what this procedure generates.

If you've instead added the permanent status condition directly to that VBA, watch for a second WHERE or ORDER BY being appended. That would make the generated SQL invalid. The procedure's On Error Resume Next can hide the resulting error.

See Step 4 of the integration instructions. If it still fails, post your current RequeryForm code and say whether clicking does nothing or displays an error. Debbie's question about template controls versus Access's built-in controls is an important distinction.

Next Unseen