# Solved: Movie Database



## computerman29642 (Dec 4, 2007)

I have attached a movie database that I have been working on. I would like for you all to review the design, and let me know if I am on the right track.


----------



## OBP (Mar 8, 2005)

What you again 
Can I suggest that instead of a "star" field that you have a "Starring" table related via the M_MovieID field.
You would then want to have a Search Form for "Wild Card" searching of Film Names and also "Starring" to answer questions like "Do you have any films with Fred Bloggs in it?"

I am also not sure that you actually need 2 Checkout tables, I think that just one with and in and out date would suffice.

You will want reports on Items that are "IN" and "Out" and due back within a certain period,.
How about a "Booking or Reserving field" so that Customers can reserve movies that are "out"?


----------



## computerman29642 (Dec 4, 2007)

LOL...Yeah, I keep coming back don't I? 

I will implement the suggestions you have made. I am going to try and do this database mostly myself and just use you all as crtitics. I really want to get better at Access, so I thought I would build a db for my movie colection. What a better way to learn....my movies get organized and I learn Access at the same time....


----------



## OBP (Mar 8, 2005)

That is one of the best ways, because you know the subject well and what you want it to do. :up:


----------



## computerman29642 (Dec 4, 2007)

Why would you recommend a Starring Table instead of having that information in the Movie table?


----------



## computerman29642 (Dec 4, 2007)

> How about a "Booking or Reserving field" so that Customers can reserve movies that are "out"?


 This is just a personal movie colection, so it does not need to be this advance.

My wife and I have learned that it is a good thing to kepp track of when someone borrowed the movie and who actually borrowed the movie. We have lost many movies by not keeping track.


----------



## OBP (Mar 8, 2005)

The "Starring" table would allow you to enter a number of "stars", you can do the same thing in one field by seperating them by commas or something, but it is less elegant and not good database design.


----------



## computerman29642 (Dec 4, 2007)

> The "Starring" table would allow you to enter a number of "stars"


How would this be done? I am assuming that when you say "enter a number of stars" you mean more than one star for a movie. Would that be correct?


----------



## OBP (Mar 8, 2005)

Yep, subform required.


----------



## computerman29642 (Dec 4, 2007)

Will there need to be more than one star field within the table?


----------



## OBP (Mar 8, 2005)

No, just movieID


----------



## jimr381 (Jul 20, 2007)

Because one movie will have many stars...... hopefully.


----------



## computerman29642 (Dec 4, 2007)

I have worked on the movie database a little more. Please review the design and fuctionality of what I ahve done, and let me know what you all think.


----------



## jimr381 (Jul 20, 2007)

I see the stars and movies being setup in a Many-To-Many relationship, since one star can be in many movies and one movie might have many stars.

Are you not going to be charging them for checking out the movies?

What form is your main form?


----------



## computerman29642 (Dec 4, 2007)

So, are you saying that I need a Many-To-Many relationship?

No, I will not be charging anyone for borrowing the movies. This is just basically a way for me to organize my movies, search my movies, and know who borrowed by movies.

As of right now, the frmMovies form is my main form.


----------



## jimr381 (Jul 20, 2007)

Aye I would organize it as a many to many relationship since it should feasibly be setup like I said before. 
I see you have other forms. Are you planning on making a switchboard to access these or just using the database window?


----------



## computerman29642 (Dec 4, 2007)

I have not decided on the switchboard. I have thought about creating one, but I have not made my mind up yet. What do you think?

I will change the relationship to Many-To-Many. I still have not figured out how you all were talking about setting up the frmAddMovie form to select more than one star.


----------



## jimr381 (Jul 20, 2007)

I think OBP was talking about the form. Isn't it ya'll down there instead of you all?


----------



## computerman29642 (Dec 4, 2007)

LOL....Well, I guess that would be a true statement. Every now and then we can speak with proper grammar down here. 

The correct way to setup a Many-to-Many relationship would be through a junction table...right?


----------



## jimr381 (Jul 20, 2007)

Aye it would utilize a junction. I am from VA and that is supposed to be a southern state as well.


----------



## computerman29642 (Dec 4, 2007)

I will create the junction table and relationship(s), and then repost the db. I want to be sure it is done correctly.

LOL....Why you given me a hard time then?


----------



## computerman29642 (Dec 4, 2007)

I have created the junction table (tblMovieStar). Please take a look at the relationship(s), and let me know if I now have a Many-To-Many relationship established.


----------



## computerman29642 (Dec 4, 2007)

How do youknow if a Many-To-Many relationship has been established?


----------



## jimr381 (Jul 20, 2007)

There is not really something called a many-to-many relationship when you examine the relationship via the window. You just know that you have two one-to-many relationships that are hitting a junction table.


----------



## computerman29642 (Dec 4, 2007)

When I tested the junction table by creating a new movie, no data goes into the junction table.


----------



## computerman29642 (Dec 4, 2007)

By chance did you happen to take a look at the attached dataabase?


----------



## jimr381 (Jul 20, 2007)

The cast should be setup in a subform, so that you can select multiple cast members for the movie. This subform will reference the junction table. I would setup the fields in the junction table as a drop-down list that references the star table.


----------



## computerman29642 (Dec 4, 2007)

I will work on getting that done. Thanks.


----------



## computerman29642 (Dec 4, 2007)

The subform would be added to the frmAddMovie form...correct?


----------



## jimr381 (Jul 20, 2007)

Aye. I was still adding information to my previous post when a client asked a question, so you might want to reread it.  You also are missing X as a rating for the movies as well, unless you do not loan those out.


----------



## computerman29642 (Dec 4, 2007)

jimr381 said:


