i have a created a database to keep track of my movies collection. The main form in this database is a form in which i enter various details about a particular movie. In this same form i have created a table control with 4 pages in which on each page there is a subform. These subforms holds information about that movie and their recordsources come from 4 different queries.

Page 1 has a recordsource qryMovieakaTitles in which it holds additional title names which the movie may be known as
Page 2 has a recordsource qryMovieActors in which it holds the main actors in that movie
Page 3 has a recordsource qryMovieGenres in which it holds the the name of the genres which the movie may fit into
Page 4 has a recordsource qryMovieKeywords in which it holds a set of keywords which are related to the movie


My main problem is that sometime when i come to open the main form frmMovies i am getting a message box stating:

the recordsource "qryMovieakaTitles" specified on this form or report does not exist.

You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.

In the Form or Report's design view, display the property sheet by clicking the properties button, and then set the recordsource property on to an existing table or query.


After clicking ok, This message box repeats itself for the other three recordsources and then the form loads but without the actual info. Then i close and re-open the form, and it shows up fine.

I tried importing the objects to a new database and compact and repair and all went fine, but sometimes this error still crops up, and i have to import the objects again to a new database.

Any ideas what might be causing this problem, and if there is any resolution to resolve this issue?

Thank you for any feedback.

Actually, i am noticing that the above description might not be the main culprit of this problem. The frmMovies form has its recordsource qryMovies, in which it has fields such as MovieID, Title, Length, NoOfDiscs, ProductionCompany, etc.

Eventually the first time i am opening the database when i go to the Queries and try to open the query qryMovies, i notice that instead of the actual data which there is in the underlying table tblMovies, i can only see a series of number in the queries fields i.e. for the first record each field holds the number 1, the second record holds the number 2, and so on. Eventually if i close the query and re-open it, everything appears to be fine, that is the each record displays the actual info and not just the series of number.

Any idea what's going on pls ?

Thank You

any ideas what might be going on please ?

thanks

Hi
Zip a copy of your DB so that we can take a look

Mike

Hi thanks for your reply.

Attached is a sample of my database ... the form which is giving me a problem is frmMovies, where i am getting the message box that all the queries which the subforms are built on are missing, when in fact they are there.

Hi,

I just tested the database on another pc and the same thing has occured, therefore i can conclude that it is a problem with the structure of the database itself rather than a problem with access. Moreover, I have even tried to remove the name autocorrect feature and decompiling the database, however, the problem is still persisting.

By any chance if anyone tries to download the attachment and on the first time the form opens fine, please try to close the database and re-open it, as normally it is there when the problem arises.

Thank you.

Hi Belti

The Dbase works fine for me - have closed and opened a few times and the MainForm frmMovies works fine

Mike

Hi

thank you for your reply.

Can someone point me out what the problem might be on my pc, and what action can i try to sort it out ?

Thank you.

The db opens fine for me too....

But .. I do have a question .. Why do you have your forms bound to queries when you queries do not "do" anything to the data (ie: Filter it, Sort it, Join it, etc) ... you can bind your forms directly to the tables, then you do not have to worry about maintaining two different objects to show you the same data... If your queries "did" something for you, or provided you with something (like the SQL statement of it) ... then, that would be a different story.

If you would like, I can C&R and "Clean" your db, then send it back and see if it would then work on your system?

EDIT ADDED >>>
I aggree 100% with Crystals Comments ... I noticed some of the same things after I posted and had intended to edit but, Crystal beat me to the punch ...

Edited by: datAdrenaline on Sat May 5 0:09:50 EDT 2007.

Hi Belti (what is your name?)

in your relationship diagram, you have two relationships defined for the same combination:

tblActors_1 to tblMoviesActors on ActorID needs to be deleted and then tblActors_1 needs to be deleted from the diagram

I also noticed that you have the DefaultValue of foreign keys still set to 0. Access does this automatically on all numeric fields, but it is good to remove it since 0 will never match up with a sequential Autonumber

for more information, read:
Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access

You should also keep fieldnames consistent when they are the same piece of information -- for instance the "Director" field in Moveies is really "DirectorID"

Another thing that may be throwing a wrench into the works is that you have used Captions in the table design -- they can cause confusion

MovieID is given a caption of "Movie" in several places -- including tblMoviesakaTitles, which is what qryMoviesakaTitles (totally unnecesary) is based on

so, to summarize

1. get rid of the field captions in your table design
2. delete the DefaultValue of 0 for foreign keys
3. base forms directly on tables

