1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Access Help

Discussion in 'Windows & Other Software' started by rctneil, 6 Mar 2010.

  1. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    Hey,

    I ahve made a database containing two tables in Access and they are linked with a 1 to M relationship. I ahve made a form from one of the tables and on the form is a box where the user enters the id of the company associate with that product. Instead of the user having to find the id and then enter it into the text box. How can I get a combo box to appear and it auto fill with the names of all the companies from the companys table and when the form is submitted then the correct id appear in it's place?
     
  2. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    Don't you just add a combo box and follow the wizzard? I've not used forms in access for a while but I think it does a pretty good job of doing it all for you.
     
  3. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    I'm trying all sorts to no avail here. Whats confusing me, is how I get the combo box to dynamically display all the names of the companies in the box but when one is chosen then the associated id of that company is used when inserting into the table?
     
  4. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    Try this:

    Open up your form in Design View. Click the combo-box control and drag it onto your form where you want it to appear (in the detail section).

    A wizard should come up, select 'I want the combo box to look up the values in a table or query'. Click next.

    Select the table you want to read the values from, i.e. the company table. Click next.

    Select the 'company name' field or whichever field(s) you want to display in the combo box and move it over to the 'selected fields' side. Click next.

    Set your sorting options, most probably this will be by company name. Click next.

    You'll now see a preview of your data; just click next if it's ok.

    Now select 'store that value in this field' and select your CompanyID column; this will then automagically put the ID of the selected company into this field.

    Click next, give it a name and click finish - done.
     
  5. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    Right, I started that but no wizard is appearing when i draw the box on the form?
     
  6. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    Which version of access have you got?

    Oh, make sure you have 'use control wizards' selected too.
     
  7. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    i'm using 2010 beta but it's not terribly different from 2007
     
  8. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    Ah ok. I had a feeling it was some archaic version :p.

    The 'use control wizards' is to the right of the controls in 2007 at least.
     
  9. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    Found that option. It's in a drop down below the controls now!

    Just following your steps now, will post back!
     
  10. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    Excellent, Thanks so much. My form is sooo much easier to use now!

    I will have some other access questions as I get further on with creating this mini system
     
  11. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    nice :).

    Make sure you get a good database design from the start, other wise it's a nightmare to come back and change it all :D.
     
  12. rctneil

    Wise Guy

    Joined: 31 Jan 2007

    Posts: 1,864

    ok, another question for you:

    I have made a form being used as a main menu sort of thing. I have added a button to open a form and it works and does open the form. How can I make the button open the form but open it so the form is blank ready for a new entry to be added?

    I also wish to add another button to open the form but to just open it to any records page so the user can just use navigation buttons to browse throguh the records one by one


    Bonus Question. This system is aiming to be a simple system for me to be able to organsise any receipts from orders I do as I currently don;t have a way to keep track of them. I don;t need anything fancy but a system like this will enable me to get what I want and also for me to learn a bit about using Access to build a database application:


    Right, If I am on the "Add Receipt" form whcih I ahve already built and I fill in all the details and go to choose the company from the drop down list but the company I want does not yet exist. What is the best way of adding it without losing the data already entered or how can I create a new company and associated fields on the same "add receipt" form or open the "add comapny" form, fill in the details of that and then choose the newly added company from the dropdown in the receipt form?
     
    Last edited: 6 Mar 2010
  13. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,207

    Location: Derbyshire

    Hmm you could right click on your button then go to 'build event' and set the 'data mode' to 'add'.