Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 30th, 2005, 04:44 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I like the off the wall cartoons and anime. (Invader ZIM, Family Guy, ect.) My form is for data entry, tracking. And I want to save the meaningful data. Example, in the first combo box, I'm choosing what is being changed. (Users, Queues, Rules, Departments, etc) I want these values to show in the table. I will most likely be viewing the saved data using reports, so if there is a way to make sure the meaningful data is on the report, that will work as well. I don't think I will be using the table that this data is saved to for anything much.

Jen, with the Joo Joo.

 
Old March 31st, 2005, 08:06 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

When you use a combo box look up to a foreign key, you are saving the foreign key. This is a good thing. You can then display any of the meaningful data from the foreign table be referencing the foreign key.

In order for any of the meaningful data to show up in your reports, you will need to base them on a query, and not just on the table. If you base them on the table alone, you will get a number instead of meaningful data. If you make a query with the look up table as part of the query, you can display any of the meaningful data from the foreign table based on the foreign key reference.

As a matter of fact, it is not uncommon to do something like using a States table to add a state to an address. The look up might be to State - "MD" but in the Maryland record you can also put the Full State Name - "Maryland", State Motto - "Fatti Maschii Parole Femine", State Sport - "Jousting", etc. Then if you want any of these in your report, you just add them to your query. If you just use the table you will get State - "25" or something like that.

Do we have all your issues covered?

mmcdonal
 
Old March 31st, 2005, 09:56 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jen, Looks like mmcdonal covered you while I was away on vacation.

The points about storing the foreign key (FK) and referential integrity are important.

Of course this CAN require that your reports be based on a query rather than on the table itself. However, there is a way around this...

If you go to the table definition there is a "Lookup" tab at the bottom. This lookup applies to each field. If you define the "Display Control" as a "Combo Box" and set the other properties much the same as you do for the combo boxes on your forms, you can generate a report directly from the table without having to build a query. (I'm using Access 2003. I haven't tried this in prior versions.)

The neat thing about doing this is that the Wizards will automatically put combo boxes on reports and forms to select/display the meaningful data rather than the foreign key. Additionally, if you display the table in datasheet view, you get to see the meaningful data, including the ability to select the data using a combo box.

But even if you don't used the Lookup settings for a field, it is relatively easy to build a query for your reports.

I highly recommend sticking with the FK. However, if you insist on storing the "meaningful" value, you can simply change your combo boxes on your forms to select the meaningful value without the autonumber field (changing the column count and other properties as appropriate). You will of course have to change the fields in your table to be able to store text instead of a number.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 31st, 2005, 04:51 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I have been well covered! I hope you had a nice vacation. I'm going to hang on to these postings. After I have had a chance to become more familiar with Access and queries, I'm sure I'll be able to use this advice.

Sorry if I'm getting repetitive but I have a few things I wanted to get out, maybe this will help you help me...

My combo boxes are bound to the same table that my form is bound to.

The Primary Key from my combo boxes' tables is the value that is being stored in this table.

The combo boxes are pulling a value in another field in their tables the data I want to see, is this the Foreign Key?

The second paragraph in mccdonal's last posting makes sense, but I'm just not sure where to go with it. For instance, what type of query would I need to use?

Still playing with it...

:-) Jen of the JooJoo.

 
Old March 31st, 2005, 05:18 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

After playing some more, I have some news to report. I now have 2 of my combo boxes storing the right data in the fields of the table that they are bound to. I changed the bound column from 1 (the auto number field) to 2 (the dropdown data field). This works for the second combo boxes in each set. If I do this to the first combo boxes in each set, the second combo box is no longer updated to show just the items that relate to the option selected in the first. (done by query in second combo box, requery in first.)

Halfway there!

Jen

 
Old April 1st, 2005, 10:38 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Glad you're making progress. The vacation was nice. Got to stay at my cousin's B&B http://www.circlesranch.com.

The query that mmcdonal talks about is rather simple. If you hadn't changed your bound columns, you'd just be using the query builder. In the query builder you'd add the main table and each of your lookup tables. For each lookup field in the main table you'd link the numeric value to the key in the lookup table. This is the Foreign key. Another way to put that is that you would link the numeric value to the key in the foreign table, hence Foreign Key.

When you select fields to display in the query, instead of selecting the numeric value from your main table, you'd select the description that is in the lookup table.

Not that it matters to you, but I'm a bit disappointed that you switched to storing the text in your main table. What happens if you decide to change the description of something slightly? For example what if your first combo box needs to have "User IDs" as another option. Perhaps you'd decide that "Users" wasn't descriptive enough. So you'd want to change that to "User Names". If you'd left the FK in the main table, you'd simply go to the lookup table and change "Users" to "User Names". With the "meaningful data" store in the main table you'll have to go update every record that has "Users" in it.

Oh, BTW, I have had trouble making Access do what I want when I don't use Bound Column = 1. Can't remember what the trouble was. I just make it a general rule to always use 1.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old April 1st, 2005, 05:02 PM
Authorized User
 
Join Date: Mar 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Glad you had a nice vacation. I'm hurt that your are disappointed with my choice. But I still love you for solving my last niggly issue. I can see the wisdom of using a query. I have a copy of my database that I'm using to play with queries. I have a problem with the table relationships tho. The look up tables have a one to many relationship, from first to last. But the main table isn't really related to the lookup tables. I stuck a ChangeID autonumber field as the primary kay in the main table and have an indeterminate relationship (or one to one) with the ChangeID in the first lookup table, ChangeMadeTo. When I do a select query with the main table and the other tables, I end up the same record over and over or just 6 records. (ChangeMadeTo has 6 fields, is this due to the relationspip with the main table?) Anywho, I'm following your (and mmcdonal's) advice. You will probably see other posts from me on my query quest.

Many thanks!

Jen

 
Old April 2nd, 2005, 05:14 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

When you say ChangeMadeTo has 6 fields, do you mean it has 6 records? I'm guessing ChangeMadeTo has two fields: an ID field and a description field.

I'm guessing that your query shows two tables: the main table and the ChangeMadeTo table. I'm guessing that you don't have a line linking the two tables. So here's what you do to set up the query correctly...

Click on the ChangeID field in the main table and drag it over to the ID field of the ChangeMadeTo table. Depending on whether or not you have used Access Relationships to define how tables are linked, the link will be one-to-one or one-to-many. You should be sure to set the join (link) the way you want it for this case. Double-click on the line between the two tables. This opens the dialog for the join properties. Because you always want to show records from the main table and only those from ChangeMadeTo that match, make sure that option is selected. This will point the arrow from your main table to the ChangeMadeTo table.

When you run your query, you will get only one row for each of the main table.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Count in combo box(display results in text box) mboyisis Access 4 April 4th, 2008 07:08 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Unbound combo box / listbox problem VBM2 Pro Visual Basic 2005 3 March 13th, 2007 11:04 AM
filtering records on subform from an unbound combo jd_erd Access 3 February 12th, 2007 11:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.