How do i create an Access lookup table

[sam49]

Hi not sure if this is in the correct forum.
Can someone please help me with a problem in access 2007.
I am using a database from Microsoft from Microsoft web site for Book’s.
On the form i input the data there i a box called topics and when i move to this box all i have to do is put in the first letter of a book topic and access get the relevant data from another table and completes the rest.

What i am trying to do is make another one of these items so i can quickly input the publishes details just like the topic details.
I believe it’s called a lookup table or a combo box.

I have seen these items in a music collection for the artist’s names and in that there was two of theses boxes.

So how do i make this happen or can someone recommend a good video tutorial to help me please.
Regards

 

[namliam]

It is a default functionality of what is called a "Combo box"

If you make a new combobox the wizard will guide you thru the setup and you are done...

Good luck! Post back with questions if you have them

And *Welcome to the forum*

 

[Banana]

Just to be clear- there are two things:

Lookup Fields which are a bad thing™ and LookUp Tables which is a good thing™. I don't know what music collection actually used but if you were able to open the table and select form a list, then that's lookup field and it's known to cause problems.

Anyway, assuming we have this table:

tblBooks
BookID (Primary key, Autonumber)
AuthorID (Number, Long Integer)
BookTitle (Text)

and we want to look up the Author name. Note that we don't store the name in the book table but only the key. That's called foreign key because it depends on a primary key from another table, which in our case is the lookup table. However there is no actually a button or option to choose "foreign key"; it is defined implicitly in later step.

lkpAuthors
AuthorID (Primary key, AutoNumber)
AuthorName (Text)

You would create a relationship between AuthorID in tblBook and AuthorID in lkpAuthors and it would be a one-many. That's how Access then know that the AuthorID in tblBook is a foreign key.

Once you have the two table setup, you now go to to creating a new form. You would bind the form to the tblBooks. You would then add a new combobox to the form.

If you have command wizard on, it'll give you options- choose the one saying that "get value from other table or something like that, then choose the tblAuthor, and use AuthorID as the bound column while displaying the AuthorName.


So the combobox's setting would be:

Format tab:
Column Count: 2
Column Width: 0;

Data tab:
Bound Column: 1
Rowsource: SELECT AuthorID, AuthorName FROM lkpAuthors;


Did that help?

 

[sam49]

Hi guys thaks for the help.

I still carn't get it to work i guess i must be missing something.

Does anybody know of a video tutorial i could buy that way i might be able to follow what to do.

I just dont seem to get it.

Regards Edward

 

[Banana]

There are video tutorials, made by Crystal who is great at what she does (teaching Access) and a great person as well, but I don't think it cover comboboxes. I'm sorry I don't know of any other video clip.

 

[MSAccessRookie]

Hi guts thatns for the help.

I still carn't get it to work i guess i must be missing something.

Does anybody know of a video tutorial i could buy that way i might be able to follow what to do.

I just dont seem to get it.

Regards Edward

I am sure there are video series that you can buy, but there are also a few useful video tutorials online, including an entire series that is available for free on YouTube. A google search for Access Video Tutorials should get you pointed in the right direction.

 

[sam49]

Thank you i will give utube a try
Regards to you all

 

[sam49]

Hi Guys,
Ok i have managed to make a table with the information i want to lookup, and i have made a combo box to look at this table and i can select what ever entry is in this table. This is a great step forward for me.
The only problem i have is i have to enter any new data in the table before i start to be able to see it in the combobox.

So how do i get to be able to double click in the combo box and add new data to my table.

On the form in design view, the combo box say Unbound is this something to do with me not been able to updat the table with new data.

Also when i input a new record the combo box has the last data already in, how do i get it to start with no data in it please.

Regards

 

[Banana]

Normally, it should be bound to the field of the many-side table you want to put the data in. (unless you are using it to search for records, in which case we want an unbound combobox).

As for adding new entries, see if this link helps.

 

[sam49]

Hi Guys
I recently asked for help with my access 2007 database and with the help of the advice given i was able to create a combo box that looks at a table i made, i can the select the data from this other table and it automatically enters into the form i am working on.

One last question if i may the database i am trying to alter is one i downloaded from Microsoft and is the book database.

On this database there is a combo box where if i double click it brings up the table it is linked to,then i can put new data into the table.

I am unable to get this double click to work, I have created a table and a combo box linked together, and if i open the form in design and then go to properties and then to events i see and item for Dbl click .
I presume this is where i tell access to open the table linked to the combo box, But how do i do this please?

 

[jsv2002]

You need to create an event procedure you will see a button with 3 dots ... click that and select event procedure then type in something similar to;