> The cast should be setup in a subform, so that you can select multiple cast members for the movie. This subform will reference the junction table. I would setup the fields in the junction table as a drop-down list that references the star table.


I will get started on this. Thanks.


----------



## computerman29642 (Dec 4, 2007)

I believe I have something really wrong. I tried to place the subform on the frmAddMovie form with the subform linked to the tblMovieStar table (Junction Table). However, no values are present in the combo box on the subform.

I have a StarringID in three different places. It is found in the tblStarring table, tblMovie table, and tblMovieStar table. Do I need all three?


----------



## OBP (Mar 8, 2005)

I haven't seen your latest creation, but I would think it shouldn't be in the Movie table, the Link should be form the Movietable (MovieID) to the Junction table and from the Starring table (StarringID) to the Juction table.


----------



## computerman29642 (Dec 4, 2007)

I deleted the StarringID from the tblMovie Table. I believe something is still really screwed up. I have attached a copy of the database. I would really appreciate you taking a look.


----------



## OBP (Mar 8, 2005)

You are correct, it is well screwed up 
One major problem that you have is the form that you are using does not assign a value to the MovieID autonumber until you have clicked the "Add" button, which means that you although you can assign a Master/Child Link between the Main and Sub forms using the MovieID, there is nothing there for the Subform to Pick up on.
So I have corrected the data source for the Subform, added the Actual fields from the table to mainform for the MovieID and Movie Title, linked the 2 forms and it is now working.
But your Tabbed "Movies" form isn't 

PS I like the String conversion using Propercase.


----------



## computerman29642 (Dec 4, 2007)

LOL....I had a feeling that I played with it too much. I appreciate you taking the time to correct the mistakes. 

I will work on getting the tabbed "Movies" form working.



> PS I like the String conversion using Propercase.


I guess you felt like a positive statement needed to be thrown in somewhere....LOL!


----------



## OBP (Mar 8, 2005)

No, I actually hadn't seen that fuction used before, so I learnt something new, as I am always doing helping others.


----------



## computerman29642 (Dec 4, 2007)

I am glad that I was able to actually show you something. 

I have the CategoryID and RatingID in the Movie Table, do they need to be deleted? There is a way to have all the stars display on the Movie form without showing duplicate records...correct?


----------



## OBP (Mar 8, 2005)

No you need those IDs in the table to refer to the 2 tables.
I am not sure that I understand your question about Duplicate Stars, do you have some?


----------



## computerman29642 (Dec 4, 2007)

The Movie form is not duplicating the Stars. When a movie has more than one Star, all of the data is being duplicated in the subform of the Movie form. I was just making sure that was something I could stop from happening.

Would it be better to have the Stars showup in a Listbox?


----------



## OBP (Mar 8, 2005)

You can, as you appear to like List boxes,  but personally I prefer Subforms.


----------



## computerman29642 (Dec 4, 2007)

I do not mind using subforms, but I would like for the data not to duplicate.


----------



## OBP (Mar 8, 2005)

Preventing duplication is not quite so straightforward as normal as this is a many to many table.
One way to achieve it would be to have another field that combines the MovieID and StarID and then set it's property to Indexed No Duplicates.


----------



## computerman29642 (Dec 4, 2007)

I will see if I can create the new field that will combine the data. I personally believe it would look better. What do you think?


----------



## OBP (Mar 8, 2005)

I am not sure about the "look" of it, I usely just do "Function"


----------



## computerman29642 (Dec 4, 2007)

Is it possible to have both?


----------



## OBP (Mar 8, 2005)

Yes, but don't ask me


----------



## computerman29642 (Dec 4, 2007)

> One way to achieve it would be to have another field that combines the MovieID and StarID and then set it's property to Indexed No Duplicates.


Are you saying to make a new field within the table or form?


----------



## OBP (Mar 8, 2005)

Both, but in the form add some VBA to the Star Combo After Update event that concatenates the the 2 ID fields in to the new field, like
me.Newfieldname = me.j_MovieID & "-" & me.j_StarringID


----------



## computerman29642 (Dec 4, 2007)

I did as you suggested. I am getting negative numbers (-4 & -5) for values in the new field on the form. Is this correct?


----------



## OBP (Mar 8, 2005)

There must be something wrong with your VBA syntax, you should end up 
4-5


----------



## computerman29642 (Dec 4, 2007)

I will take another look. Thanks.


----------



## computerman29642 (Dec 4, 2007)

Okay, I have that issued resolved. Now, when I go to select a second star, the data in the new field changes for the first star.

For example, the first start Information was "16-5", but when the second star was added the values changed to "16-4".


----------



## computerman29642 (Dec 4, 2007)

I believe I figured out what I did wrong.


----------



## OBP (Mar 8, 2005)

Didn't "Bind" the field to the one in the table?


----------



## computerman29642 (Dec 4, 2007)

Exactly...LOL!

However, the duplication is still going on.


----------



## OBP (Mar 8, 2005)

Have you set the Field to "Indexed" and "No Duplicates"?


----------



## computerman29642 (Dec 4, 2007)

Yes.


----------



## OBP (Mar 8, 2005)

So how do you get duplicates then?


----------



## jimr381 (Jul 20, 2007)

When you are setting up the two fields as a primary key you will want to select both of them before you click on the key icon to associate them as a primary key.


----------



## computerman29642 (Dec 4, 2007)

I am not sure.


----------



## jimr381 (Jul 20, 2007)

Can you post your latest revision of the database, so I can take a look at it? I cannot promise that I will get to it today, since I was out yesterday and everyone wants me today, but I will look at it on Monday.


----------



## computerman29642 (Dec 4, 2007)

> When you are setting up the two fields as a primary key you will want to select both of them before you click on the key icon to associate them as a primary key.


