movie database

I don't have time to review your db today, but thought you might be interested in looking at the D V D Collection db template from the MS web site and see how yours compares. Will follow up in a day or so unless someone else jumps in.

http://office.microsoft.com/en-us/te...i:TC001018644|

Alan

thanks .... i'll have a look at that and get back to you.
I did actually search MS for templates but must have missed that one.

grrr my MS Access 2007 won't run it ... comes up with an error ... I'll try and look at it on 2003 at work

That database actually doesn't look too bad, however, I would prefer the forms to look a little more like the ones I have put into my database.

Also it does not pickup on duplicate entries which is a feature that I MUST have in my database.


A couple of things to explain:
I don't actually want to prevent the duplicate entries at this point .. I just want to catch them so that can then view them. I would still want to be able to force the entry input because at this time I am entering data by disk volume and each volume has several titles.
At a laer point I will want to be able to edit the database and delete records.

Initial review and I have the following information or questions:

I would like the form titled "form open" to open when I run the database.

Under the tools -->Startup select the form you wish to have open when the database opens.

the "add new movie" button opens the add new movie form.

Use the wizard for command buttons to Add a new Record

the search movies button opens a new form to search for a particular movie

Use the command button wizard here to open a new form and then use the following tutorial for developing a customized filter to search for a particular record.

http://www.datapigtechnologies.com/f...tomfilter.html

or use the next tutorial if you want an alternative

http://www.datapigtechnologies.com/f...earchform.html

the show all button should just open the table behind the database.

Use the Form Wizard to build a form bound to the table holding the data and set the form to Datasheet view.

in the add new movie form I would like to use the add movie button to add a new record, when the new record form opens the movie# field needs to generate automatically with the next number in the sequence based upon the last number in the ID field. (thought I could do this by linking the movie# field to the ID field in properties).

Why have a separate movie number. Why not use your ID as your movie number and set it to autonumber in the table.

and obviously the return to main menu button will close the form and return to the "form open" form.

Again use the command button wizard to close the form. Any other form still open will appear from behind the current (form having focus).

Once you have accomplished the above post back with any questions you may have.

Just use the ID number and be sure that it is set to autonumber in your table design. Also, make sure it is set as your primary key.

Alan.

Ok here is the latest updated program.
There are a few things I have had some issues with and some things that I cannot find.

Firstly,
As you suggested I have used the ID field to create the movie number entry however it is defaulting to the number 2 which is the first entry in the field column of the table instead of defaulting to the next new entry number.

second - on the add new movie form itself - you need to click the add new button once to clear the fields to be able to create a new entry and then click it again after entering the details so that it then creates the new entry. If you enter new data and then click the add new button it clears the fields and then creates a new entry.

third - i followed one of the query tutorials (think it was the second one) but it is giving me some problems. could you pls have a look at it and see if you can see any errors.

next - the search query needs to be able to have selectable results or if only one result then open the "edit" form that is already created. (edit form works in full i just cant get the search results to link to it)

also query needs to display duplicate entries - there is a duplicate entry form created as well.

As you suggested I have used the ID field to create the movie number entry however it is defaulting to the number 2 which is the first entry in the field column of the table instead of defaulting to the next new entry number.

So what? Its only a number that is used for tracking. It equates to a title and is used for maintaining a unique value for the record. It should have no other bearing. This is a relational database and not a spreadsheet.

second - on the add new movie form itself - you need to click the add new button once to clear the fields to be able to create a new entry and then click it again after entering the details so that it then creates the new entry. If you enter new data and then click the add new button it clears the fields and then creates a new entry.

Looked at your code for the button. Reverse the order of the docmds. You want to open the form first and then add a new record.

Your third item. You have named your form "Search Movies" but in your query you are directing the criteria to look at the form "SearchForm" which does not exist.

I don't understand the need for a form to edit the results as you can edit the results directly in the query data sheet.

If you want a duplicates query, use the query wizard to create a duplicates query.

I also don't understand the need for a disk nr. in your table and search. BTW, you should change your field names to not include # sign. Access doesn't like special characters in field names. Here is link to reserved names and characters

http://support.microsoft.com/kb/286335


Alan

Alan

I am not at all sure why your query does not work. But I did find one irregularity. Your combo box for the Rating is bound on column one of your table (the ID), however your movie listing table has a text value for ratings. You have a data mismatch in your query that needs to be resolved. You are using a number request in your query but the table containing the movies has a text rating. This will never return results.

Your add a movie form is unbound to your table. It will never update to the table unless you either bind the form to the table. Then it will update automatically. Or you can write some code to have the unbound form populate the table. Look at the data pig site for how to do this. He has a tutorial.

Having said all this, have you taken any classes on Access or studied any books. Your process seems to be disjointed. I suggest you get a book or do some research on this and find a tutorial -step by step - or you will just be spinning your wheels for a long time to come.

I cannot find anything else wrong with the query (search). It has to be in the table setup and population. Why it is not generating results confuses me. You may want to start over with a clean table -- build some input forms using the wizard that are bound to the table. Then create your search query. Oh yes. Run a compact and repair regularly during development. Otherwise, your db will become bloated.

Alan

OK here we go again.
I recreated the whole thing from scratch and everything appears to work EXCEPT my search query string and form.

The search query returns NO results whatsoever.
I don't understand it ... I followed the tutorial exactly - obviously swapping my search form name in place of the one in the datapig tutorial.

Ok, playing around a little more I have found this:

With the search queries in design view showing all on the criteria line (so in sql view they are using AND statements) the search returns NO results .... if I change it so that it uses an OR statement then searching by volume number returns only the relevant matching entries eg searching for 1 will return 1, 13, 15 etc but searching using any of the other fields will return all results.

I am completely lost


I found a solution (well at least I think I did):

http://support.microsoft.com/kb/209645/en-us

I think that I may have fixed it using the information in the KB you supplied. Nice finding. I've attached. Look at the query. I think that you may not have clearly understood the KB.

Alan

Alan,
Thanks for that, I have had a look at the database you attached and run some test searches however it is still returning the same incorrect search results as my original search criteria:
* When searching by volume it returns all results.
* When searching by title it returns the results that match the search string but also returns any empty rows as well.
* When searching by rating it also returns the relevant results PLUS any empty rows as well.


If you run some test searches on the last database that I attached I hope that you will find - as I did - that the search problems have been resolved.
All of my searches are now returning correct results.