DoCmd.OpenTable "your table name here"

Alternatively look at the event procedure of the command button/hyperlink that you want to emulate and copy the code to your control altering to your relevant table name.

Good luck John

 

[lmcc007]

Just to be clear- there are two things:

Lookup Fields which are a bad thing™ and LookUp Tables which is a good thing™.

Are Lookup Fields when you choose "I will type in the value I want." ? Why is it a bad thing? Especially, when you only have 5 to 10 entries.

 

[Banana]

No, they're in the table design view, not in the form design view using combobox wizard.

Evils of Lookup Fields

 

[ajetrumpet]

Are Lookup Fields when you choose "I will type in the value I want." ? Why is it a bad thing? Especially, when you only have 5 to 10 entries.

You're referring to a list box creation with the wizard in access.

lookup tables are awesome, and i use them all the time. lookup fields are NOT. these fields are basically just list boxes and combo boxes as default controls in fields of tables instead of text boxes. of course they have to look up information from other places though.

 

[jsv2002]

Are Lookup Fields when you choose "I will type in the value I want." ? Why is it a bad thing? Especially, when you only have 5 to 10 entries.

Because it is no longer a "normalised" database and in my view should not be an option at table level it causes many problems later down the line...

 

[lmcc007]

You're referring to a list box creation with the wizard in access.

lookup tables are awesome, and i use them all the time. lookup fields are NOT. these fields are basically just list boxes and combo boxes as default controls in fields of tables instead of text boxes. of course they have to look up information from other places though.

The difference between creating them in table design versus form design is still a little confusing to me.

I am currently playing with creating them in form design. Anyway, just learn something new.

I am not understanding the difference in them since you build your form from a table or query. The lookup field seems to work the same as it does in a table--it was created the same. I just discovered there is an additional choice, which is: Find a record on my form based on the value I selected in my combo box.

Let me continue to play with it to see what it does; I am trying very hard to learn the basic--tables and stuff.

 

[jsv2002]

Say you had 2 related tables one was part the other shape, You have PartID and ShapeID with ShapeID being a foriegn key in Part table.

So you could have 3 defined shapes square ID 1, Circle ID 2, Triangle ID 3 but many parts using this table. So you get:

Part 1 Shape 2 = Circle
Part 2 Shape 1 = Square
Part 3 Shape 3 = Triangle

and so on...

But if you change the underlying table of parts to "lookup" shape instead of the "ID" field you show the name this is ok viewing at table level but say you want to run a SQL query in a form later on...

So your table displays the "Name" instead of the "Number ID"



Therefore you write your query;

SELECT Part, ShapeID
FROM Part
WHERE ShapeID="Circle"

You will get a data mismatch because the stored value is the ID number not the "lookup" you have created in the table...

So just confused myself greatly trying to explain something that should not be allowed to happen in the first place...

 

[Banana]

What you are doing is just fine.

The "Lookup Fields" is not something you define in form design. You would define it in table design view. (e.g. on database windows, click Table tab, then open a table in design view. Select one of field. ON the bottom where you define properties, there's a tab for "Lookup" That one is the spawn of devil.)

 

[lmcc007]

What you are doing is just fine.

The "Lookup Fields" is not something you define in form design. You would define it in table design view. (e.g. on database windows, click Table tab, then open a table in design view. Select one of field. ON the bottom where you define properties, there's a tab for "Lookup" That one is the spawn of devil.)

Okay, I am confused. Here is what I did for my name prefixes in Table Design.

tblCompanies

CompaniesID AutoNumber
CompanyName Text
NamePrefixes Lookupwizard...
I will type in the value I want
Next>
Col 1--I typed: Dr.
Mr.
Mrs.
Miss
Ms.
Next>
What label would you like for your lookup column?
NamePrefix
Finish

then I tried it in form in Design View by creating a Combo Box. I selected:

Combo Box (Form Control)
I will type in the values I want
Next>
Col 1--I typed: Dr.
Mr.
Mrs.
Miss
Ms.
Next>
Store that value in this field: NamePrefixes
Next>
What label would you like for your combbo box?
NamePrefix
Finish

So, are both ways okay? Or, is the form procedure the preferred or the best method?


What does "Remember the value for later use?" do.


THANKS!

 

[Banana]

Ah, right. I forgot about another way to do it; the first one you did in table design is same thing as clicking the LookUp tab on the bottom of the window where you set the field properties and is a bad thing.

The one you did in form, is the preferred and best way of doing it.

Remember the value for later use basically does nothing in terms of data manipulation; it's left up to you to make use of it via VBA or macros .