Which fields?


----------



## jimr381 (Jul 20, 2007)

You are trying to alleviate duplicates in your junction table right or which table are you trying to alleviate duplicates in?


----------



## computerman29642 (Dec 4, 2007)

I am trying to stop duplication on the frmMovie form. Please take a look at the attached database. You will noticed that when the frmMovie form is opened, there is duplication.


----------



## jimr381 (Jul 20, 2007)

I see nothing when I open up the frmMovie form.


----------



## jimr381 (Jul 20, 2007)

Why do you have a movie number and movieID as well? Is movie number for cataloging them? It seems like replication to me. Your frmAddMovie seems to be still setup wrong. It should be a subform and not a drop-down list, so that you can see the whole list of stars.


----------



## OBP (Mar 8, 2005)

Ah now I see, the duplication comes from the Query, Not the table.


----------



## computerman29642 (Dec 4, 2007)

Are you referring to the qryStarring query? Should I be using the MovieStar query?


----------



## computerman29642 (Dec 4, 2007)

> Why do you have a movie number and movieID as well? Is movie number for cataloging them? It seems like replication to me.


Yes, the movie number is for cataloging. I have all my movies have labels with numbers written on them. So, I used the autonumber filed to index withthin the database, and the other number field to search for the movies.


----------



## computerman29642 (Dec 4, 2007)

> I see nothing when I open up the frmMovie form.


The duplication I am talking about is that of the description field, rating field, etc.


----------



## jimr381 (Jul 20, 2007)

Are you using a different database then I am using, because I see no data within the frmMovie.


----------



## computerman29642 (Dec 4, 2007)

I am using the same one I attached. There should be three movies. Test, Test2, & Tester. Do you have any movies listed in the tblMovie table?


----------



## jimr381 (Jul 20, 2007)

No I do not and do not see them in the drop-down list either. When I add my own in there I do see it with the supporting information and it is not duplicating it as well.

Did you notice that when you click on the "Add Information" button that the drop-down list at the top of the form goes away?


----------



## computerman29642 (Dec 4, 2007)

Are you looking at the database I attached in Thread #65?


----------



## jimr381 (Jul 20, 2007)

I had a duplicate of your zip file on my desktop.


----------



## computerman29642 (Dec 4, 2007)

> Did you notice that when you click on the "Add Information" button that the drop-down list at the top of the form goes away?


Yes, that is what I want it to do. However, I have not gone back to fix the issue of it not showing back up when the first tab is clicked again.


----------



## jimr381 (Jul 20, 2007)

You have multiple stars in each of those movies so it is displaying a record for each.


----------



## computerman29642 (Dec 4, 2007)

That is what I am trying to correct.


----------



## jimr381 (Jul 20, 2007)

I personally would setup the primary subform to reference the movie table and not the query and then a subform within that to reference the junction table which will be showing all the stars.


----------



## OBP (Mar 8, 2005)

I think Jim is looking at AddMovie
You can't include the stars in the Subform the way that you have without having duplicates in the Movie Subform.


----------



## computerman29642 (Dec 4, 2007)

So, I will need to re-evaluate the design of my database?


----------



## jimr381 (Jul 20, 2007)

I am working with it right now to touch it up a little bit.


----------



## computerman29642 (Dec 4, 2007)

Thank you.


----------



## jimr381 (Jul 20, 2007)

OK I changed a few things in this one. The Star's name should be a text field instead of a memo field. I added numerous drop-down lists to alleviate data entry and for display purposes within the tables.


----------



## computerman29642 (Dec 4, 2007)

Thanks Jim. I will take a look right now.


----------



## computerman29642 (Dec 4, 2007)

Do the Category & rating fields need to be dropdown boxes? The frmAddMovie form has stopped working?


----------



## jimr381 (Jul 20, 2007)

For it to display the data correctly then yes. Typically it holds the number in the field. With the drop-downs established it display the corresponding text. I will look at your add movie form. Why do you turn off moving and sizing?


----------



## computerman29642 (Dec 4, 2007)

> For it to display the data correctly then yes. Typically it holds the number in the field.


Is there a way to disable the drop down? Does the subform need to be in datasheet view?



> I will look at your add movie form.


Thank you.



> Why do you turn off moving and sizing?


Habit


----------



## jimr381 (Jul 20, 2007)

gamecockfan said:


> Is there a way to disable the drop down?


Aye setup the field as a text box instead of a drop-down list within the form. Why exactly would you want to disable the drop-down lists anyhow? I would use the movie form to proof the data as well, so if you flub it you can make changes to it right there. Selecting from the list is a whole lot easier than typing it out and leads to "fat fingering" or data entry mistakes.



gamecockfan said:


> Habit


The first thing I do when opening up a form is double-click on the title bar to maximize it. You might want to inquire and see if others do that as well. If they do it might perturb them more to lock that feature down.


----------



## computerman29642 (Dec 4, 2007)

The frmMovie form was to be used just for display, and not really for data entry. If someone was to use the database other than me, I do not want them to be able to change anything. A firend may want to see what movies I have, and see the information pretaining to that movie, but I do not want them to be able to change the information.

That is why I had the second tab with all the Add buttons. Eventually, I was going to apply a password tothat tab or the buttons on that tab.

If the dropdown box is chaged to a textbox, can the name still apear instead of the number?


----------



## jimr381 (Jul 20, 2007)

Ok setup the subform to not allow for edits, additions or deletions and it should be for data viewing purposes only. You will not a separate form for editing your movies that you have flubbed up. No need to change the type of item it is, just do what I stated above.


----------



## computerman29642 (Dec 4, 2007)

