# Solved: Hide a Field on a Subform



## computerman29642 (Dec 4, 2007)

I have a password form in my database. I am trying to set a fields visible property to tru when the correct password is entered. Here is the code I am using.


```
[Forms]![frmMovie]![sfrmMovie]![txtMNumber].Visible = True
```
When the correct password is entered, the field does not show up on the subform.


----------



## OBP (Mar 8, 2005)

Try 
me.sfrmMovie.txtMNumber.Visible = True


----------



## computerman29642 (Dec 4, 2007)

I tried the code provided, but the txtMNumber field still does not display on the subform.


----------



## OBP (Mar 8, 2005)

Can you post it?


----------



## computerman29642 (Dec 4, 2007)

I have posted the database as you requested.

Here is what I am trying to accomplish. I placed the txtNumber field on the sfrmMovie subform. The txtNumber field consist of the iif statement we worked on yesterday. However, I found that when the main tab form went into edit mode, the txtNumber field could not be editted. So, I placed the txtMNumber field on the sfrmMovie subform with the source to the m_Number field in the tblMovie table.

I would like for the txtNumber field to not show when the form is in edit mode, and the txtMNumber field to show. When the form is not in edit mode the field txtNumber will show, and the txtMNumber will not show.


----------



## computerman29642 (Dec 4, 2007)

Now that you have the most current database, I would like to have your opinion as how I should do the children movies. I was thinking that I really did not want the children movies mixed with the adult movies.

So, I was thinking about creating a second tabbed form called 'ChildrenMenu' (or something like that), and then have a switchboard button that will open that form.

What do you think?


----------



## jimr381 (Jul 20, 2007)

You could have an initial switchboard that allows you to select children's or All movies. The Children's button would link to a form based upon a query that would show you only the ones that you designated as child friendly. You could initially just have it look at all of the G rated movies if you do not want to specify for each one if they are an adult or children's movie.


----------



## computerman29642 (Dec 4, 2007)

Could I not just add two radio buttons to the tab form I have in place now? 

One would be Adult and the other Children. If the adult is selected, then only the adult movies would be displayed in the cboTitle combo. If the children is selected, then only the children movies will be displayed in the combo box.


----------



## jimr381 (Jul 20, 2007)

I guess the radio button could be tied to a filter via code that would do where condition to filter the records.


----------



## OBP (Mar 8, 2005)

Why have a seperate Form for Child Movies?
Why not have an Option Group with Adult & Children as Options.
All you need then is an indicator field in the movies table to show whether it is a Kid's Movie or an Adult movie.


----------



## computerman29642 (Dec 4, 2007)

So, what was I doing wrong on the visible property?



> Why have a seperate Form for Child Movies?
> Why not have an Option Group with Adult & Children as Options.
> All you need then is an indicator field in the movies table to show whether it is a Kid's Movie or an Adult movie.


I was thinking that the list in the title combo was getting pretty long. I have not even added the children movies yet. That is why I thought separating the two would make it easier to find the movies.

I have not added the VHS movies yet either. I was thinking of separating them as well. I trust you all to lead me in the right direction. That is why I really want your opinions.

You have the database, what do you think when you use the database?


----------



## jimr381 (Jul 20, 2007)

You will need a format field to address DVD, Blue-Ray or VHS and another field for Child/Adult.


----------



## computerman29642 (Dec 4, 2007)

I have a format table that is linked to the movie table. (DVD, VHS, etc).


----------



## OBP (Mar 8, 2005)

If you want to shorten your list why not have the user select a Category first and just list that Category's Films in the Combo.

You were trying to use the 
[Forms]![frmMovie]![sfrmMovie]![txtMNumber].Visible = True
in the Movies mainform button where it should be 
me.sfrmMovie.txtMNumber.Visible = True
and the other way around on the Password form.


----------



## computerman29642 (Dec 4, 2007)

> If you want to shorten your list why not have the user select a Category first and just list that Category's Films in the Combo.


That would work. You always try to take the simple path...don't you. LOL!  



> You were trying to use the
> [Forms]![frmMovie]![sfrmMovie]![txtMNumber].Visible = True
> in the Movies mainform button where it should be
> me.sfrmMovie.txtMNumber.Visible = True
> and the other way around on the Password form.


I should get an 'A' for effort...LOL!


----------



## OBP (Mar 8, 2005)

You really were so close, it is hard to remember all the different variations of referring to controls when they are on the same form or subform or a different form.


----------



## computerman29642 (Dec 4, 2007)

> You really were so close, it is hard to remember all the different variations of referring to controls when they are on the same form or subform or a different form.


Thanks.

Thank you for the help.


----------



## computerman29642 (Dec 4, 2007)

> If you want to shorten your list why not have the user select a Category first and just list that Category's Films in the Combo.


Would this be by option buttons, or by another combo box?


----------



## OBP (Mar 8, 2005)

I would choose a Combo box, therefore you have a Combo list based on another combo selection, does that sound familiar?

I could have sworn someone said this database was finished or nearly finished


----------



## computerman29642 (Dec 4, 2007)

LOL....I believe you have helped me enough to know that I am always playing and trying to improve my databases. Besides, the more I do/try, the more I learn.

Yes, it does sound familiar. I believe you are going back to the multi search option.

All in All, what do you think about the database?


----------



## OBP (Mar 8, 2005)

It is looking good, but I am not sure about the Password for editing, if I thought it needed controlling I would probably "Secure" the database and have the users log in.
I don't think you need the "Add Data" Menu, those buttons could be on the Switchboard, I think it is an unecessary mouse click.  Plus you have all those Add Buttons on the Main Movie form as well.
I have played around with your report in this one.


----------



## computerman29642 (Dec 4, 2007)

> It is looking good, but I am not sure about the Password for editing, if I thought it needed controlling I would probably "Secure" the database and have the users log in.


The password is just to keep users accidently changing something.



> I don't think you need the "Add Data" Menu, those buttons could be on the Switchboard, I think it is an unecessary mouse click. Plus you have all those Add Buttons on the Main Movie form as well.


Where exactly are you talking about?



> I have played around with your report in this one.


Which report?


----------



## OBP (Mar 8, 2005)

On the switchboard, Add Information button opens another form with buttons on.
The Report on the Switchboard Button.


----------



## computerman29642 (Dec 4, 2007)

> On the switchboard, Add Information button opens another form with buttons on.


I guess you are right. Having that is redundant and causes more clicking. I will remove that button.



> The Report on the Switchboard Button.


Are you saying get rid of the report button on the switchboard? I am trying to get the categories to sort in order, but I cannot seem to get them to do that.


----------



## OBP (Mar 8, 2005)

No not get rid of the report buton, but look at the report.


----------



## computerman29642 (Dec 4, 2007)

What is wrong with the report? I have the report sorted by category, then by movie title.


----------



## computerman29642 (Dec 4, 2007)

I was thinking....I know that could be dangerous, LOL!

Do you think it would be a good idea to build a form that list all of the categories? The user could then click one of teh category buttons, have the tabbed form open, and cboTitle combo be filtered to the movies for the category selected.

I looked at the multi search form you sent me, but I am not sure how I would do that with this database.


----------



## OBP (Mar 8, 2005)

Does it have Columns?
Got to go now, speak tomorrow.


----------



## computerman29642 (Dec 4, 2007)

> Does it have Columns?


No columns. Do you think that it should?


----------



## computerman29642 (Dec 4, 2007)

The Available/UnAvailable field on the main tab form is not working correctly. When I enter a check-out date, the filed is not updating until I close the form and re-open.


----------



## computerman29642 (Dec 4, 2007)

I add the following code to the sfrmCheckOut/In subform (After_Update event)...


```
Me.Parent.txtAvailable.Requery
```
Do you see any problems with this?


----------



## computerman29642 (Dec 4, 2007)

Let me know what you think about the report now. I have added columns, as you suggested.


----------



## OBP (Mar 8, 2005)

Chris, the Report in the Database had the Columns created for you, as well as the Grouping and sorting by category. 
I haven't been able to do any Forum and Database work today due to other commitments.


----------



## computerman29642 (Dec 4, 2007)

> Chris, the Report in the Database had the Columns created for you, as well as the Grouping and sorting by category.


What so you mean by 'The Columns created for you'?



> I haven't been able to do any Forum and Database work today due to other commitments.


No worries. I can understand other things coming up, or having prior commitments. I have been playing around with it today, and I believe I cannot do anything else to it except figure out the select a category, and then a movie title on the tab form.


----------



## OBP (Mar 8, 2005)

