Access Help

Associate
Joined
31 Jan 2007
Posts
1,860
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?
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
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.
 
Associate
OP
Joined
31 Jan 2007
Posts
1,860
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?
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
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.
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
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.
 
Associate
OP
Joined
31 Jan 2007
Posts
1,860
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
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,324
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.
 
Associate
OP
Joined
31 Jan 2007
Posts
1,860
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:
Back
Top Bottom