> The first thing I do when opening up a form is double-click on the title bar to maximize it. You might want to inquire and see if others do that as well. If they do it might perturb them more to lock that feature down.


I will give that some thought. I guess it would be nice for anyone who uses the database to be able to view the forms as they like and not just how I like.


----------



## computerman29642 (Dec 4, 2007)

> No need to change the type of item it is, just do what I stated above.


 Which statement above are you referring to? Do you mean to just change the dropdown to a textbox?


----------



## jimr381 (Jul 20, 2007)

gamecockfan said:


> If the dropdown box is chaged to a textbox, can the name still apear instead of the number?


That statement.


----------



## computerman29642 (Dec 4, 2007)

> Ok setup the subform to not allow for edits, additions or deletions


This has been done.



> it should be for data viewing purposes only.


Do you mean design view only?



> Originally Posted by gamecockfan
> If the dropdown box is chaged to a textbox, can the name still apear instead of the number?
> 
> That statement.


I believe this mean leave the fields as they are (drop down boxes)?


----------



## jimr381 (Jul 20, 2007)

You will see the names or whatever they are referencing if you leave the drop-down fields established like I have.


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database. I believe the only change made is that I turned the "Starring" field on the frmMovie form into a list box.  

However, it displays all the stars and not just the ones that belong to the movie.


----------



## computerman29642 (Dec 4, 2007)

I just noticed that the cboTitle combobox on the frmMovie form does not requery the way it should. When the form is first opened, the movie title "Test" is displayed. However, if you click the dropdown box, you will notice that the movie title "Me" is first in the list.


----------



## jimr381 (Jul 20, 2007)

I got a massive sinus headache, so I am kind of veggining out until I go home. I will take a look at it tomorrow.


----------



## computerman29642 (Dec 4, 2007)

Sounds good. :up: I hope you get to feeling better.


----------



## jimr381 (Jul 20, 2007)

The list box is fine. The only reason why I set it up the way I did before is because I thought you would want to list multiple stars. With the way you have it setup now you cannot select multiple stars.


----------



## computerman29642 (Dec 4, 2007)

I am a little confused.



> The only reason why I set it up the way I did before is because I thought you would want to list multiple stars.


I placed the listbox there so that when a movie title is selected, the user would be able to see the list of stars that are in that movie. Will that not work with the listbox on that form?



> With the way you have it setup now you cannot select multiple stars.


What do you mean by select multiple stars? Why would I want to select multiple stars?


----------



## jimr381 (Jul 20, 2007)

Because some movies a leading lady and a leading man. If you can only select one star then you cannot address this. Take for instance the movie departed. It had these stars in it:


Leonardo DiCaprio ... Billy Costigan
Matt Damon ... Colin
Jack Nicholson ... Costello
Mark Wahlberg ... Dignam
Martin Sheen ... Queenan
Ray Winstone ... Mr. French
Vera Farmiga ... Madolyn
Anthony Anderson ... Brown
Alec Baldwin ... Ellerby
Kevin Corrigan ... Cousin SeanJames Badge Dale ... Barrigan
David O'Hara ... Fitzy (as David Patrick O'Hara)Mark Rolston ... Delahunt
Robert Wahlberg ... Lazio - FBI
Kristen Dalton ... Gwen
You could say that you had tons of starpower in the movie. If you only list Dicaprio then you would be missing many other stars. I pulled this from imdb.


----------



## computerman29642 (Dec 4, 2007)

I see what you are saying. Is there a way to take the format you had, and modify it so that all the stars in the movie could be visible at the same time on the form. As it stands now, the user would have to click the dropdown box.


----------



## jimr381 (Jul 20, 2007)

You would nest a subform within the other subform which will list all of the values in the junction table. OR 
If you do not have a subform established then you would just establish one that references the items in the junction table.


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database. I am unable to get the frmMovie combobox to display in ascending order, and when a new movie is added, the combobox does not update until the form is closed and re-opened.

Do you think I should keep the add buttons on the second tab, or place them on the frmMovie form in the footer?


----------



## computerman29642 (Dec 4, 2007)

This may be a really stupid question, but is there a way to change the color of the highlight when a record is selected?

For example, when the frmMovie form opens the first record to be select is the Description field. The highlight is black so it makes it hard to read.


----------



## jimr381 (Jul 20, 2007)

I would add the "add" buttons to a sidebar on the right of the main page. I have not figured out why the filter field that you have at the top will not show them in alphabetical order, since you did sort within the parent tables. I know that it is displaying based upon how you enter them, so it seems like it is still wanting to sort via the primary key. Maybe you can make a query based upon the table sort the data within that and have that be the data source for the form.


----------



## computerman29642 (Dec 4, 2007)

Is there something called a sidebar in Access, or do you mean just to place them on the side of the main form?


----------



## OBP (Mar 8, 2005)

Reference the Highlight, add this to the m_Description's GotFocus event
Me.m_Description.SelStart = 0


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Reference the Highlight, add this to the m_Description's GotFocus event
> Me.m_Description.SelStart = 0


That worked.  What exactly does that code do?


----------



## OBP (Mar 8, 2005)

It sets the "Text Insertion Point" for the field, if you change the 0 to a value you will see the cursor move.


----------



## computerman29642 (Dec 4, 2007)

Once again you have taught me something new. Thank you.


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database. I have set the frmMovie form (Main form) to the qryMovie query. I have the qryMovie query m_Title set to ascending order, as well as, the m_Title query found under the combobox.

I was not sure what you meant about the side bar, so I have placed the command buttons on the side of the main form. Please let me know if this is what you had in mind.

I have also deleted the second tabbed sheet.

Once you all have given me the thumbs up on the movie side, I am going to begin the customer checkout side.


----------



