Solved Query selection in Access 2000.

[Woodpecker]

Thanks for your observations June7. The Expr1 label never originally existed. It was Media, but somehow changed itself to Expr1. I've had to edit all the queries that it affected. You've already said that pop ups shouldn't be used as a user input for a query, which I'm going to put into practice once I get to understand combo boxes. Any tips here would be well received!

 

[Gasman]

OK, yes I forgot you had already loaded half of your DB. :-( Sorry.

I imported and renamed the Media table, as that seemed the easiest option. Also renamed the Media field.

Query now works, and as you say You get a Record Deleted message and the records all change to #Name.

No idea at the moment whatsoever
I have never seen a message come from a query like that before. It happens on several of your queries, not just the one you mentioned, but no others.
I cannot see any code in the DB and cannot tell is a record is actually being deleted or not.

However I imported it all into a new DB and that deletion message no longer appears, nor do the records change to #Name. So I can only assume corruption?.

 

[Woodpecker]

Yes, despite the deletion message, no records are deleted. It also misbehaves when inputting the criteria for a pop-up, it asks me two or three times for the same input. I am suspecting (or hoping) that all these problems are the result of corruption, which through your perseverance, appear to have overcome by importing into a new db. Could you run through the procedure for doing this please Gasman?

 

[Gasman]

Just create a new db and import everything from your old one.
I did that with the db I uploaded, which now has your media table as a local table.
So if that was your real db, it has already been done for you. The renaming of fields to their old name as well.

 

[Woodpecker]

Thanks very much Gasman. Where will I find the file you've uploaded?

 

[Gasman]

In post #22 ?

 

[Woodpecker]

Oh yes, thanks Gasman. Unfortunately I'm still using Access 2000. Would you mind saving the .ACCDB file to .MDB and resending. Thanks

 

[Gasman]

Might be a while as I am out in the community van all day.
Just create a new db and rename as I did, or perhaps someone else can convert it before I get back home.
Sorry about that.

 

[Gasman]

Ok, found a few minutes between trips.

 

[Pat Hartman]

Regarding the queries where everything got renamed to Expr#, That happens when you delete a table or query that a query references. I'm not sure what causes Access to do this, I believe it happens during a C&R. Sooo, if you have a process where you delete and recreate a table, don't do the delete after the process is finished, do the delete as the first step of replacing the table. This minimizes the chances of Access "helping" you out and messing up your queries.

 

[Woodpecker]

Ah! Thanks for solving that mystery for me Pat.

 

[Woodpecker]

Ok, found a few minutes between trips.

Thanks very much Gasman.

 

[Pat Hartman]

You're welcome. Sometimes Access help can be very painful. I have the scars to prove it

 

[Woodpecker]

Can anybody tell me where my query results have gone ? I've two linked files on my Access 2000, Recording and Media. The Media File has a Media Type field, which has been populated with three different values, - DVD-R, PRE, and RAM. I've written a query (see attached query) in order to list the db contents by Media Type. When I run it and input RAM, it lists all records with a Media Type field value of RAM, but however, when I input either DVD-R or PRE, it doesn't return any results at all, - very strange!

 

[Woodpecker]

Being a 'new kid on the block', I'm green when it comes to forum form & etiquette. I'm not sure if I should be starting a new thread, continually following up an individuals replies etc. I'd like to be knowledgable enough in Access to be able to help others myself, but until then I'll have to just keep asking the question while other kind souls answer it for me. If anyone sees me dropping a huge clanger, please tell me....I won't cry.... Honest!

 

[Gasman]

I'd be asking a new question.
As I already have your db, what query are you running.?
Never mind, created it myself.

Works for me?

However no PRE ?
These are the PRE from the media table

And you have no disk in the recordings table > 77701
As the join is an inner join, data must be in both tables, so no data shown.

 

[Gasman]

As we are already in 36 posts in this thread , I would start a new thread if my last post does not solve your problem.

 

[CarlettoFed]

In the attached file you can see an example with your normalized data and search form.

 

[isladogs]

I have just dipped into this thread but not read all posts
You will also see Expr# in query fields where a referenced table or its fields have been renamed without the Name AutoCorrect feature being used.

 

[Woodpecker]

I'd be asking a new question.
As I already have your db, what query are you running.?
Never mind, created it myself.

Works for me?
View attachment 107906
However no PRE ?
These are the PRE from the media table
View attachment 107907
An you have no disk in the recordings table > 77701
As the join is an inner join, data must be in both tables, so no data shown.

Yes, thanks

I'd be asking a new question.
As I already have your db, what query are you running.?
Never mind, created it myself.

Works for me?
View attachment 107906
However no PRE ?
These are the PRE from the media table
View attachment 107907
An you have no disk in the recordings table > 77701
As the join is an inner join, data must be in both tables, so no data shown.

Yes, thanks Gasman. Should have worked that one out for myself eh!