Solved

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

Posted on 2013-05-21
12
484 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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