## jimr381 (Jul 20, 2007)

A sidebar is just a navigation bar on the side. You just manually set it up.


----------



## computerman29642 (Dec 4, 2007)

> A sidebar is just a navigation bar on the side. You just manually set it up.


Okay....Good. That is what I did. 

I just added this code to the frmMovie form...


```
Private Sub cmdMovie_Exit(Cancel As Integer)
    Me.cboTitle.Requery
    Me.Form.Requery
End Sub
```
I believe the combobox is updating once a movie is added now.


----------



## computerman29642 (Dec 4, 2007)

I am in the process of creating the Checkout data entry form, but I am unsure if I should have a CheckIn Table and a CheckOut Table? If I am only going to have one table, should I create a junction table to link the Customer or Movie Table?


----------



## jimr381 (Jul 20, 2007)

You will have a junction table to link the two. I would just have a check-in check-out date field. When you total the field and search through them any even amount of dates entered would have an item checked out and any odd would be checked in. That would be tied to a first initial check-in when you put it in database. If you are not going to check them in initially then it would be the exact opposite so all items when counted that are odd are checked out.

You definitely would want to get more feedback though since I have never done a data warehousing database before.


----------



## computerman29642 (Dec 4, 2007)

This database does not need to be complex. It is just basically a way for me to organize my DVD collection, as well as, keep track of people who borrow my movies.


----------



## computerman29642 (Dec 4, 2007)

> You will have a junction table to link the two.


Would I need just one junction table that connects all three tables (Movie Table, Customer Table, Checkout Table), or a junction table only to connect the Customer Table and Checkout Table?


----------



## jimr381 (Jul 20, 2007)

The junction table will be the checkout table. It will hold the primary keys for the Customer and the Movie table along with the checkout/checkin date.


----------



## ~Candy~ (Jan 27, 2001)

After you guys get this all set up, can you email me a copy?


----------



## jimr381 (Jul 20, 2007)

AcaCandy said:


> After you guys get this all set up, can you email me a copy?


It will cost you.


----------



## computerman29642 (Dec 4, 2007)

jimr381 said:


> The junction table will be the checkout table. It will hold the primary keys for the Customer and the Movie table along with the checkout/checkin date.


That makes sense. I have the junction table setup as you described. Thanks.


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> After you guys get this all set up, can you email me a copy?


It will cost you big.


----------



## computerman29642 (Dec 4, 2007)

In your opinion, how would you do the Checkout form? Would you place another tab on the main form where the user can select a customer, and the checked out items display in a subform or just place a form on a second tab that shows all records?


----------



## jimr381 (Jul 20, 2007)

I would probably do a subform within another tab or at the bottom of the main form.


----------



## computerman29642 (Dec 4, 2007)

I was kind of thinking another tab as well.


----------



## ~Candy~ (Jan 27, 2001)

That would be my tab  Put it on my tab


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> That would be my tab  Put it on my tab


LOL....You got it!


----------



## computerman29642 (Dec 4, 2007)

I am not sure of the best way to create the new tab.

I was planning to make the cboTitle combobox visible property set to false, and then have a cboCustomer combobox that will have a visible property set to true. However, the main form is tied to the qryMovie query.

Would this be a good path to continue down?

Please review the attached database.


----------



## computerman29642 (Dec 4, 2007)

I was thinking that using the existing cboTitle dropdown box on the main form would be better than trying to hide one and make another visible. What do you think?

However, I cannot get the cboTitle dropdown box to work for both tabs, When I select a title from the dropdown box, the form keeps jumping to the first tab.Also, the dropdown box is not populated with the first selection when the form is first opened.


----------



## jimr381 (Jul 20, 2007)

The drop-down will be fine for selecting. You would want to check and see if you could actually add records to the sub-form or see if would be setup as read-only since it is piggybacking off of a query.


----------



## computerman29642 (Dec 4, 2007)

Would it be better to create a separate query?


----------



## ~Candy~ (Jan 27, 2001)

Aren't you guys done yet?  I'm ready to start my moving listing


----------



## computerman29642 (Dec 4, 2007)

Hey...Greatness takes time! 

As soon as, I figure out how to keep track of when people borrow the movies, the database will be done. 

If you just want a database to list your movies, then the database should be ready to go. :up:


----------



## ~Candy~ (Jan 27, 2001)

I might need the borrow part too


----------



## computerman29642 (Dec 4, 2007)

LOL! I will be sure you get a copy once I have everything worked out.


----------



## OBP (Mar 8, 2005)

What is taking you so long?


----------



## computerman29642 (Dec 4, 2007)

LOL...I just want to make AcaCandy wait.  

I cannot seem to get the cboTitle to work correctly with the second tab.


----------



## OBP (Mar 8, 2005)

Have you posted the latest version?


----------



## computerman29642 (Dec 4, 2007)

I have attached the latest version of the database. if you have a suggestion as to what might work better, please let me know.


----------



## OBP (Mar 8, 2005)

Like this?
Check out PS I Love You.


----------



## computerman29642 (Dec 4, 2007)

How do I get the database to remain on the second tab when I select a movie title? If I select the movie title while on the second tab, it jumps to the first tab.

In your opinion, would you have a command button that would open a form to enter the checkout information, or just enter the information from the second tab?


----------



## computerman29642 (Dec 4, 2007)

Also, the movie title stopped displaying when the database is first opened since the second tab was added.


----------



## OBP (Mar 8, 2005)

In the Movie Combo that selects the Movie title there is an Afterupdate event , it's last line Sets the Focus on the movie info Tab, just delete it.
The Tab works fine like this, check out Saw 2.


----------



## computerman29642 (Dec 4, 2007)

That works great. Thanks.

