Forms - I have a problem with creating a form. Please help

[mouk2007]

I am designing a DataBase on a DVD rental shop in MS Access. I wish to create a form allowing the user to select a DVD CopyNumber and find the details of the last loan for that copy (name of the person who borrowed it, DVD title, DateOut, DateDue and DateReturn).

I have set up a query containing all these information, but I am not sure how to create a form allowing the user to select the DVD copy number to see these information.

Could anyone please help me with this. Many Thanks

 

[fneily]

Did you look at the combobx wizard from the toolbox toolbar? Create a combobox in the form hearder. On the second screen of the wizard, select the third option.

Also, it would help if you posted your table structures(fieldnames and primary keys).

 

[JoeAtWork]

In my day, practically every beginners textbook on programming and databases used the "VHS Video Rental Store" for their example application. Looks like the only thing that's changed is now it's DVD's.

 

[mouk2007]

Thanks for your reply 'fneily'.

The problem that I have is that in the combobox wizard only 2 options are available and the 3rd option (find a record on my form based on the value I selected in my combo box) is not there and only the first 2 options are there. I am creating this form based on a query. Could you please let me know why the 3rd option is not there and what I should do. Many Thanks.

 

[fneily]

Sounds like your form is not bound to your query or table. If you're using SQL, see:
Combo Box Wizard Does Not Display Third Option If a Form Is Based on a SQL Statement

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

Use the Wizard to create a form based on your table or query. When the form is complete then save it and open it in design view. Use the Wizard to add a combo box to this form and on the Wizard first screen select the 3rd item, "Find a record...." Finish the Wizard.

 

[mouk2007]

Thanks again fneily. I have created a form based on my query using wizard by clicking on form using wizard option, the selecting my query and adding all the information and clicking on Finish. However when I open it in design view and add a combo box the third option is still unavailable. Could you please let me know what I should do differently.

Many Thanks.

 

[GingerR]

make the form be based on a TABLE (i.e. whatever your main table is that has one DVD ID per row)

also, again, it would help if you shared your table structure with us.

you should probably have a main form (based on main table where each DVD ID is unique) and a subform (based on a table which holds the rental history and for which in this case you only want to show the latest rental record).

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[fneily]

Bring up the Property sheet of your form. Click the All tab and post what's in the Record Source Option

 

[mouk2007]

Here it is:

SELECT Member.MemberFirstName, Member.MemberLastName, Loan.DateOut, Loan.DateDue, Loan.DateReturned, DVDTitle.DVDTitle FROM (DVDTitle INNER JOIN DVDCopy ON DVDTitle.DVDNumber=DVDCopy.DVDNumber) INNER JOIN (Member INNER JOIN Loan ON Member.MemberNumber=Loan.MemberNumber) ON DVDCopy.CopyNumber=Loan.CopyNumber;

 

[fneily]

Your given us dribs and drabs. Post your table stuctures - fieldnames, primary keys.

Also, create a new query with just one of the tables you are using. Create a form from that and create a combobox. See if you have the third option. Then add a second table in your query, add fields (use the Field button) to the form, create another combobox and check for the third option. ETC.

 

[mouk2007]

Sorry, got it.

Thanks.