System objects displayed in query design window

[isladogs]

Here's a new one (for me at least)

When creating a query, a long list of system objects (form & report modules with code) are appearing in the Show Table window

For what its worth, I can even use these in a query - see screengrab

However, I obviously don't want this behaviour

Any ideas on how to get rid of this issue (other than recreating a new fresh db)?

Not that it should be relevant but hidden & system objects are both unticked in Navigation Objects

 

[Ranman256]

these are deleted objects and are not supposed to show. But with mixed versions they tend to show anyway.

just compact the db and they should vanish.

 

[isladogs]

these are deleted objects and are not supposed to show. But with mixed versions they tend to show anyway.

just compact the db and they should vanish.

I'd already compacted, decompiled, recompiled & compacted again before posting
I'm only too aware they aren't supposed to show ... but in fact all are current form & report module objects (37 in all).
However they are being wrongly flagged as queries in MSysObjects which is why they appeared in the query designer dialog

I have no idea what you mean by 'mixed objects'

Anyway, I've created a recordset just containing those objects and deleted them from MSysObjects using a variation on the code I posted in this thread:
https://www.access-programmers.co.uk/forums/showthread.php?t=293579

They've now gone which is fine...& hopefully never to return.
However I'd like to know why they appeared in the first place

NOTE: The actual objects have been recreated in MSysObjects but are now flagged correctly again

 

[The_Doc_Man]

The items beginning with a tilde (~) are actually temporary queries OR they are queries used as a row source when you used a wizard to build a query on-the-fly for that combo box or list box.

The first part of the name is the form. The second part of the name is the field.

I don't know WHY they were retained other than as I have always said: Wizards are often dumber than a box of rocks. However, I answered because you also asked how they were created. Answer: A form wizard.

 

[isladogs]

First of all, thanks to both @Ranman & @The_Doc_Man for replying

Apologies for the delay in responding - I've not had access to my computer for a couple of days.

I should also apologise for not explaining myself well in either post.

I understood exactly what had happened & what it all meant.
What I wasn't clear about was the reasons why the MSysObjects table had got its 'knickers in a twist'

The items beginning with a tilde (~) are actually temporary queries OR they are queries used as a row source when you used a wizard to build a query on-the-fly for that combo box or list box.

The first part of the name is the form. The second part of the name is the field.

I don't know WHY they were retained other than as I have always said: Wizards are often dumber than a box of rocks. However, I answered because you also asked how they were created. Answer: A form wizard.

This is correct.

ALL form/report controls with a row source have a record in MSysObjects which is treated as a TEMP query (type = 5 ; flag = 3)
In fact this happens however the row source is entered - not just using a wizard or query design window.

NOTE: the naming convention is as follows:
~sq_f = form ; ~sq_c = form control ; ~sq_r = report ; ~sq_d = report control

In this case, the 'flag values' had somehow been modified so a small proportion of those items behaved as 'standard' queries (crosstab/append/update/delete/select/union) and were therefore displayed in the query design window.
(See attached screenshot for more info on MSysObjectTypes)

I still don't know how it happened as it is IMPOSSIBLE to edit these MSysObjects records AFAIK.

Anyway, in case my experiences are of any use to others, I've completely rewritten my article in the code repository to include all the code used in fixing 3 types of error in the MSysObjects table together with an example database.

See the following link:
https://www.access-programmers.co.uk/forums/showthread.php?t=293579