Is there a way to still have the combo box populated when the form is first opened. I believe the problem is with the conflict with MovieID & jcc_MovieID.


----------



## OBP (Mar 8, 2005)

Delete the VBA Code in the frmCheckOut/In form's On Current Event Procedure, it does not need to be a new record.


----------



## computerman29642 (Dec 4, 2007)

That did the trick. 

I was thinking about adding a Yes/No field to the movies table that will let me know if the movie is available or not. Would this be hard to do?


----------



## OBP (Mar 8, 2005)

Yes nice and easy, have the Booking Out set it on and the Booking In re-set it off.
Also do you want to show the Booking Out/In for movie records that have been returned?


----------



## computerman29642 (Dec 4, 2007)

> Also do you want to show the Booking Out/In for movie records that have been returned?


What exactly do you mean?



> Yes nice and easy, have the Booking Out set it on and the Booking In re-set it off.


Do this through code on the frmMovie form or sfrmMovie form?


----------



## OBP (Mar 8, 2005)

Well on the Booking out Form do you want to show all the old Bookings in and out or just the ones that are still out?
I would add the Tick box to the table and then the mainform, it can be above the tabs and set to invisibe. The booking form can then set it.


----------



## computerman29642 (Dec 4, 2007)

> Well on the Booking out Form do you want to show all the old Bookings in and out or just the ones that are still out?


I guess I would like to see both the in and out.

The onjective is to have a fast way to see what is out, and who has it out.


----------



## OBP (Mar 8, 2005)

Include the Out Tick Box on your Movie Combo as well and you can see at a glance which ones are out, you can then check on the Form who has it.


----------



## computerman29642 (Dec 4, 2007)

> Include the Out Tick Box on your Movie Combo as well and you can see at a glance which ones are out, you can then check on the Form who has it.


Please review the attached database. I believe I have done as you suggested.


----------



## OBP (Mar 8, 2005)

Try this.


----------



## computerman29642 (Dec 4, 2007)

Okay...If there is a 0, then the movie is checked out and if there is a -1 then the movie is not checked out...correct?

Why does the movie 'Along Came Polly' have a -1, but the return date is null?


----------



## OBP (Mar 8, 2005)

Just me testing.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Just me testing.


Testing?


----------



## OBP (Mar 8, 2005)

I was testing both the Check Box and the Date fields to make sure they switch on and off.
I was also deleting data in the Table.


----------



## computerman29642 (Dec 4, 2007)

Instead of showing a 0 or -1, could it display Available or Unavailable?


----------



## OBP (Mar 8, 2005)

Yes it can, just use a Combo instead.


----------



## computerman29642 (Dec 4, 2007)

Please take a look at the attached database. Should the code under the CheckOut/In form be moved to the frmMovie form?


----------



## OBP (Mar 8, 2005)

I decided you don't need the Combo after all, sorry about that. 
I think this version works quite well with the Title and Availability combined in the Combo.


----------



## computerman29642 (Dec 4, 2007)

I have added 4 new forms (frmMNumberSearch, frmTitleSearch, frmSearch, and a Switchboard). I am unsure if I want to continue using the tabbed form, or start using the switchboard.

Please review the attached database, and let me know what you all think I should do.


----------



## OBP (Mar 8, 2005)

I haven't looked at it yet, but on principle I prefer the Switchboard for "Admin" stuff, ie background data and the Tabbed mainform for all things "Movie".


----------



## computerman29642 (Dec 4, 2007)

I was not really sure how to use the new search forms with the tabbed main form. Maybe the search forms were a wasted effort (not really needed).


----------



## OBP (Mar 8, 2005)

They are a good exercise, even you don't use them much.
See my latest posts on Mark's thread on how to create a "Multi-Search" form instead of 4 seperate forms.
http://forums.techguy.org/business-applications/715768-access-help-see-attached-db-10.html


----------



## computerman29642 (Dec 4, 2007)

It appears that you have subform on top of subform. Is that correct?


----------



## OBP (Mar 8, 2005)

Yep, neat isn't it?


----------



## computerman29642 (Dec 4, 2007)

Very neat. The problem is.....will I be able to reproduce it...LOL!


----------



## computerman29642 (Dec 4, 2007)

How can I take the frmCheckOut/In form add a combo box that displays the customers, and then CheckOut/In infrmation only for that customer.


----------



## Aj_old (Sep 24, 2007)

gamecockfan said:


> How can I take the frmCheckOut/In form add a combo box that displays the customers, and then CheckOut/In infrmation only for that customer.


Make a new form base on the tblCustomers, put there all 3 field of the table, arange them on newly created form, after that add subform frmCheckOut/In


----------



## OBP (Mar 8, 2005)

I am not sure that you can do it with your current Form as it is linked to the MovieID, I think you would have to employ the Multi search Concept and have another (hidden) form on the Tab that is filtered by the customer combo and only made visible when the Combo is clicked. You could develop the Query and Form and then add it to the Tab after you have it working.


----------



## computerman29642 (Dec 4, 2007)

Okay. I am going to start working on the Multi Search Form.


----------



## computerman29642 (Dec 4, 2007)

Should I have a switchboard or just have the tab form open?


----------



## OBP (Mar 8, 2005)

I would have both, but have the one used most as the one opened on top when the database opens.


----------



## computerman29642 (Dec 4, 2007)

When a new movie is added, how do I set the default availability to available?


----------



## OBP (Mar 8, 2005)

Me.Availability = "Available"
in the After Update event of the Movie title field


----------



## computerman29642 (Dec 4, 2007)

When you have a moment, could you take a look at the database, and let me know any suggestions you may have.

In order form the tabbed form to work properly, you will need to change the switchboard option to edit mode instead of add mode.


