Solved

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

Posted on 2013-05-21
12
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 50 total points
ID: 39185357
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
ID: 39185378
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 40

Assisted Solution

by:als315
als315 earned 450 total points
ID: 39185401
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:LB1234
ID: 39185427
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 40

Accepted Solution

by:
als315 earned 450 total points
ID: 39185452
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
ID: 39185461
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39185545
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
ID: 39185549
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
ID: 39185567
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
ID: 39185579
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
ID: 39185710
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
ID: 39185712
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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