Solved

Access question regarding lookup fields and entering data directly into tables.

Posted on 2013-05-21
12
477 Views
Last Modified: 2013-05-21
I'm new to Access, and have been understanding the program fairly well.  But I've run into a dilemma.  I'm using queries to populate combo boxes to make entry into datasheet view faster and more consistent (I'm not currently using forms to enter the data).  So in table design view, I'm setting the type for the field to short text, creating a query to another table which has the categories I want to select from, and all is well.  

But I'm reading that I shouldn't be doing it this way.  But the alternative is entering info via primary key ID in some other table and of course there's no way to tell what numbers are associated with employee names, company names etc.  Furthermore, some of my lookups have no started returning numbers (primary key ID's i'm guessing, since something has failed with the look up) instead of the associated information.

So how do I resolve the phantom numbers where readable info once was issue, and also, how do i circumvent this problem without using a lot of forms.  This DB is just for me and one other person, both of which are IT staff so making a lot of pretty forms seems like an unnecessary project.  Thanks!
0
Comment
Question by:LB1234
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 50 total points
Comment Utility
You may have some orphaned records. That would explain why you have numbers in some "combo box" field rows and the associated value in others. You should define relationships between these two tables, link up the primary key in the categories table with the (foreign) key in your main table and specify "enforce referential integrity".

You don't need forms, but they are useful for data validation and as a place to put buttons, charts, list boxes, etc...
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Wow, I need to proofread better.  The paragraph above is a disaster.  Anyway, I have a department and employee table, and I've created a relationship between them (one department to many employees).  However the department field is still showing numbers, as I would expect it had to since it has to be the same data type as the autonumbering ID in department.  So then, how do I display text in a field that's assigned a numerical datatype?  I'm not getting this. THanks for your help.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 450 total points
Comment Utility
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Als, watched the whole thing, thanks.  One of the first things she does is break the relationship between the two tables in order to resolve the mismatch data type issues between the primary key and the foreign key in the lookup field.  But what of the relationship?  Are table relationships and lookup fields mutually exclusive?
0
 
LVL 39

Accepted Solution

by:
als315 earned 450 total points
Comment Utility
No, but you can't change type of field when it is used in relationships. You can add this relationship again. It is not real change of field's type, it is master, where proper field type will be selected. You can do all this work manually. Look at picture, where all changed fields are marked
lookup
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Thanks so much!  So she just forgot to remake the relationship at the end of the video?  I assumed since she didn't recreate the relationship in the vid, it just stayed undone.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Here is the deal...

You create a combobox to display the list of values.
The combobox will store the numeric value, but it will also "display" the text value.

Not sure why you are not using a form, ...then this is all easier (IMHO)


See this very simple example of how this normally works.

Study it carefully and see if you can understand all the design aspects and control properties
Database46.mdb
1
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Hi Boag, I'm not using tables because I've already got a bunch of data entered into various fields in existing databases.  Don't want to re-type all that stuff into forms.  I think it'd be much easier to various queries create new tables, append, delete, etc in bulk.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
Comment Utility
Lookup fields behave unpredictably at times, and most developers who have used Access for many years tend to shun them (see article here: http://access.mvps.org/access/lookupfields.htm)

Combo boxes are a user interface tool and, in my opinion, the correct place for a user to enter and edit data is in forms, not tables.  It is very easy to create simple, workable forms using the wizard and they really don't have to be "pretty" unless you want to put in the effort.  Forms offer all sorts of advantages over tables for data entry and viewing - not least because you can view data from multiple related tables at the same time - for example, you could have a form showing all the fields from your departments table, with a subform listing all the employees in that department.

Anyway, I shall finish preaching now and get back to your combo box problem  :-)

First, as Peter has said, you should set up a relationship between your two tables which enforces referential integrity (RI).  This will ensure that you can never have an employee record with a DepartmentID value which does not exist in the Departments table.

Then you should ensure that all the properties of your combo box are correctly set.  A combo box typically has a RowSource with two fields - the stored value and the displayed value.  In your case, the RowSource should be something like this:

SELECT DepartmentID, DepartmentName FROM Departments ORDER BY DepartmentName;

The ColumnCount property should be 2, corresponding to the number of fields in the RowSource.

The BoundColumn is the column containing the value to be stored - in this case 1 (DepartmentID).

Finally, set the ColumnWidths.  This property has a text value in the form "a;b;c", where a is the width of the first column, and b the second, etc.  If one or more of these values is omitted (for example, "a;;c") then the corresponding column(s) get an equal share of the remaining space.  

The displayed column (the one showing when the combo box is not dropped down) is the first column with a non-zero width.  Putting this together, with a two-column combo box, if we simply set ColumnWidths to "0" then the first column is hidden (zero width) and the second column (unspecified width) occupies the entire combo box.  

Setting these properties correctly should ensure that you always see the DepartmentName, not the DepartmentID.

If you prefer to stick with the table for data entry then these properties can be set in the lookup control in design view, but I highly recommend you use a form.

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Graham, thanks so much for the time and effort involved in writing that!  The issue that I've already got hundreds upon hundreds of records already.  Don't want to have to hand enter them using forms.
0
 
LVL 21
Comment Utility
LB1234,

I regularly remove look ups from fields in tables. There is no data lost.  I create a form in continuous view to enter data. It looks a lot like the table gird only better. Now you get the full power of a form. For example, it allows me to add use the "Not In List" to let user add data to the look up list. Something that people were asking about int he comment on youtube.

I should thank the Access Dev team for this "feature". It has been a great source of revenue.
0
 
LVL 12

Expert Comment

by:pdebaets
Comment Utility
You don't have to re-enter data into forms. The data is stored in your tables. Forms provide a view to the data already in your tables. Forms also allow you to add (update, view, delete) records in your tables.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now