----------



## computerman29642 (Dec 4, 2007)

AcaCandy, I am not sure how you would like for the databse to work. You can take a look at the last attached db, and see if that does what you want.

I would like to know your suggestions and opinions as well. I believe it would be good to get feedback from a user.


----------



## ~Candy~ (Jan 27, 2001)

I get to go from a beggar to a BETA TESTER?


----------



## computerman29642 (Dec 4, 2007)

LOL! You will be helping me to help you in order to help me. 

I still have to pretty the database up, but I basically want to know what you think about the functionality.


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> In order form the tabbed form to work properly, you will need to change the switchboard option to edit mode instead of add mode.


Um,


----------



## computerman29642 (Dec 4, 2007)

I will the fix it, and then re-attach.


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> LOL! You will be helping me to help you in order to help me.
> 
> I still have to pretty the database up, but I basically want to know what you think about the functionality.


I had a customer this morning on my "work" site and I coulda used you  Couldn't find anyone to transfer him to for a mind boggling database question.


----------



## computerman29642 (Dec 4, 2007)

I have fixed the database. Please review the attached database.



> I had a customer this morning on my "work" site and I coulda used you Couldn't find anyone to transfer him to for a mind boggling database question.


I am probably not the one you would want to turn to for help with Access. I am trying to self teach myself. All that I know about Access has come from playing around, OBP, and Jim. OBP and Jim are amazing when it comes to Access.

I am building this database just for fun, to learn, and organize my movies.


----------



## OBP (Mar 8, 2005)

AcaCandy, wasn't I available this morning?


----------



## ~Candy~ (Jan 27, 2001)

OBP said:


> AcaCandy, wasn't I available this morning?


You weren't online when I was looking. We try to transfer to another person first, but, when no one is available, I sometimes suggest coming here.


----------



## ~Candy~ (Jan 27, 2001)

Ok, I must be an idiot. What's the first box supposed to do? I just get an error message  Movie Main. Can I system restore back to Version 5?


----------



## ~Candy~ (Jan 27, 2001)

Ok, nevermind, my virus program didn't like something  I had to ok it to run.


----------



## ~Candy~ (Jan 27, 2001)

Ok, off the left where it says add information, it's all scrunched down 

Is there going to be (or is there) an option to print a list?


----------



## computerman29642 (Dec 4, 2007)

> Ok, off the left where it says add information, it's all scrunched down


I can change the form design to allow for the form to maximized if you like.



> Is there going to be (or is there) an option to print a list?


What type of list?

I was thinking of creating a report that shows all the movies in the database. Do you think there should be some other report added?


----------



## ~Candy~ (Jan 27, 2001)

A list of the movies only. I like to have it handy when I go to the flea market, so I don't buy duplicates 

On the scrunch item, it's only that one thing, so I'm not sure about maximized?


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> A list of the movies only. I like to have it handy when I go to the flea market, so I don't buy duplicates
> 
> On the scrunch item, it's only that one thing, so I'm not sure about maximized?


I will create a report that displays all the movies in the database. 

When you say "scrunched item", do you mean the form?


----------



## ~Candy~ (Jan 27, 2001)

Thanks 

Just the words that say "Add Information" --- looks like it's 2/3 size like.....hard to describe


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database. I have made it so tha the AddInfo form can be maximized. I have also created a report to list all the movies in the database.

Let me know if you would liek to see anything else on the report.


----------



## ~Candy~ (Jan 27, 2001)

What I'm seeing if you go to the main menu, off to the left there it says add information....that is what is scrunched --- looks like it's trying to match the height of the box to the right where it says description.

And I don't see how to print? I'm sorry, I don't have much experience in even looking at these things


----------



## computerman29642 (Dec 4, 2007)

No need to apologize to me. I know how you feel. 

I have added a command button on the main tab form. You should be able to click the command button to open the report. I have also adjusted the "Add Information" textbox. Let me know how it looks now.

Please review the attached db.


----------



## ~Candy~ (Jan 27, 2001)

The last one says invalid attachment.....here's the screen shot, I think


----------



## computerman29642 (Dec 4, 2007)

You are unable to open the last attachment I posted?


----------



## ~Candy~ (Jan 27, 2001)

Ok, I got it to download......must be a server time delay.

Would it be too much to remove the Movie Number or at least not have that the default first entry? And go straight to the movie title first without having to tab? Or is the beggar now pushing it?  

List looks good (except for that nasty movie number  )


----------



## computerman29642 (Dec 4, 2007)

Let me know what you think now.


----------



## computerman29642 (Dec 4, 2007)

OBP, I am curious to find out what you think.


----------



## ~Candy~ (Jan 27, 2001)

On that new one, Category replaced the movie number 

And this is probably way way pushing it as I'm sure this is mostly to be used as an online database and not really heavy printing of it, but, is there anyway to do like a single spaced movie title only list?

You can tell me to go away anytime now 

Or maybe it's just as easy to copy from that list and paste it into either a word document or excel spreadsheet?

I have an excel spreadsheet now, using 4 colums and about 6.5 or 7 point type. Nothing nearly as cool as this database


----------



## computerman29642 (Dec 4, 2007)

No, this is not an online database. This is just for me to use on my personal computer at the house.

Do you want the category off the report as well?



> And this is probably way way pushing it as I'm sure this is mostly to be used as an online database and not really heavy printing of it, but, is there anyway to do like a single spaced movie title only list?


I am not sure what you are asking here.

I would never tell you to go away. Your questions, changes, opinions, and suggestions are helping me learn. 

I would like to get the db so that you do not need to take the extra step of moving it to excel or word. You let me know what you want, and I will try to accommodate.


----------