(if I were you, though, I'd take Brent up his his generous offer!)

Hello my Friend!

>> 0 will never match up with a sequential Autonumber <<

.. Actually .. it can, if your autonumber is set to increment up, once you get to 2147483647 (max of Long data type - in binary 01111111111111111111111111111111) the next number will be -2147483648 (10000000000000000000000000000000) then, I beleive, it will march right on up and set the autoid to 0. Also, I am pretty sure you can reset a tables' seed value to 0 and thus have the first autonumber be 0.... Or ... if you choose the "Random" autoid, or decide to replicate ... I wonder if 0 is "removed" from the list of availble id's??

I will test again with the "marching up to zero" and report back....

One thing to remember that you CAN set a FK field to Null and create a orphaned child record with no complaints from the Referential Integrity rules!

Hi Brent,

ok, smarty pants!

It will also match up if you do an Append query and PUT a record in there wilth a zero ID

that is "never": under normal circumstances

"create a orphaned child record"

if the data is not filled out, it is easy enough to test -- sometimes, though, the data is not known when the record will be saved -- this happens a lot more often than some random number hitting zero <smile>

I know the arguments for not doing this, but in the REAL world, it happens and the structure needs to accomodate it

anyway, I said "sequential Autonumber" and you argued "increment" -- don't you have 185 other emails to check ?

ROFL!!!! ....

... I just can't stop laughing!!!! ....

>> but in the REAL world, it happens and the structure needs to accomodate it <<

... Here's my real world ... my defaults are set to 0!!!! ... ... I often forget to change, but also, I have the fields set to required, I have been able to design my apps to adhere to this design philosophy.


PS>
... I am actually going to bed now ... I will discuss the IIF() from that other thread tommorrow, I will be testing some stuff and presenting it to the thread.

Hi Brent,

"I often forget to change"

not me, I never forget <smile> I also don't make ANY fields required in the table design as everything is handled by forms -- sometimes I batch-load data in and who knows what it will have?

"going to bed now ... testing some stuff"

so can we anticipate another birth announcement in 9 months?

"discuss the IIF()...tommorrow"

... sweet dreams!

Hi all,

First of all i very much appreciated all of your replies and sorry for my late reply. I would also like to thank you for the valuable suggestions, which i am certainly going to tackle right now. Also i very much appreciate for your suggessted clean up, but i just discovered a new issue which may prove that the problem is not from this particulare database itself.

I have several other database which i created myself and have been using some for months others for ages and never had a problem with. Recently i upgraded my pc and also did a format to my hard disk. However, now i am noticing that when i try to open my older databases my music database for example, when i open the main form frmSongs, for the first record which has an autonumber primary key as 1, i am getting the song title and some other fields all with the value of 1, then i close the form and re-open it and all the info appears correct. The same is happening with my stamp collection database.

Does this means that it might be a problem with my office installation and do you think i should try to repair access or uninstall/install the office ?

Thank you for any suggesstion and all your valuable information.

Hi,

I just tried the following:

created a new database;
created a table with just three fields: (person id - autonumber & primary key, lastname - text and firstname - text)
created a justified form by using the wizard;
in the form i simply entered 3 records with just random names and surnames;

sample data:

1 smith alan
2 jones david
3 smith james

Now when i open the database and try to open the form instead of seeing 1 smith alan, i am just seeing 1 1 1 in the three fields, then i close the form re-open it and the correct info appears fine i.e. 1 smith alan.

From the above testing database i can certainly conclude that the problem is not with my previous database in question but with access itself.

i have even took a step further and tried to use the detect and repair, and uninstall/install office but still to no avail.

Any further suggestions what i may try in order to sort out this strange problem which i have never encountered before?

thank you.

Hi Belti,

sounds like a reinstall is definitely in order

uninstall Office

then install it again

if you have any user files in the office data structure (not likely) under C:\Program Files\Microsoft Office, you will want to move them
~~~

is this what you said you already did? I have found you have to actually remove the program and put it back ...

~~~

What OS (Operating System) is the computer running?

How much memory is in there?

When is the last time you de-fragged the hard drive?

What percentage of free space do you have on the drive?

Hi,

I would like to thank you very much all for your support, but finally i decided to format win xp, as all other previous attempts failed to succeed. After i formatted my pc, and re-installed microsoft office, all my databases seems to be working fine (at least for now )

as regarded the specs info, i was running on winxp sp2, had approx 5GB available space on that partition, 512MB RAM and i run the defragmenter quite often.

Thank you all for your support.

Hi Belti,

you're welcome

glad to hear you've got it working good -- you should still heed the advice that was given to you in this thread

~~~~~~~~~~~~~~~~~~

"had approx 5GB available space on that partition"

this could have been the problem --

IMHO, it is best not to partition these days.
You should always have at LEAST 10% of the drive (each virtual drive too) totally free


~~~
SP2 is a good idea

Do you run utilities ... Use a firewall and anti-virus? Malware and Spyware detector programs? Registry cleaner?

Hi,

Thanks for your reply. As regards any utilities, i do use Zone Alarm as a firewall, AVG as antivirus and ad-aware as an anti-spyware.

The reason why i partitioned the drive is that i had a 120GB and i partitioned it into 20GB for OS & apps and the data 100 for the data files ... this will give me the facilitiy that when i need to format, i can simply format the 20GB only.

thank you very much for your valuable advices.