[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-05-21
12
Medium Priority
?
495 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 200 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 1800 total points
ID: 39185401
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1800 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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