## ~Candy~ (Jan 27, 2001)

Thanks, you're too kind 


When I said online, I meant for use of the computer. Poor choice of wording on my part 

I'd just like to be able to print a listing of all of the movies from the database that wouldn't take up, say, 20 pages or so. I'm sure it can't be done the way I have it now, but, if it would go into just one column, single spaced, it would be easier to copy and paste into Excel. I have one sheet now, front and back. Back is only half full. 

Another thing and I didn't notice this, is there a choice to tell if the movie is on DVD or um, errr, VCR?


----------



## computerman29642 (Dec 4, 2007)

You would like the report to be only 1 page (front and back)? I could try to make the font smaller on the report. What size font are you printing now?

As of right now, there is not a way to select between VCR or DVD. If you like, I could add that option.


----------



## ~Candy~ (Jan 27, 2001)

Sorry, I had a diet coke explode in my freezer  Had to take care of that........

On the DVD/VCR issue, a checkbox for one or the other would be cool...or for that matter, the ability to choose both, as I have some in both formats. 

For the listing, I'd like to try to keep it as short as possible, be it one page front and back, or two pages, or even 3 or 4. Obviously, I can turn the paper over during the printing process. I think I'm using something like 6.5 or 7 point now, I'll have to pull it up and take a look. It's on the computer I crashed, I'm in the process of "overinstalling" Vista and then I should be able to take a peek at it. (Yeah, it's backed up somewhere, but, not where I am in the world today  )


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database.

I have taken the category off the report. I have added a Format field. Let me kow if how I did the format field is works for you.

Keep in mind I still plan to make the design look better. I am just trying to get the functionality correct right now.


----------



## computerman29642 (Dec 4, 2007)

AcaCandy, If you like....I could set the report up in columns. You probably could get 3 columns of movie titles on the front side, and three columns worth on the back side of one page.

Let me know what you think.


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> AcaCandy, If you like....I could set the report up in columns. You probably could get 3 columns of movie titles on the front side, and three columns worth on the back side of one page.
> 
> Let me know what you think.


WOW! That sounds almost too good  I'm behind in emails this a.m. I'll get caught up in the next hour or so. (yes, I get that many  )


----------



## computerman29642 (Dec 4, 2007)

Take a look at the last db I attached and let me know what you think.

As for the columns, I tried to set that up, but for some reason it is not working properly. I will keep working with it to see if I can get it to work.


----------



## ~Candy~ (Jan 27, 2001)

Don't wast a lot of time on it  This is your database, not mine, I'll make do with however it will work  The fact that I can use it is good enough for me


----------



## computerman29642 (Dec 4, 2007)

Well, you may ask for the db to be able to do something I have not thought about and would like to add.

I believe I have decided to keep the format option in my database, so that is one example of how you have given me some insight.


----------



## ~Candy~ (Jan 27, 2001)

Cool, glad to help by accident 

That last one looks good! :up:


----------



## computerman29642 (Dec 4, 2007)

Please, let me know if you see anything else that needs to be done. 

I will send you a final version once I have that completed.


----------



## ~Candy~ (Jan 27, 2001)

Thanks, I will  

I'm going to start adding some stuff to it and see what happens


----------



## computerman29642 (Dec 4, 2007)

OBP helped me figure out how to get the columns to work on the report. I will have the report working properly inthe final release. Let me know if you would like to have a copy of the database withthe report working properly before the final release.


----------



## ~Candy~ (Jan 27, 2001)

Sure, I have them stacking up on my desktop now


----------



## computerman29642 (Dec 4, 2007)

LOL....I know.....I know....That is why i was checking to see if you wanted to wait or not. 

If you really want me to send it now, I will.


----------



## computerman29642 (Dec 4, 2007)

I am going to mark this thread as solved. We have covered many topics in this one thread. I will post a new thread for any further questions.

I really appreciate all those who have helped me in this thread.


----------



## jimr381 (Jul 20, 2007)

ACA if you want help with certain changes, then feel free to let us know in another post.


----------



## ~Candy~ (Jan 27, 2001)

:up: Thanks! Will do.


----------



## EAFiedler (Apr 25, 2000)

What!? After reading through this entire thread. The final release was not attached? 

What a let down. <kicks the dirt>


----------



## OBP (Mar 8, 2005)

The Saga continues here
http://forums.techguy.org/business-applications/730707-hide-field-subform.html


----------



## EAFiedler (Apr 25, 2000)

Thanks


----------



## jimr381 (Jul 20, 2007)

4 Pages is that a record for the 2 of you?


----------



## EAFiedler (Apr 25, 2000)

jimr381 said:


> 4 Pages is that a record for the 2 of you?


Reading it or creating it?


----------



## ~Candy~ (Jan 27, 2001)

You guys are playing with my final product? 




*off to go read the new adventure  *


----------



## computerman29642 (Dec 4, 2007)

Anyone who like a copy of the db, just let me know.


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> You guys are playing with my final product?
> 
> *off to go read the new adventure  *


AcaCandy, Your final database was built for your needs. How is the database working out for you? If there is anything you would like to have changed or added, just let us know.


----------



## jimr381 (Jul 20, 2007)

Go ahead and post the end result for all to see if you would please.


----------



## computerman29642 (Dec 4, 2007)

As of right now, I am still working out a kink on the main form. Once this issue has been resolved, I will post up the completed database.


----------



## EAFiedler (Apr 25, 2000)

Yay!


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> AcaCandy, Your final database was built for your needs. How is the database working out for you? If there is anything you would like to have changed or added, just let us know.


I'm out of town currently, I've played with it a bit, but, not enough to have any complaints yet  

Have I mentioned that you guys are awesome?


----------

