movie database

**vampyr07au** · 04-16-2011, 11:06 AM

I am trying to create a database of my movies.

I have created the table that will hold the data and I have created a form to enter the data however I am having trouble figuring out how to make the data entry work.

Attached is a copy of the database.

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

the "add new movie" button opens the add new movie form.
the search movies button opens a new form to search for a particular movie
the show all button should just open the table behind the database.

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).

also want to know if it is possible to have the data verified as being unique before the entry is added and if it is a duplicate entry a window opens showing the duplicate entries

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

Search button to open the search movies form.

**alansidman** · 04-16-2011, 12:04 PM

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

**vampyr07au** · 04-16-2011, 05:09 PM

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

**vampyr07au** · 04-16-2011, 11:39 PM

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.

**vampyr07au** · 04-17-2011, 09:19 AM

I hope this might help to make things clearer.

Attached is a diagram of the way I want things to work.

**alansidman** · 04-17-2011, 10:23 PM

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.

**vampyr07au** · 04-17-2011, 10:31 PM

This reply is awesome !!
Thank you very much .... I will start working through all of this later today or tonight ...

to answer your one question:
"Why have a separate movie number. Why not use your ID as your movie number and set it to autonumber in the table."
That is what I wanted just wasn't sure how to do it.
So do I need a seperate column title movie number for that ? or can I just refer the movie number box to the ID column and use autonumber value ? (hope I'm making sense lol)

**alansidman** · 04-18-2011, 07:06 AM

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.

**vampyr07au** · 04-18-2011, 12:04 PM

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.

**alansidman** · 04-18-2011, 02:29 PM

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

**vampyr07au** · 04-18-2011, 08:45 PM

Originally Posted by alansidman

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.

I didnt think of it like that.

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.

.
OK will fix that .. thanks

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.

OK will fix that too

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.

good point and again something i didnt think of.

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

ok will have a look at that.

[quote]I also don't understand the need for a disk nr. in your table and search.[multiple movies on them and the disks themselves are all numbered/quote]
the disk number is because i have a large number of disks with

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

ok will fix that also ... thanks

appreciate your responses and I will post again with the results of your suggestions and answers.

thanks heaps for that

**vampyr07au** · 04-19-2011, 11:03 AM

OK ... I made some mods to the database .... removed some of the forms and fixed the special chars issue.

Also tried to test everything else.

The add new movies button from within the add new movies form doesnt seem to want to add the new movie.

Also the search query still doesnt seem to be working right.
The way I understand it - its supposed to find ANY entry similar to whatever I enter in any of the search criteria however it isn't returning all the results properly.

Also need to know how to make sure that I cannot mistakenly edit an entry from within any search results.

**alansidman** · 04-19-2011, 04:08 PM

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

**vampyr07au** · 04-19-2011, 08:10 PM

Ok thanks.
I'll have a look at everything you have said.

I did some access and sql a long time ago but not recently.

I thought that I had actually bound the rating combo box to the second table I had created for ratings only.

Also thought that the buttons I created using the wizard were already bound to the table.
I did use the data import to bring most of the data that was in that table from excel prior to beginning to develop the forms etc.

I will look at starting over again and see how we go.

**vampyr07au** · 04-20-2011, 11:59 AM

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

**alansidman** · 04-21-2011, 08:02 PM

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

**vampyr07au** · 04-22-2011, 02:42 AM

ok I have a few more questions:

1st - I want to set the column widths that display on the datasheet that opens when I view all my database entries or view the search results.
I found the following code

Forms![FormName].ColumnWidth = -2

which is supposed to set the column width to be the same as the largest text entry.
The site I got the code from says that we use it in the OnLoad event but I cannot see the datasheet itself in my vba code viewer.
Can someone explain how I can do this pls?

2nd - I want to lock the datasheets from being able to be edited by anyone when viewing but would also like to have a way to have an editable record as well - so an edit button on the main menu.
How can I do this - I have been searching for ages and cannot find anything to help me.

**vampyr07au** · 04-22-2011, 02:50 AM

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.

**vampyr07au** · 04-22-2011, 03:09 AM

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.

**vampyr07au** · 05-01-2011, 06:46 PM

this thread is now complete and all issues solved. I ended up using the following code for the search button:

Private Sub Command7_Click()
  
    Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] = " & Me.Volume & " AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] LIKE '*" & Me.Title & "*' AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & "' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If


    End Sub

Hopefully this might help someone else.

All the "like" criteria that allow the searches to be done with parial entries are found in the query.

movie database

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Re: movie database

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions