Solved Query selection in Access 2000. (1 Viewer)

[Woodpecker]

Hi all, as this is my first message I hope all you seasoned users will bear with me. I've used Access for several years to record various information about collections I have. Though I've had quite a bit of experience with the front end, I've usually had to rely on others when attempting anything even slightly complex. I usually try to use or modify an existing query etc, and end up just about 'getting by'. Just recently however I've come up against a problem which is getting the better of me! When I create a query which requires an input selection from me, when I specify the filter term in the query it works perfectly, but if I try to enter the filter in a dialogue box on the screen, it crashes & gives the message 'Record Deleted'. I've attached a screenshot of the query in design view, and also a shot of the results with '#Name?' in every field. Any advice you can offer will be well received!

 

[June7]

Advise against popup intput prompts as cannot validate entry.

Query works for me.

Is Genre field designed as a lookup in table? Also advise not to do that.

 

[Gasman]

Upload enough of the DB to see the issue.

 

[Woodpecker]

Advise against popup intput prompts as cannot validate entry.

Query works for me.

Is Genre field designed as a lookup in table? Also advise not to do that.

Thanks for your rapid response but I don't fully understand what you wrote. Perhaps, if I was more adept I would be able to fill in the gaps, so I'll have to explain exactly what I've got. The DVD File db consists of two relational tables, Media table, and Recordings table, and together they hold info on DVD recordings I've made over the years.
The Media table fields are: Disk Num-Media Type-Sysnum.
The Recordings table fields are: Sysnum-Disk Num-Genre-Title-Duration-Year-Director
I don't have much use for Forms or Reports, but I do have various Queries which I use regularly. The Genre Selection is one such query. When I run this query, a text box prompts me to enter the Genre that I want to list by. Is this termed a lookup? Why do you advise not to do that?
When I input a value in the Disk Num field of the Recordings table it looks at the same field name of the Media table and displays the Media Type. Would this be what you define as a Pop-up?
Sorry if my reply doesn't address your points but I'm sure all will become clear to me eventually.

 

[Woodpecker]

Upload enough of the DB to see the issue.

Hi, I'm happy to post more info. Do you mean attach the .mdb file intact?

 

[Gasman]

Yes. There are size limits, so depending on how big it is, you might have to create a test version with less data in the tables and just the query in question.

 

[Woodpecker]

The size of DVD File.mdb is 5.3MB. Is that too large?

 

[Gasman]

The size of DVD File.mdb is 5.3MB. Is that too large?

TBH, I do not know, try and attach, if it complains, then zip it. Should Compact it first as well regardless.

 

[Woodpecker]

Here's the db in question.

 

[jdraw]

Your database references Media Table which is a linked table that is not included in the mdb.
In fact, there is a database Media File.mdb missing.

 

[Woodpecker]

When I open Access and select the DVD File, I then select Tables. In this menu I can select either Media Table or Recordings Table. Surely these are the two relational db's you're referring to. Surely, all the components of the DVD and Media files are contained within DVD File.mdb, if not, I've been working with a db for the last 25 years that doesn't actually exist!

 

[Gasman]

When I open Access and select the DVD File, I then select Tables. In this menu I can select either Media Table or Recordings Table. Surely these are the two relational db's you're referring to. Surely, all the components of the DVD and Media files are contained within DVD File.mdb, if not, I've been working with a db for the last 25 years that doesn't actually exist!

You clearly do not know the difference between local tables and linked tables?
So without the Media Table how is the query meant to work?

 

[jdraw]