The report in the database that I posted should have 2 columns of data.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> The report in the database that I posted should have 2 columns of data.


What/Which database?


----------



## OBP (Mar 8, 2005)

Interesting, I posted a copy of the Database and it hasn't shown up 
Anyway it was this one.


----------



## computerman29642 (Dec 4, 2007)

Yours is like mine except that you created a new page for each category. I believe I like that better. Thanks.

So, what did you think about my idea to deal with category filter of teh titles?


----------



## OBP (Mar 8, 2005)

I haven't had time to give it any thought yet, I will get back to you tomorrow.


----------



## computerman29642 (Dec 4, 2007)

That's fine.

I am looking at your report, how did you get the categories to separate on separate pages?


----------



## OBP (Mar 8, 2005)

Notice they are in the Group header, now check the properties of the Group Footer, Force page.


----------



## computerman29642 (Dec 4, 2007)

I looked everywhere but the Group Footer. Thanks.


----------



## computerman29642 (Dec 4, 2007)

Can the two columns be lined up?


----------



## jimr381 (Jul 20, 2007)

Have you looked at selecting both then going to "Format">>"Align">>"Left"? They took away my download rights again and as such I cannot look at your files at the moment.


----------



## computerman29642 (Dec 4, 2007)

The report is setup to display two columns. When the data starts to go into the second column, that column is higher than the first column. I would like for them to be aligned.


----------



## OBP (Mar 8, 2005)

I don't think that you can, the second column is automatically utilizing the Group Header space and I don't see any way to stop it.


----------



## jimr381 (Jul 20, 2007)

Can you post your latest rendition of your database? OBP is right though, info in your group header will not match up with info in the detail area height wise.


----------



## computerman29642 (Dec 4, 2007)

I have attached an up-to-date copy of the database.


----------



## computerman29642 (Dec 4, 2007)

I manipulated the report, and the columns now line up with each other. :up: It may not be the best way/practice of doing the report, but it gets the job done (kind of like the whole duck tape effect). 

Please review the attached database.


----------



## OBP (Mar 8, 2005)

Very sneaky, I love it, well done :up:


----------



## computerman29642 (Dec 4, 2007)

Thank you.  I must admit, I learned from one of the best.  

Now, all I have to do is figure out the multi search for the main form.


----------



## OBP (Mar 8, 2005)

Now that is easy, just create an Unbound form and put on it a field for each thing you want to search for. Create a Query of your data and use the old
Forms![formname]![fieldname] in the various Criteria rows to find them.
You might be better off using
Like "*" & Forms![formname]![fieldname] & "*"
for the Film Title and Star names though.
For things like category use a combo on the form.


----------



## computerman29642 (Dec 4, 2007)

The main form has a title combo all ready. On the unbound form, would I need a title search as well?

I was thinking maybe just a star and category search. Should the star search be a combo as well?

What exactly does this line do? Like "*" & Forms![formname]![fieldname] & "*"

How exactly will the unbound form work with the main form? Will the unbound form be used to open the main form, or will the main form be a subform on the unbound form?


----------



## OBP (Mar 8, 2005)

The Like "*" & Forms![formname]![fieldname] & "*" is Wild card Pattern matching,
if you put in Jim, it will find all Stars called Jim ... or ...Jim ...
The first asterix means anything before your input and the second one means anything after it.
It is very powerful for quick searches.
How you present the data is up to you, but it would probably take a "Copy" of your Main form unless you want to delve in to Dynamic SQL Record sources.


----------



## jimr381 (Jul 20, 2007)

gamecockfan said:


> The main form has a title combo all ready. On the unbound form, would I need a title search as well?
> 
> I was thinking maybe just a star and category search. Should the star search be a combo as well?
> 
> ...


Basically what it is, is a selection area that allows you to pass whatever they select into the query. I would want to place that in the criteria field that the field is searching on.

For clarification purposes the Like does a wildcard search. * means I do not care so it does not care what is the begining or the the and will search for whatever they select from list or type out manually.


----------



## computerman29642 (Dec 4, 2007)

Tony and Jim, you can both tell me to "STOP, Do it this way", but hear is what I was thinking.....

I would create form with a command button or option button for each category, as well as, an "All" command button or option. This form will be my new main form for when the database is opened. The user would be able to select a category, and then click a "Search Button". When the search button is pressed, the tabbed form will open, but the only titles displayed in the title combo would be the ones that belong to the category choosen.

Once again, this maybe my mind working over-time.


----------



## jimr381 (Jul 20, 2007)

I would create multiple buttons on the switchboard. One for All Movies, one for Kid's Movies and one called Search Form that will search based upon a form that you would setup using the steps we specified before.


----------



## computerman29642 (Dec 4, 2007)

Would you make a copy of the tabbed form in order to create the All and Kids forms?


----------



## OBP (Mar 8, 2005)

I am a little confused now, are you talking Movie Categories, or Ad hoc searching?
If it is Movie Categories we differ a bit, because I would definitely not have the categories selection on the Switchboard, the reason being is that when a user has finished looking at the "current" selection they have to go back to the Switchboard to make another one, which is not efficient.
I would have the Movie selection on the Main Form either as a Combo or as an Option Group Frame.
For Ad Hoc searching you could make it your new switchboard, or a new Main form to put your main form on, in which case all records would go through the one query thus avoiding the need for a new form, there again I would use an Option Group Frame to indiacte whether you wanted search or display all records.


----------



## computerman29642 (Dec 4, 2007)

I am a little confused now. 



> If it is Movie Categories we differ a bit, because I would definitely not have the categories selection on the Switchboard, the reason being is that when a user has finished looking at the "current" selection they have to go back to the Switchboard to make another one, which is not efficient.


I get what you are saying here. We do not want to make more work fo rthe user. The database needs to be as user friendly as possible.



> I would have the Movie selection on the Main Form either as a Combo or as an Option Group Frame.
> For Ad Hoc searching you could make it your new switchboard, or a new Main form to put your main form on, in which case all records would go through the one query thus avoiding the need for a new form, there again I would use an Option Group Frame to indiacte whether you wanted search or display all records.


Here is where you lost me. I am not sure what the design is going to be here. Do I need a new form? Would I just build off the tabbed form (Main form) that I have now?


----------



## jimr381 (Jul 20, 2007)