I think there is something about your setup that you are unaware of.
Here is a png of the Recordings Table Properties
(seems it was called MEDIA in the Media File.mdb
It was created in 2006, if this hasn't been "doctored")



Here is a png of the Media Table Properties
(Notice: It is a linked Table created in 2016??)


and a list of things in the navigation pane




If you have used a database successfully for 25 years, then you are using a different version that what you have posted.

You may want to talk to your IT person to sort out what exactly it is that you are using.

 

[Woodpecker]

You clearly do not know the difference between local tables and linked tables?
So without the Media Table how is the query meant to work?

View attachment 107770

View attachment 107768
View attachment 107767

You're right Gasman, I'm not clear on the distinction between local and linked tables, neither do I have much idea as to the intricacies of the Access db engine, but of course you'll realize, that's why I'm here in the first place!
It's true, there isn't a separate Media table in the storage directory (F:\MS Office Data\Access\ ) that I've nominated, but I don't understand how the db is still functioning then. When I open the recordings table I can see and interrogate all the records in that file, and the same for the media table, Where is it drawing data from if not from DVD File.mdb. The C:\Planet Data Bank\Access\Media File.mdb path on your screenshot does exist on my pc, but it's an old storage directory which hasn't been used for several years. My selected saving location is F:\MS Office Data\Access\ (which doesn't contain a copy of Media File.mdb. Could it be that Access defaults to looking for files in the C:\ location anyway?
I've just returned to the db and updated the Media File with another 20 records, when I open the Media File.mdb I see that it has been updated and is the file the db is using. Why, when I have selected the F:\ save location? Couldn't I just move the Media File to the selected F:\ location and delete the C:\Planet Data Bank completely, or will the db try to save the Media File to C:\ which will then be an invalid path?
Thanks Gasman, I really do appreciate the time you're giving me.

 

[Woodpecker]

I think there is something about your setup that you are unaware of.
Here is a png of the Recordings Table Properties
(seems it was called MEDIA in the Media File.mdb
It was created in 2006, if this hasn't been "doctored")

View attachment 107769

Here is a png of the Media Table Properties
(Notice: It is a linked Table created in 2016??)

View attachment 107777
and a list of things in the navigation pane


View attachment 107774

If you have used a database successfully for 25 years, then you are using a different version that what you have posted.

You may want to talk to your IT person to sort out what exactly it is that you are using.

Thanks JDRAW that's something that I wasn't aware of. I'm a retired engineer - You want it, I'll design and build it, but don't ask me to work with a line of machine code! Over the years I've loaded various versions of Access and ended up with 2000, which does the job without too much headache (well normally!). It sounds from what you've said that some versions haven't completely uninstalled. I'm loathe to backup and then reformat the hard drive to get rid of the junk. Is there a less drastic way of sanitizing my MS Office, what are your views on that?

 

[June7]

Thanks for your rapid response but I don't fully understand what you wrote. Perhaps, if I was more adept I would be able to fill in the gaps, so I'll have to explain exactly what I've got. The DVD File db consists of two relational tables, Media table, and Recordings table, and together they hold info on DVD recordings I've made over the years.
The Media table fields are: Disk Num-Media Type-Sysnum.
The Recordings table fields are: Sysnum-Disk Num-Genre-Title-Duration-Year-Director
I don't have much use for Forms or Reports, but I do have various Queries which I use regularly. The Genre Selection is one such query. When I run this query, a text box prompts me to enter the Genre that I want to list by. Is this termed a lookup? Why do you advise not to do that?
When I input a value in the Disk Num field of the Recordings table it looks at the same field name of the Media table and displays the Media Type. Would this be what you define as a Pop-up?
Sorry if my reply doesn't address your points but I'm sure all will become clear to me eventually.

"a text box prompts me to enter" - this is a popup input prompt, not a lookup. I never build popup prompts in queries because cannot validate user input, which can be a source of frustration for users. Say they intended to input "F" but typed "G". Use forms with comboboxes for user to input/select criteria. Query can reference controls on form for dynamic criteria. However, I also do not put dynamic criteria in queries at all. I use code to apply filter to forms and reports.

I confirmed Genre field in Recordings is not a lookup field.

Genre Selection query using prompt without Media table join works.

Media table has an arrow icon indicating it is a linked table - data is actually in another file. Hover cursor over table and you can see path to other file. This is called a split database. Recordings table is a local table. Post Media File.mdb as well and we can better advise. (I also avoid spaces in file names).

If this was a new db under development, would also advise not to use punctuation/special characters (underscore only exception) nor spaces in naming convention.

Sounds like this db is for personal use, not in a business environment and you do not have IT support.

Have you ever studied an introductory tutorial for Access?

 

[Gasman]

You're right Gasman, I'm not clear on the distinction between local and linked tables, neither do I have much idea as to the intricacies of the Access db engine, but of course you'll realize, that's why I'm here in the first place!
It's true, there isn't a separate Media table in the storage directory (F:\MS Office Data\Access\ ) that I've nominated, but I don't understand how the db is still functioning then. When I open the recordings table I can see and interrogate all the records in that file, and the same for the media table, Where is it drawing data from if not from DVD File.mdb. The C:\Planet Data Bank\Access\Media File.mdb path on your screenshot does exist on my pc, but it's an old storage directory which hasn't been used for several years. My selected saving location is F:\MS Office Data\Access\ (which doesn't contain a copy of Media File.mdb. Could it be that Access defaults to looking for files in the C:\ location anyway?
I've just returned to the db and updated the Media File with another 20 records, when I open the Media File.mdb I see that it has been updated and is the file the db is using. Why, when I have selected the F:\ save location? Couldn't I just move the Media File to the selected F:\ location and delete the C:\Planet Data Bank completely, or will the db try to save the Media File to C:\ which will then be an invalid path?
Thanks Gasman, I really do appreciate the time you're giving me.

You are using a Recordings table in the DB you supplied called DVD.mdb.
The Media table is in a file called Media File.mdb in that path that is shown in pic 3 in post #12.

So locate that file as well and upload that. Now you say that file does not exist?, so I am confused as well as to how all this works. It certainly does not work on my PC, so you must have it somewhere?, so as long as your system is working. Perhaps also check how many copies of DVD file you have on your system, as things are not tallying up as they are.

I use a program called Everything that quickly finds files on my system. Or search for your own link.

If you are the sole user of this DB, perhaps it is better if all tables are local for your understanding?
I'll let others comment on whether this might be a better option for you.

 

[Gasman]

@Woodpecker Have you found that mdb yet?

 

[Woodpecker]

Hi Gasman, I've had a busy few days away from the PC. Last week I tried many times to post screenshots and various attachments but all were met by aa error sign Saying "Oops! Something went wrong, this post identified as being spam. Contact an administrator" The files I were tryng to send were very small 150kB - 2mB. When I attached DVD File.mdb a week ago I had no problems and that was 6mB. Since last posting I've run an Everything search which showed there were 10+ instances of DVD File.mdb on my C\ drive alone! I've now deleted all the redundant and out-of-date instances, & just retained a working copy of of both the DVD and Media files on C:\. Of course, I've altered the settings in Access to reflect these changes. All this now brings me back to my original problem, that of running a query from two linked tables that requires user input. I'm attempting to attach the Media File.mdb again.

 

[June7]

Query references a field that does not exist: Expr1: [Media Table].[Media Type]
Change to: [Media]

I change the filter criteria to remove the LIKE "*" & and then filter works. Since Genre is a single letter, why use wildcard?

If I use LIKE "*D", it also fails, however, LIKE "D*" works. I am not understanding why the popup input fails. But then again, I never use.