You could just add the toggle button for the "Kids" and "All Movies" on a sidebar (section on the side with a box drawn around it and the "Search" button in there as well which will bring up the search form. I actually use both a sidebar and a switchboard in my databases. Why you might ask, because some users will definitely miss the toggle button to switch between Kids and All Movies, but if it is in their face on a switchboard at start up, then they will not.

I also typically put links to the reports that user's need in a sub-switchboard, as well as links to other forms in sub-switchboards, but this is because I am do not want them futzing around in the database window.

Different people look at things differently. From doing web design before I try to focus on not overwhelming the user with too much crap on one screen, hence me using tabbed forms and also a switchboard to consolidate navigation through one place. This is a little different animal though since it is small and not going to be used by many.


----------



## computerman29642 (Dec 4, 2007)

Will what you are decribing work with th tab form (main form) I have in place, or will a new form need to be created. If you look at the latest version of my database, you will notice that there is a frmSearch form. It is not being used, however.


----------



## jimr381 (Jul 20, 2007)

I still am unable to access zip files, let me yell at my network guys again and get back with you later on.


----------



## computerman29642 (Dec 4, 2007)

Ok.


----------



## jimr381 (Jul 20, 2007)

I have an awards presentation for something I am receiving and then a promotion lunch, so I should be able to respond to you around 2PM today. Just letting you know so that you are not waiting in the wings. I am sure OBP will come along beforehand though.


----------



## computerman29642 (Dec 4, 2007)

Thanks. Congratulations.


----------



## OBP (Mar 8, 2005)

This is the kind of "Oprion Group" that I was talking about.
I am currently working on this database for Stuart so it has been sanitized of records, but you can see the option group top right on the form and how how it "Filters" the form's data in the Enquiries Query.


----------



## computerman29642 (Dec 4, 2007)

Tony, I apologize, but I am still a little confused.

Your suggestion is to have an Option group with two options: "Kids" and "All"? When the option of "Kids" is selected, then the title combo will be filter to only display the kid movies....correct?


----------



## OBP (Mar 8, 2005)

I was not just thinking of the Kids and All, but also Categories, unless you are going to use a Combo? It can also be used on any search forms to decide how and what to search for.


----------



## computerman29642 (Dec 4, 2007)

I am trying to find the best way of doing this. The most user friendly.

I have a frmSearch form created, but it is not being used. If you still have a copy of my database, would you take a look and see if that would work at all?


----------



## computerman29642 (Dec 4, 2007)

Maybe we should start back at Poitn A. What is the objective?


----------



## jimr381 (Jul 20, 2007)

I just sat down at my desk again. I more than likely will not be able to look at your database until Monday at the very earliest.


----------



## computerman29642 (Dec 4, 2007)

That's fine. I have just now sat back down in front of the database myself.


----------



## OBP (Mar 8, 2005)

Have a look at this one, I have added an Option group to the Main Movie Form filter the Categories and a Simple Search Function for those "I can't remember the name of the Film, but I think it had star in the title" or "What films have you got with Bruce Willis in?" type questions.
You could have an "Adult/Kids" one as well.


----------



## computerman29642 (Dec 4, 2007)

I really like the way the option buttons work. Is there a way to make "All" option button default?

The SearchResult form does not appear to work.


----------



## computerman29642 (Dec 4, 2007)

I am sitting here looking how you did the queries, and the forms. I would not have figured any of that out.


----------



## OBP (Mar 8, 2005)

The Search Form works on mine. 

It is difficult to make a default option because the Subform is Displayed before the Default takes effect, so you get a blank Subform.


----------



## computerman29642 (Dec 4, 2007)

> The Search Form works on mine.


So, if you type "Bruce Willis" in the search box, and select option Star you are able to see the movies listed that Bruce Willis plays in?

When I type in a name of a star, and click the search button, the next form comes up blank.



> It is difficult to make a default option because the Subform is Displayed before the Default takes effect, so you get a blank Subform.


That's fine. I am trying to figure out how you got all of this to work by doing it myself with the copy of the database I have. I have the query setup like yours, and I have the frame built like yours.

I have placed this code...


```
Private Sub Frame_AfterUpdate()
    Me.Filter = ""
    Me.Requery
    Me.cboTitle.Requery
End Sub
```
under the frmMovie form.

However, when I select one of the option buttons, the cboTitle combo will requery, but all the movie titles are still listed. What am I missing?


----------



## OBP (Mar 8, 2005)

Have you changed the Subform Record Source to the same Query?
I put Baker in as a star and 2 come up


----------



## OBP (Mar 8, 2005)

I tried Along for a film and 10 records come up with 2 films and 10 Stars listed.
If you just wanted the Films listed with no Stars you could use a new form.


----------



## computerman29642 (Dec 4, 2007)

> Have you changed the Subform Record Source to the same Query?


No, let me give that a try.



> I put Baker in as a star and 2 come up


So the "Star" option goes by the last name? I was typing in the whole name, and a star that was not in the table.

Is there a way to have the movie title display once on the Serach Reult form?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> I tried Along for a film and 10 records come up with 2 films and 10 Stars listed.
> If you just wanted the Films listed with no Stars you could use a new form.


I am going to play around with it for a while, and see how I would like for it to work.

Thank you so much for the help.


----------



## computerman29642 (Dec 4, 2007)

> Have you changed the Subform Record Source to the same Query?


I have changed the subform touse the aryMovie query. However, the title combo is still displaying all the mvovies when one of the option buttons are selected.

The funny thing is when I test the movie query, it works properly.


----------



## OBP (Mar 8, 2005)

To only display the Films you would need to copy the Query and Form and then remove the stars from the query and form. In the Search Button's VBA you would open up the original search results for Stars and the new one for Films.


----------



## computerman29642 (Dec 4, 2007)

Is a movie title search really necessary? I think the only search needed is for a star...do you agree?


----------



## OBP (Mar 8, 2005)

The movie search is when the user can't remember the proper name and say something like " I am sure it had Force in the title" you then find any films with the word "Force" in to list them.


----------



## computerman29642 (Dec 4, 2007)

OK....I get what you are saying. 

I am still unable to get the movie option buttons to work properly. It appears to me that everything is setup like yours, but the title combo still displays all the mvoies.


----------



## OBP (Mar 8, 2005)

Is the Combo also based on the Search Query?

Will you also look at a Kids/Adults Option?


----------



## computerman29642 (Dec 4, 2007)

> Is the Combo also based on the Search Query?


Yeah...I just realized that the combo was not based off the query. The combo is working properly now. Thanks.



> Will you also look at a Kids/Adults Option?


All of the kid movies are in the category 'Family'. So, if the user selects the Family option then he or she will be able to see only the kid movies.

So, do you think the Kids/Adult option is necessary?


----------



## OBP (Mar 8, 2005)

If the Kids option was selected it could Disable all of the other options in the Category Option Group.


----------



## OBP (Mar 8, 2005)

But it is your database, I am just offering suggestions


----------



## computerman29642 (Dec 4, 2007)

Where exactly would the Adult/Kid option be located? I am trying to picture in my head how it would work.

Please, keep giving suggestions.


----------



## OBP (Mar 8, 2005)

How about like this.
Of course you could password control it so that you need the password to display "All Movoes".
Or if the database was secured you would use the Log in ID to make that decision and not need the Option Group.


----------



## computerman29642 (Dec 4, 2007)

I will see if I can get that to work.

Right now, I am trying to get the search to work on my form. I have the query working, but it does not work properly on the movie form.


----------



## OBP (Mar 8, 2005)

Be Warned, it took me a while 
You will need to compare everything on mine and yours to see any minor difference that could be critical.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Be Warned, it took me a while
> You will need to compare everything on mine and yours to see any minor difference that could be critical.


I have the serahc form working properlt I believe. The only thing I would like to fix is when I search for a title ("300"), the title is duplicated. How can I get the title to just show once, and not show a record for each star that plays in the movie.


----------



## computerman29642 (Dec 4, 2007)

Maybe this is how this code is soppuse to work


```
Like "*" & [Forms]![frmMovie]![Search] & "*"
```
but when I look up a star by the last name of "Law" it is finding other movie titles that has a star with the last name starting with a 'L".

Is this how it suppose to work?


----------



## OBP (Mar 8, 2005)

The Query will always list more than one Film while the Stars table is in the Query, that is why I said before that you would need to Split them and have 2 queries/Forms and the Search Button would open up the appropriate form.


----------



## computerman29642 (Dec 4, 2007)

In order for the search button to open the correct form, I would have to change the code to include an if statment...correct?


----------



## computerman29642 (Dec 4, 2007)

An IF statement something like this...


```
If Me.Frame2.Value = 1 Then
        Me.Filter = ""
        Me.Frame = 8
        DoCmd.OpenForm "frmSearchResultsTitle"
    Else
        Me.Filter = ""
        Me.Frame = 8
        DoCmd.OpenForm "frmSearchResultsStar"
    End If
```


----------



## OBP (Mar 8, 2005)

:up:


----------



## computerman29642 (Dec 4, 2007)

I believe everything is working properly now. Please review the attached database, and let me know if you see anything that I may have missed.

One thing I would like for you to test. Select the Star option, type in "Law", and click search. There should only be one movie to display, but there are more than one that displays.


----------



## computerman29642 (Dec 4, 2007)

OK...I just realized why when I look up "Law" (for Jude Law), the results show more than just the movie "Alfie".

The code

```
Like "*" & [Forms]![frmMovie]![Search] & "*"
```
 is designed to show everything that contains the word "Law" before and after...correct?

If so, it is finding "law" in Martin Lawerence, so it is displaying the movies Martin Lawerence plays in as well.


----------



## OBP (Mar 8, 2005)

No you have 4 Law Star records 1 Jude Law and 3 Martin Lawrence in your Moviestars table. 
You can't see it because your Stars Form doesn't display Stars.


----------



## computerman29642 (Dec 4, 2007)

> You can't see it because your Stars Form doesn't display Stars.


Yeah...I thinkI am going to add the stars back to the stars form.

As far as everything else, did it look ok?


----------



## OBP (Mar 8, 2005)

Looks good, but I haven't looked in depth, I am cooking dinner.


----------



## computerman29642 (Dec 4, 2007)

That's fine. Hope you enjoy your dinner.


----------



## OBP (Mar 8, 2005)

I think it is working rather well and does quite a bit for the user.

I am just sitting down to Minted Leg of Lamb, 4 Veg, mashed and buttered potatoes and Yorkshire Pudding with alshings of gravy. Is your mouth watering, or are you a Vegitarian?


----------



## computerman29642 (Dec 4, 2007)

OK...I found something else that is acting up. When you have a moment, give this a test and let me know what happens.

Perform a search for either a title or star. Click the detail button on one of teh results found. Now, click the "Action" option button. The title combo should get work correctly. Now, click the "Comedy" option button. The title combo does not displaythe first title in the list for comdies.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> I think it is working rather well and does quite a bit for the user.
> 
> I am just sitting down to Minted Leg of Lamb, 4 Veg, mashed and buttered potatoes and Yorkshire Pudding with alshings of gravy. Is your mouth watering, or are you a Vegitarian?


I agree, I believe the database is working very well, and for the most part user friendly.

Yes, my mouth is watering big time. I have another 5 hours before I get to go home and have a nice dinner. I hope you enjoy.


----------



## computerman29642 (Dec 4, 2007)

For some reason after the Star command button is pressed on the main form, the database locks up. The only thing that works after that point is the Star command button. I am able to press it as many times as I like.

If I try to click any other command button on the form, I get the following error message:

"Run-time error '2455': You eneter an expression that has an invalid reference to the property Form/report"

When I click the debug button, here is the code that is highlighted:


```
[Forms]![frmMovie]![sfrmMovie]![sfrmMovieStar]!
[j_StarringID].Requery
```


----------



## OBP (Mar 8, 2005)

It works fine on the last version that you posted as I was cooking diner, try downloading and testing it yourself.


----------



## computerman29642 (Dec 4, 2007)

I did download and test the database. Whenever I click the Star button on the main form to open the AddStar form, that works fine. The problem happens when I click the close button on the AddStar form. Once I have clicked the close button on the AddStar form, and try clicking one of the other command buttons, I get the "Run-time error '2455': You enter an expression that has an invalid reference to the property Form/report" error.

Note: I do not actually add a star when I open the AddStar form. When I click the Star command button, the AddStar form opens, and I just click the close button.


----------



## computerman29642 (Dec 4, 2007)

Would I need to use a

```
On Error Resume Next
```
 statement?


----------



## OBP (Mar 8, 2005)

I think that you will find that it only happens if you do not have Records displayed on the main Form, ie the Option Group has not been actioned. It is the Star Buttons "On Exit" code that appears to be the problem.
If it the same for you then add this in front of those 2 lines of code

If IsNull(Me.Frame) Then Exit Sub


----------



## computerman29642 (Dec 4, 2007)

Yes, it is the same for me. I will test that now.

I did not even think about the title combo not displaying any records. Just out of curiosity, why do you think it does not do that for any of the other "On Exit" codes?


----------



## computerman29642 (Dec 4, 2007)

That seems to have fixed the problem. Thanks Tony.

I believe one the following issue is resolved, the database functionality will be completed. YEAH! 

Here is the only problem I have found through testing...

Perform a search for either a title or star. Click the detail button on one of the results found. Now, click the "Action" option button. The title combo should work correctly. Now, click the "Comedy" option button. The title combo does not display the first title in the list for comdies.


----------



## OBP (Mar 8, 2005)

I agree, but it is a very minor thing and the first title is actually there. You could probably force it to display it when Comedy is selected, but is it worth it?

What about the Kids/Adult split?


----------



## computerman29642 (Dec 4, 2007)

I agree that it is a minor thing, so that maybe something I worry with later. I guess my question would be, why does it work for the 'Action' option?

I have not played with the Adult/Kid spilt, but I will do so now.


----------



## computerman29642 (Dec 4, 2007)

> What about the Kids/Adult split?


With the Kids/Adult, how did you see this working? I was thinking that I would make some objects on the form not visible until a selection is made.


----------



## OBP (Mar 8, 2005)

You could default to Kids and hide all of the Frame choices except family.


----------



## computerman29642 (Dec 4, 2007)

That is a very good idea. I will see if I can get that done.


----------



## computerman29642 (Dec 4, 2007)

Here is something I just thought about.

If the Kids option is selected, how will the search work?


----------



## jimr381 (Jul 20, 2007)

Work is still messing me over on attachments, so I am not able to download it.


----------



## OBP (Mar 8, 2005)

Well you will just have to modify the Search Queries to only show "Family" films if Kids is selected.


----------



## computerman29642 (Dec 4, 2007)

Jim, I hope they get you fixed here soon. I have lost one of my life lines....LOL! 



> Well you will just have to modify the Search Queries to only show "Family" films if Kids is selected.


I will try to get that to work.


----------



## jimr381 (Jul 20, 2007)

Maybe Sugan can help you. He seems like the helpful. If I ever need EQ2 gold I will not come see him, since will never buy gold.


----------



## computerman29642 (Dec 4, 2007)

Is there a way to add a "New Release" type option button? When this option button is selected, it would display only the movies that have been added to the database in the last 2 months.


----------



## computerman29642 (Dec 4, 2007)

The search on the main form is not working properly. It works fine until you select an option button. If you select an option button, and then go to search for a title ot star it causes problems.

If I perform a search with '3' in the title, I will get a list of movies. When I click the details button, the movie title displays in the combo box, and all other information displays in the subform. However, if I click an option button, and then perform a search the title nolonger displays in the title combo.


----------



## OBP (Mar 8, 2005)

What you have to remember is that the Search Results form actually sets the Movie Form's Filter when it displays the Single Record selected, this could be affecting the way the other "Options" will work.
Of course you can have a "New Release" option, the Movie Query would only find Release dates Between Date() and Date() - 91.


----------



## computerman29642 (Dec 4, 2007)

The Search Result form does not work properly if an Option is selected first, and then a search is performed.

I am going to try and implement the new release option now.


----------



## OBP (Mar 8, 2005)

Add this to the Search Results Form's VBA after it opens the Movie Form

Forms![frmMovie]![cboTitle].Requery


----------



## computerman29642 (Dec 4, 2007)

Can The same thing be done to the frame or options, so after a search is performed and an option button is selected the title will display in the title combo?


----------



## OBP (Mar 8, 2005)

There is something decidedly strange about the way the Movies Form reacts after the search has been run, it does not display the records for all of the Option Group buttons, even though they are there, although it works fine for "All" and "Action", it is possible that it is because they both use the First record of the overall recordset as their first record.
But I am still not sure why the "Requery" doesn't reset after the search the way it does if the search has not been executed.


----------



## ~Candy~ (Jan 27, 2001)

I'm observing


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> There is something decidedly strange about the way the Movies Form reacts after the search has been run, it does not display the records for all of the Option Group buttons, even though they are there, although it works fine for "All" and "Action", it is possible that it is because they both use the First record of the overall recordset as their first record.
> But I am still not sure why the "Requery" doesn't reset after the search the way it does if the search has not been executed.


I have played around with the code to get the requery to work with the other options. Nothing seems to work.


----------



## jimr381 (Jul 20, 2007)

Requery should reset the form, but have you thought about reopening the form all-together via code before you preform the search?


----------



## computerman29642 (Dec 4, 2007)

No, I have not thought about doing anything like that. That may be a possibility.


----------



## OBP (Mar 8, 2005)

Jim, it would have to be re-opened After the Search as that is where the trouble occurs, the Search Form opens a Filtered version of the Form and that is what is causing the problems. The "Requery" action is not resetting the m_MovieID and movieId fields.
I am currrently looking at opening the Movie Form and then applying the Filter, but without success at the moment.


----------



## computerman29642 (Dec 4, 2007)

I have been playing around with the code as well, but have not had any success.


----------



## computerman29642 (Dec 4, 2007)

I am still in the process of testing, but after some reserach I believe I have found the solution to the filter issue.

I have changed the following ocde behind the Movie form...


```
Private Sub Frame_AfterUpdate()
    Me.Filter = ""
    Me.Requery
    Me.cboTitle.Requery
    
    Me.Form.FilterOn = False
    Me.Form.RecordSource = Me.Form.RecordSource
End Sub
```
If anyone can foresee a problem, please let me know.


----------



## computerman29642 (Dec 4, 2007)

What would be the best way to keep track of movies that have not been returned? Would a report be the best route to take, or a search type form?


----------



## OBP (Mar 8, 2005)

It shouldn't matter whether you use a Report or a Form, if you want any "Interaction" with the records that are output, like the Command button on the Search Results Form then a form is probably easier.

That last line of your new Frame code is interesting. I will take a look at that.


----------



## computerman29642 (Dec 4, 2007)

> It shouldn't matter whether you use a Report or a Form, if you want any "Interaction" with the records that are output, like the Command button on the Search Results Form then a form is probably easier.


I think I am going to do a search type form with the customer checkout. So, before they are able to take any othe rmovies, I can be sure that they have brought back all others.



> That last line of your new Frame code is interesting. I will take a look at that.


I actually did a google search on form filters, and found that someone used the two lines of code I added to the frame code. I have been testing the code since yesterday, and it appears to be working fine. Let me know what you find when you test the code.


----------



## OBP (Mar 8, 2005)

Yes that single line of code seems to do the trick as I was already using the other line.
have a variation of your Movies Form that I was working on to try and overcome the problem and I found that it was definitely the Form Filter that was acusing the problem.
I will post the new version that actually uses a Form's Filter to find the Film Titles, but not the Stars, but it does save a Query and Form though.


----------



## computerman29642 (Dec 4, 2007)

> I will post the new version that actually uses a Form's Filter to find the Film Titles, but not the Stars, but it does save a Query and Form though.


What do you mean?


----------



## OBP (Mar 8, 2005)

Have a look at this "alternative" Title Search.
Well done on the Google Search.


----------



## computerman29642 (Dec 4, 2007)

Thanks. 

I see, you added a next button to cycle through the search querires, and got rid of the form that pops-up. I will play around with it to see which I prefer.

I am currently working on the customer search form. By the design of the main form, how would you do the customer search? I was thinking about copying the search frame on the main form, copying it on top of the current search frame, and having it display when the CheckOut/In tab is selected.


----------



## OBP (Mar 8, 2005)

Or you could not bother with it at all and just use Customer Selection Combo on the Check in/out Subform to bring the search results form if there are any Films out with them.


----------



## computerman29642 (Dec 4, 2007)

I am not sure I am following you.  

Are you saying use the customer combo that is already on the Check Out/In subform? Then a search result form would need to be created to display the movies (if any) that the customer has out?


----------



## OBP (Mar 8, 2005)

Yes, when you click to select the Customer you can run the check and if there any records either display them or have a Message come up.
Why bother entering their name twice


----------



## computerman29642 (Dec 4, 2007)

> Why bother entering their name twice


Good point. :up:



> Yes, when you click to select the Customer you can run the check and if there any records either display them or have a Message come up.


Is there a way to have the message display the titles the customer currently has checked out? Do you think it would be better to display a message or display the records?


----------



## OBP (Mar 8, 2005)

Displaying a form requires you build a Form, displaying a message requires you build the message in VBA using a recordset.
Which would you rather do?


----------



## computerman29642 (Dec 4, 2007)

I guess I would rather display the results on a form.


----------



## OBP (Mar 8, 2005)

Coward, where that learning Spirit disappeared to?


----------



## computerman29642 (Dec 4, 2007)

I thought the form would be the more complex.  I will accept your challenge, and do the message box.


----------



## OBP (Mar 8, 2005)

When do you want to start?


----------



## computerman29642 (Dec 4, 2007)

Now is as good of a time as any. Once this is completed, I believe this database will be done. :up:

I know....I know....I have said that before, but I am going to force myself to leave it alone this time.


----------



## OBP (Mar 8, 2005)

Oh yeah.
Ok first of all you need to create some records in the tblCustomer checkout to play with
and then you will need an After Update Event in the Customer Combo.


----------



## computerman29642 (Dec 4, 2007)

Both have been done.


----------



## OBP (Mar 8, 2005)

Ok, now comes the fun part.
copy this in to your After Update event and change the Table Names (in Bold) to your table name and the ProjectID to CustomerID and see if you can get it to work

Dim rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "Select *CombinedEquipmentprocessing*.* " & _
"FROM *CombinedEquipmentprocessing* " & _
"WHERE ProjectID = " & ProjectID
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
msgbox recount
rs.Close
Set rs = Nothing

What should happen is when you choose someone with Movies out it will tell you how many, if they haven't got any out you might get an error message or you might get a zero.


----------



## OBP (Mar 8, 2005)

You need to use jcc_CustomerID


----------



## OBP (Mar 8, 2005)

If the Customer has no movies out you will get an error message, "No Current Record"
so we need to modify the Code to this 
Dim rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "Select tblCustomerCheckout.* " & _
"FROM tblCustomerCheckout " & _
"WHERE jcc_CustomerID = " & Me.jcc_CustomerID
Set rs = CurrentDb.OpenRecordset(SQL)
recount = rs.RecordCount
If recount = 0 Then
rs.Close
Set rs = Nothing
Exit Sub
End If
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
MsgBox recount
rs.Close
Set rs = Nothing


----------



## OBP (Mar 8, 2005)

The reason for the Movelast, movefirst is that the Recordcount is not always Accurate unless you go to the last record.


----------



## computerman29642 (Dec 4, 2007)

Does it display a number if the movie has been returned or only if it is checked out?


----------



## OBP (Mar 8, 2005)

At the moment it only counts them all


----------



## OBP (Mar 8, 2005)

????????


----------



## computerman29642 (Dec 4, 2007)

Could we get it to only count the ones checked out, and give more detail than just a number?


----------



## OBP (Mar 8, 2005)

I have been waiting for you to come back before moving on to the next phase.
Is what you have working?


----------



## computerman29642 (Dec 4, 2007)

Yes, everything appears to be working.


----------



## OBP (Mar 8, 2005)

Ok, but in that table we don't have any Movie names.
So open the qryCustomerCheckout Query and add the Movie table to it and then add the movie Title to the columns, it won't affect the way that your subform works.
Now change the the VBA code to this
Dim rs As Object, SQL As String, recount As Integer, count As Integer, message As String
message = " This Customer has the Following Movies out "

SQL = "Select qryCustomerCheckout.* " & _
"FROM qryCustomerCheckout " & _
"WHERE jcc_CustomerID = " & Me.jcc_CustomerID & _
"And isnull(jcc_DateReturned)"

Set rs = CurrentDb.OpenRecordset(SQL)
recount = rs.RecordCount
If recount = 0 Then
rs.Close
Set rs = Nothing
Exit Sub
End If
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
message = message & " - " & rs.m_Title & vbNewLine
Next count
MsgBox message
rs.Close
Set rs = Nothing


----------



## OBP (Mar 8, 2005)

I will have to go soon, but that works OK for me on the limited trials that I have done.
It is very powerful isn't it.
SQL recordsets are great fun because they work in the background.


----------



## computerman29642 (Dec 4, 2007)

I will continue to test and let you know what I find.

Yes, very powerful. I am going to play around, so I can get a better grasp as to what it all does.

Thank you for all the help. I will keep you posted.


----------



## computerman29642 (Dec 4, 2007)

I just noticed that I have a customer that has two different movies checked out. However, the message box is displaying one title twice.


----------



## OBP (Mar 8, 2005)

It would you need this extra piece of code.
For count = 1 To recount
message = message & " - " & rs.m_Title & vbNewLine
*rs.MoveNext*
Next count


----------



## computerman29642 (Dec 4, 2007)

I have to run and take care of something real quick, but when I get back to my desk I will test that new code.


----------



## computerman29642 (Dec 4, 2007)

Perfect.


----------



## computerman29642 (Dec 4, 2007)

Is there a way to list all the movies after the sentence " This Customer has the Following Movies out "?


----------



## OBP (Mar 8, 2005)

& vbNewLine


----------



## computerman29642 (Dec 4, 2007)

Please review the attached database. If you agree that the database works well, and nothing else needs to be done then this database will be completed.


----------



## OBP (Mar 8, 2005)

Your Checkin/Out tab is currently Blank, to make it work on my older copy I had to reset the Form Properties.
How does yours work?

Ignore last, it is your Edit button again


----------



## computerman29642 (Dec 4, 2007)

Mine seems to work just fine. I have been playing around with it since we added the new code.

When you say "Blank", what do you mean? The CheckOut/in subform will be blank if there are no records, and the form is not in edit mode.


----------



## OBP (Mar 8, 2005)

see edit to last Post


----------



## computerman29642 (Dec 4, 2007)

Sorry!


----------



## OBP (Mar 8, 2005)

It's your database.
But that code does work well


----------



## computerman29642 (Dec 4, 2007)

I know, but I also want it to be user firendly.


----------



## computerman29642 (Dec 4, 2007)

I believe I have found another problem withteh search on the main form. Try performing a search by title or star, when the search form opens...click the details button. Now, go up to the title combo and select a different title. You will notice that the information in the subform does not change as it should.


----------



## OBP (Mar 8, 2005)

Use the magic that you found for the Frame in the Combo
Private Sub cboTitle_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[m_MovieID] = " & Str(Nz(Me![cboTitle], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
*Me.Form.RecordSource = Me.Form.RecordSource*

movieid = Me.m_MovieID
Me.sfrmMovie.Requery
End Sub


----------



## computerman29642 (Dec 4, 2007)

Should I take out the requery statement from the After_Update event?


```
Private Sub cboTitle_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[m_MovieID] = " & Str(Nz(Me![cboTitle], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Form.RecordSource = Me.Form.RecordSource
movieid = Me.m_MovieID

[B]Me.sfrmMovie.Requery[/B]
End Sub
```
The line of code

```
Me.Form.RecordSource = Me.Form.RecordSource
```
 fixed the subform information that was not changing, but now after a search has been perform and the user wants to select a different title from the combo, the combo jumps back to the first title in the list instead of displaying the title the user selected.


----------



## OBP (Mar 8, 2005)

I spent hours on this yesterday, I know what the form is "Not" doing but not why or how to fix it.
When the Combo searches immediately after the Form Filter has been set and finds the required record it uses 
me.bookmark = rs.bookmark 
to set the form to the record that has been found. Except it doesn't, the record stays the same. Adding the "Me.Form.RecordSource = Me.Form.RecordSource" just resets it back to the first record of the "Overall" Recordset.
If you change the Frame option before using the Combo it works Ok, if you make more than one selection it works Ok.

The only option that I can see to overcome the current problem though is to use VBA to step through the actual Form records to Find the Combo Selection instead of using "Bookmarks", or use the same method to find the "Search results" Filter.

I have thought of another improvement to the database and that is to have the "Search Results" Forms on the Mainform's Tabs, which means that you do not have to open and close them


----------



## computerman29642 (Dec 4, 2007)

I have spent many hours on this issue as well. I even broke the database for a moment. 



> The only option that I can see to overcome the current problem though is to use VBA to step through the actual Form records to Find the Combo Selection instead of using "Bookmarks", or use the same method to find the "Search results" Filter.


You let me know what would be best, and that is the road we will take.



> I have thought of another improvement to the database and that is to have the "Search Results" Forms on the Mainform's Tabs, which means that you do not have to open and close them


Sounds good to me.


----------



## OBP (Mar 8, 2005)

I will see what I can do.


----------



## computerman29642 (Dec 4, 2007)

I do not know the overall affect of doing this, and I have not tested this in detail, but I was able to fix the issue by doing the following....

I changed the After-Update Event of the cboTitle:


```
Private Sub cboTitle_AfterUpdate()
    movieid = Me.m_MovieID
    Me.sfrmMovie.Requery
End Sub
```
I then changed the subform data property.

*Link Child Fields: m_MovieID*
*Link Master Fields: cboTitle*

Once again, I still have a great deal of testing to do, so this may have broken something else within the database.


----------



## jimr381 (Jul 20, 2007)

If you want to have it actually apply the filter when the subform is opened with the main form, then you will want the moveid in the child table to match out with movieID in the master table as well. It is confused now and will not be doing the filtering for you.


----------



## computerman29642 (Dec 4, 2007)

I just found that the solutionI posted in my previous post breaks how the CheckOut/In subform works. I guess it is back to the drawing boards.


----------



## computerman29642 (Dec 4, 2007)

jimr381 said:


> If you want to have it actually apply the filter when the subform is opened with the main form, then you will want the moveid in the child table to match out with movieID in the master table as well. It is confused now and will not be doing the filtering for you.


I will see what happens if I switch it back to movieid.


----------



## OBP (Mar 8, 2005)

If setting the master child links to the combo works for the main Subforms I would stick with it.
When you say it broke the Booking in and out form did you also change it's master child link to the Combo as well?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> If setting the master child links to the combo works for the main Subforms I would stick with it.
> When you say it broke the Booking in and out form did you also change it's master child link to the Combo as well?


No, I did not try to change the master child link for the checkout form. I will change that and see what happens.


----------



## computerman29642 (Dec 4, 2007)

I changed the checkout form's master child link, and everything seems to be working properly.

I guess my question now is what effects will this have on the filtering that Jim spoke about in post #194?


----------



## jimr381 (Jul 20, 2007)

The filtering I am referring to, is it showing the related records in the subform when you change to a different record on the main form.


----------



## computerman29642 (Dec 4, 2007)

jimr381 said:


> The filtering I am referring to, is it showing the related records in the subform when you change to a different record on the main form.


Yes, the related records in the subform do display when a different record is selected. If I leave the master link as movieid, then the subform does not change when a different record is selected. The master link must be changed to cboTitle.

The only problem I have found so far is that the "Available" and "UnAvailable" on the main for is not stopped working properly.


----------



## computerman29642 (Dec 4, 2007)

> If setting the master child links to the combo works for the main Subforms I would stick with it.


When I changed the master child links to the main subforms (sfrmMovie & sfrmCheckOutIn), then the record started to change as it should when a search is perfromed from the main form. The only problem that is happening now is that when a movie is checked out, the txtAvailable textbox changes like it is suppose to do, but it displays "Unavaialble" for all movies from that point on.

For example, if a customer checks out the movie "Bad Boys", and then selects the movie "50 First Dates" in the combo, the txtAvaliable textbox still displays "Unavailable".


----------



## computerman29642 (Dec 4, 2007)

Would the best thing to do be to disbale the cboTitle after a search has been performed? This would force the user to select an option in order to use the combo again. Once a user has selected an option the combo becomes enabled again.


----------



## computerman29642 (Dec 4, 2007)

I do not know what else to do. If I get one thing fixed, then something else breaks.


----------



## OBP (Mar 8, 2005)

Chris, I will try and have a look over the weekend.


----------



## OBP (Mar 8, 2005)

Can you post a copy of your very latest level?


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the most recent version of the database.


----------



## OBP (Mar 8, 2005)

I have had a look at my last version and it is possible to overcome the "Combo" problem by using a different search method, I actually used the Form's records to loop through. The only downside is that you can see it doing it.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> I have had a look at my last version and it is possible to overcome the "Combo" problem by using a different search method, I actually used the Form's records to loop through. The only downside is that you can see it doing it.


What do you mean by "tou can see it doing it"?


----------



## OBP (Mar 8, 2005)

Have a look and you will see what I mean.


----------



## computerman29642 (Dec 4, 2007)

I have taken a look a the database. I see what you mean by you can see the data changing/updating. I found a way to keep that from happening. 

I did notice that when a search is performed, the cboTitle combo does not display anything.


----------



## OBP (Mar 8, 2005)

That was on the Form's On Current event, it had to be disabled to prevent the Combo reverting back to the first record on the form.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> That was on the Form's On Current event, it had to be disabled to prevent the Combo reverting back to the first record on the form.


So, there is not a way to have the title to display in the combo once a search has been performed?


----------



## computerman29642 (Dec 4, 2007)

Add the bolded lines to your code, and you will see how I got the form to stop showing the changes/updates.


```
Private Sub cboTitle_AfterUpdate()
    ' Find the record that matches the control.

    [B]DoCmd.Echo False[/B]
    
    Me.Filter = ""
    Me.FilterOn = False
    Dim recount As Integer, count As Integer
    DoCmd.GoToRecord , , acLast
    recount = Me.CurrentRecord
    DoCmd.GoToRecord , , acFirst
    For count = 1 To recount - 1
        If Me.m_Title = Me.cboTitle.Column(1) Then
            'MsgBox count
            Exit For
        End If
    DoCmd.GoToRecord , , acNext
    Next count
    Me.sfrmMovie.Requery
    
    [B]DoCmd.Echo True[/B]

End Sub
```


----------



## OBP (Mar 8, 2005)

You should be able to reset the combo once the search has finished.


----------



## computerman29642 (Dec 4, 2007)

So, I would need to use this line of code somewhere?


```
Me.cboTitle = Me.m_MovieID
```
That was commented out in the OnCurrent event procedure.


----------



## OBP (Mar 8, 2005)

It is finding the best place for it is the problem. We could have a hidden field to hold a flag to tell the form that a search has been performed and to update the CboTitle if that field is set. you can then sert that field from the Search results forms.


----------



## computerman29642 (Dec 4, 2007)

Would that be the best way to go?

This may be a silly question, but could we leave the coding as it was before, and have the hidden field to hold a flag?


----------



## OBP (Mar 8, 2005)

No, because the Me.Bookmark = rs.Bookmark is not working at all after performing the search.


----------



## computerman29642 (Dec 4, 2007)

OK...So, the best way to go is to create the hidden field, and insert the field from the search results...correct?


----------



## OBP (Mar 8, 2005)

I would update the field form the Search form when the Command button is clicked.
Set it to "" before the opening the search forms and then from the Search form command button set it to any value you like.
In the On Current event you can then check if that field is "" or has a value and requery or set the Combo accordingly.


----------



## computerman29642 (Dec 4, 2007)

So I can be sure we are on the same page.

The hidden field will be on the main tab form...correct? When the search command button is clicked, the field will be set to "". Once the search has been formed, you would set the field to be something like

```
hiddenfield = m_Title
```


----------



## OBP (Mar 8, 2005)

Yep and then when you set the Combo in the On Current event set the field back to "".


----------



## computerman29642 (Dec 4, 2007)

OK, I am going to see if I can get this to work. Thanks.


----------



## computerman29642 (Dec 4, 2007)

OK, I have everything working except for the On_Current event.

Here is the code I added to the On_Current event.


```
If Me.txtHidden.Value <> "" Then
        Me.cboTitle = Me.txtHidden.Value
        Me.txtHidden.Value = ""
    End If
```


----------



## computerman29642 (Dec 4, 2007)

Nevermind, I believe I have it working now.


----------



## OBP (Mar 8, 2005)

Well Done :up:


----------



## computerman29642 (Dec 4, 2007)

Well, I still have to do some testing, so I will keep you informed.


----------



## computerman29642 (Dec 4, 2007)

I believe the database is working as it should in every area. So, I have attached the final copy of the database. If anyone finds any issues, please let me know.

OBP, I would like for you to test the final copy when you have a moment.


----------



## OBP (Mar 8, 2005)

Ok, will do.


----------



## computerman29642 (Dec 4, 2007)

Thanks.


----------



## OBP (Mar 8, 2005)

Can I suggest that you remove the Properties display from the Switchboard?


----------



## computerman29642 (Dec 4, 2007)

Sure.  Did you want me to remove the properties display, and repost the database?


----------



## OBP (Mar 8, 2005)

Your Checkout/in form seems to have gone walkabout.


----------



## computerman29642 (Dec 4, 2007)

That is the one form I did not check. I guess I thought since no changes were made, it would be fine. Let me take a look.


----------



## computerman29642 (Dec 4, 2007)

What is the Checkout/in form doing for you?


----------



## OBP (Mar 8, 2005)

It isn't there at all.


----------



## computerman29642 (Dec 4, 2007)

Do not forget that if there is not data, then you will need to enter the password to get the blank field(s) to display.


----------



## OBP (Mar 8, 2005)

I just realised that, sorry.


----------



## OBP (Mar 8, 2005)

When you book in or out the VBA refuses to admit that it can Set the Focus on the cboTitle Combo even though it does actually do it.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> When you book in or out the VBA refuses to admit that it can Set the Focus on the cboTitle Combo even though it does actually do it.


What do you mean? Are you getting an error message?

When I test the book in or out, I do not get an error message, but I do noticed that something wierd is happening with the dates.

Just when I thought this db was completed...._sigh_


----------



## OBP (Mar 8, 2005)

You also have a problem that even when a Movie is "Un-Available" you can still book it out before it has been booked in.


----------



## computerman29642 (Dec 4, 2007)

Well, I guess there is more work to be done. Did you see the weird thing with the dates?


----------



## OBP (Mar 8, 2005)

What wierd thing?
Change the Password Enter button VBA to this for the Allowadditions
If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
Else
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
End If


----------



## computerman29642 (Dec 4, 2007)

Select a movie to check-out. Enter the date. Select a different movie to checkout, use the same customer, and enter a checkout date that is different than the one used for the first movie. You will notice that the check-out date for the second movie displays the date you entered for the first movie.


----------



## computerman29642 (Dec 4, 2007)

I now see what you were talking about with the 'Set Focus' on the cboTitle combo.


----------



## OBP (Mar 8, 2005)

I have found another minor problem, you can select a Title for Checkout but not enter a checkout date, which of course creates a record but doesn't set the Availability field.


----------



## computerman29642 (Dec 4, 2007)

Is there a way to requery the form after the code


```
If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
Else
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
End If
```
is true or false?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> I have found another minor problem, you can select a Title for Checkout but not enter a checkout date, which of course creates a record but doesn't set the Availability field.


LOL....I cannot win for losing.

I believe I noticed that before, and that is why I added the undo buttons. Do you think I should add delete buttons there as well. So, the user can delete a blank record if created?


----------



## OBP (Mar 8, 2005)

I am looking at ways around it at the moment.
Why did you need to requery in your previous post?


----------



## computerman29642 (Dec 4, 2007)

With this code

```
If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
Else
[Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
End If
```
 if the user brings back the movie, the availability changes back to "Available". However, the form would have to be closed and re-opened before the movie could be checked-out again.

It would be nice if when a user returns a movie, the status changes to "Available" the new record fields display. The same for when the status of the movie changes to "UnAvailable". The new record fields do not display.


----------



## OBP (Mar 8, 2005)

I will have a look tomorrow.


----------



## computerman29642 (Dec 4, 2007)

OK. I will keep testing until then.


----------



## OBP (Mar 8, 2005)

Have a look at this version, I think it addresses those issues we raised yesterday


----------



## computerman29642 (Dec 4, 2007)

Tony, what changes did you make?

The only thing I see is that if the user has already entered edit mode, selects a movie that has never been checked-out, they must go out of edit mode before they are able to input a check-out date.

I was thinking of adding a code like this

```
If [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True Then
        If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
        Else
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
        End If
    End If
```
to the After_Update event pf the cboTitle combo, but it seemed redundant.


----------



## OBP (Mar 8, 2005)

On the old version you could select a bookout customer and then leave the form without entering a checkout date, on this version I have added VBA to the subform's On Exit event to prevent that.
I have also changed the Close Button VBA, when you are in the checkou subform and click the close button it only closed the subform.
I have also re-arranged some of the other VBA as well.


----------



## computerman29642 (Dec 4, 2007)

Well, I believe the only issue left is the one I mentioned above. I can see the light at the end of the tunnel.


----------



## computerman29642 (Dec 4, 2007)

I added this code to the After_Update event of the cboTitle combo.


```
If [Forms]![frmMovie]![cmdUnEdit].Visible = True Then
        If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
        Else
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
        End If
    End If
```
Do you see any problems withthis code?

If you would, could you check to see if this is happening with your copy of the database? When I checkout a movie to a customer....I select the customer's name, enter the out date, and then select the dropdown arrow on the combo to select another title. Before the titles are display, the screen flashes/pauses for a second (I guess to requery). If you look back at the customer name in the checkout, you will see the name changed to the last customer in the list. However, if you switch titles and come back, then the customer name displayed is the one you entered.


----------



## computerman29642 (Dec 4, 2007)

I was looking at this code


```
Private Sub sfrmCheckOutIn_Exit(Cancel As Integer)
If Not IsNull(Me.sfrmCheckOutIn![jcc_CustomerID]) And IsNull(Me.sfrmCheckOutIn![jcc_DateOut]) Then
    MsgBox "Please Enter a Checkout Date"
    Me.sfrmCheckOutIn.SetFocus
End If
End Sub
```
What happens if the CustomerID is Null?

I changed the code to this..


```
Private Sub sfrmCheckOutIn_Exit(Cancel As Integer)
If Not IsNull(Me.sfrmCheckOutIn![jcc_CustomerID]) And IsNull(Me.sfrmCheckOutIn![jcc_DateOut]) Then
    MsgBox "Please Enter a Checkout Date"
    Me.sfrmCheckOutIn.SetFocus
[B]Else
    Me.Undo[/B]
End If
End Sub
```
Do you see any problems with this?


----------



## OBP (Mar 8, 2005)

That works for me, but I am not sure why it is needed, what triggers the Allowedits = no when switching to the Combo?


----------



## computerman29642 (Dec 4, 2007)

Nevermind, I took out the code

```
If [Forms]![frmMovie]![cmdUnEdit].Visible = True Then
        If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
        Else
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
        End If
    End If
```
and retested. Now I cannot get it to reproduce the problem I was having. It appears to be working now.

I think the database is just trying to mess with me and make me go crazy.


----------



## computerman29642 (Dec 4, 2007)

I just found another issue.

Open the database, enter a title in the search field, click search, click details, enter edit mode, checkout the movie to a customer.

The movie will go to "UnAvailable" as it suppose to, but if you select a different title, and then come back to that title it shows "Available".


----------



## OBP (Mar 8, 2005)

No it didn't do that.
can you check where this is on the Customer Combo After Update VBA
rs.MoveLast
rs.MoveFirst
I moved them by mistake, they should be after the 
If recount = 0 Then
rs.Close
Set rs = Nothing
Exit Sub
End If

and not before it.


----------



## computerman29642 (Dec 4, 2007)

Is this correct?


```
Private Sub jcc_CustomerID_AfterUpdate()

    Dim rs As Object, SQL As String, recount As Integer, count As Integer, message As String
    
    message = " This Customer has the Following Movies out " & vbNewLine
    
    SQL = "Select qryCustomerCheckout.* " & _
    "FROM qryCustomerCheckout " & _
    "WHERE jcc_CustomerID = " & Me.jcc_CustomerID & _
    "And isnull(jcc_DateReturned)"
    Set rs = CurrentDb.OpenRecordset(SQL)
    recount = rs.RecordCount
    If recount = 0 Then
        rs.Close
        Set rs = Nothing
    Exit Sub
    End If
    [B]rs.MoveLast
    rs.MoveFirst[/B]
    recount = rs.RecordCount
    For count = 1 To recount
    message = message & " - " & rs.m_Title & vbNewLine
    rs.MoveNext
    Next count
    MsgBox message
    rs.Close
    Set rs = Nothing
    Me![jcc_DateOut].SetFocus
End Sub
```
What issue does this address?


----------



## OBP (Mar 8, 2005)

Yes, that is fine.


----------



## computerman29642 (Dec 4, 2007)

What did that do?


----------



## OBP (Mar 8, 2005)

If the Customer did not have movies booked out the Movelast creates a "No Current Record" Error if it before the recount test, which if zero close and exits.


----------



## computerman29642 (Dec 4, 2007)

> No it didn't do that.


What was this statement referring to?

When I use the search field and then try to checkout a movie to a customer, as soon as I click the unedit mode button, the m_Availability filed is changing from "UnAvialble" to "Available".


----------



## OBP (Mar 8, 2005)

No it didn't do that when I just tested it.


----------



## computerman29642 (Dec 4, 2007)

I just noticed that your copy of the database does not do that. I will have to match codes to figure out why mine does.


----------



## OBP (Mar 8, 2005)

It could be one of those background VBA changes that I made, which I can no longer remember


----------



## computerman29642 (Dec 4, 2007)

LOL...I guess I got my work cut out for me then.

When you say background VBA, what do you mean?


----------



## OBP (Mar 8, 2005)

Well I was just looking at things that didn't quite work and fixing them as I went.


----------



## computerman29642 (Dec 4, 2007)

I am matching up the codes behind the forms, and everything appears to be the same.


----------



## computerman29642 (Dec 4, 2007)

OK, I figured out the search field issue. The problem was where I added the 'Me.Undo' to the following code:


```
Private Sub sfrmCheckOutIn_Exit(Cancel As Integer)
If Not IsNull(Me.sfrmCheckOutIn![jcc_CustomerID]) And IsNull(Me.sfrmCheckOutIn![jcc_DateOut]) Then
    MsgBox "Please Enter a Checkout Date"
    Me.sfrmCheckOutIn.SetFocus
[B]Else
    Me.Undo[/B]
End If
End Sub
```


----------



## computerman29642 (Dec 4, 2007)

Now if I can just figure this out...

I select the customer's name, enter the out date, and then select the dropdown arrow on the combo to select another title. Before the titles are display, the screen flashes/pauses for a second (I guess to requery). If you look back at the customer name in the checkout, you will see the name changed to the last customer in the list. However, if you switch titles and come back, then the customer name displayed is the one you entered.

There is still an issue where if a movie as never been checked out, and when in edit mode you select that movie the checkout fields do not display.


----------



## OBP (Mar 8, 2005)

It doesn't do that first part for me, as soon as you move out of the Check out Form the the available field is updated to Unavailable and the Checkin fields disappear.
It doesn't appear to be doing the second item either (at the moment) but perhaps there are certain conditions where it will.
Do you want to try my version again?


----------



## computerman29642 (Dec 4, 2007)

OK, When the checkout fields disappear, are you able to select a different title and they reappear?

Sure, I will try your copy again.


----------



## OBP (Mar 8, 2005)

yes, it seems to be working OK


----------



## computerman29642 (Dec 4, 2007)

I can see that yours does work correctly. I guess I will have to match codes to determine why mine is not working correctly.


----------



## computerman29642 (Dec 4, 2007)

I found the problem. You kept the following code in the After_Update Event of the cboTitle combo, and I deleted the code.


```
If [Forms]![frmMovie]![cmdUnEdit].Visible = True Then
        If [Forms]![frmMovie]![m_Availability] = "UnAvaliable" Then
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = False
        Else
            [Forms]![frmMovie]![sfrmCheckOutIn].[Form].AllowAdditions = True
        End If
    End If
```


----------



## OBP (Mar 8, 2005)

That is really funny because you suggested putting it there.


----------



## computerman29642 (Dec 4, 2007)

LOL....That is the first thing that I thought as well.


----------



## computerman29642 (Dec 4, 2007)

I found a problem. When I added that code back to the After_Update Event, the user no longers has to enter a password in order to make changes to the checkout form.

I tested your copy, and it does the same thing.


----------



## computerman29642 (Dec 4, 2007)

Nevermind, I found that the cmdUnedit button visible property was set to yes. As soon as I changed it to no, the form started working correctly.


----------



## OBP (Mar 8, 2005)

Is this the Final Final version?
Or did you intend doing something about "Kids".


----------



## computerman29642 (Dec 4, 2007)

I am still undecided about the "Kids".

If you would not mind, could you take a look at one more thing. When the report is opened, at the top-right you will see a "Restore Window" button. Is there a way to disable that button?


----------



## OBP (Mar 8, 2005)

I don't know of any method, but there probably is one.


----------



## computerman29642 (Dec 4, 2007)

I will keep researching and playing. I have attached, what I believe to be, the final copy of the movie database.

OBP, once again if you would not mind taking a look. If you think it is good, I will mark the thread as solved.


----------



## OBP (Mar 8, 2005)

Before I look have you removed the Properties from the Switchboard yet?


----------



## computerman29642 (Dec 4, 2007)

I believe so.  When I open my copy, it opens fine.


----------



## OBP (Mar 8, 2005)

But Not on the version that I have just downloaded.


----------



## computerman29642 (Dec 4, 2007)

When you say "Removed the properties from the switchboard", what exactly do you mean?


----------



## ~Candy~ (Jan 27, 2001)

Wow, you two are like the energizer bunny


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> Wow, you two are like the energizer bunny


What?!?!?


----------



## OBP (Mar 8, 2005)

The Form's Properties are Visible when the Form is in Form View Mode, I think it should only be visible in Form Design Mode.


----------



## computerman29642 (Dec 4, 2007)

Let me try to attach it once more. When I open the database here, I do not see the properties.


----------



## OBP (Mar 8, 2005)

Aca, thanks for the compliment.
I think.


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> What?!?!?


You just keep going :up: I'm just waiting and watching to see how you keep making changes, etc. It's all Greek to me  but, fun to watch 



OBP said:


> Aca, thanks for the compliment.
> I think.


Yes, it was a compliment indeed :up:


----------



## computerman29642 (Dec 4, 2007)

I have attached another copy of the final movie database. I checked to be sure that the property screen was closed on all forms. Let me know how it does on your end.


----------



## computerman29642 (Dec 4, 2007)

AcaCandy said:


> You just keep going :up: I'm just waiting and watching to see how you keep making changes, etc. It's all Greek to me  but, fun to watch
> 
> Yes, it was a compliment indeed :up:


LOL...Thanks.

I like to keep playing and testing. It helps me learn more and more. I believe I have done all I can to this database, however.

If you view the final copy, and what to make any of the changes to your version just let me or OBP know.


----------



## OBP (Mar 8, 2005)

No it is still the same, the Switchboard (frmSwitchboard) still shows it's properties when you open the Database, perhaps your Access has different settings to mine.
Open frmSwitchboard in design mode and look at the All Properties Tab, 3rd Item from the bottom "Allow Design Changes" = All Views"


----------



## computerman29642 (Dec 4, 2007)

How about now?


----------



## OBP (Mar 8, 2005)

Success.:up: 
I wonder why it doesn't show up on your Access?
When I get some time I will do some testing.


----------



## computerman29642 (Dec 4, 2007)

Yes! :up:

Thanks.


----------



## ~Candy~ (Jan 27, 2001)

gamecockfan said:


> LOL...Thanks.
> 
> I like to keep playing and testing. It helps me learn more and more. I believe I have done all I can to this database, however.
> 
> If you view the final copy, and what to make any of the changes to your version just let me or OBP know.


:up: Thanks, you both are very generous


----------

