Lookups - the finer points (1 Viewer)

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
I am trying to piece together the best practice for lookups and have read the basics but have some questions on the finer points of setting it up.

I have seen many times:
http://access.mvps.org/access/lookupfields.htm
and I am trying to avoid table-level lookups.

I have table with a list of user initials tblInitials-
ID, Initials, FullName

I want to use this in many tables so the person can record who did the action - for instance I have a table called tblActions -

ID, Action, ActionDate, ActionInitials
and I want the user to be able to select their initials when entering the action on a form.

I have read that the best way for novices is to use the combobox wizard as per the answer here:
https://stackoverflow.com/questions/16379232/look-up-vs-relationship-microsoft-access

The things I am unclear about are:
When creating the Actions table do I just make a field "ActionInitials" and set it to Data Type "Number"? Is there anything else I need to do? Any other options that are recommended for a lookup field that will be used in the combobox wizard?

After running the combobox wizard do I need to create a relationship between the tblInitials!ID and the tblActions!ActionInitials?
If I use the table level lookup this relationship is automatically created but if I use the combobox wizard it is not.

I checked out the example databases here:
https://www.access-programmers.co.uk/forums/showthread.php?t=207989
and they do have the relationships but I am not sure if they have been manually created and if they are necessary? What referential integrity is suggested for this kind of relationship?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:40
Joined
May 7, 2009
Messages
19,169
It's entirely up to you if you want table level or conbobox.
Many will discourage you to use the former.
Relationship is optional.
You can set the relation later on query, forms reports and vba.
 

sonic8

AWF VIP
Local time
Today, 09:40
Joined
Oct 27, 2015
Messages
998
After running the combobox wizard do I need to create a relationship between the tblInitials!ID and the tblActions!ActionInitials?
If I use the table level lookup this relationship is automatically created but if I use the combobox wizard it is not.
The ComboBox Wizard works on UI-Level (Forms). It does not affect tables and their relationships.
The Lookup Wizard works on table level. The configuration of UI-Level (visual) elements in both wizards is very similar. However, the Lookup Wizard can also create table level relationships but only will do so if you check Enable Data Integrity on the last page of the Lookup Wizard.



In my opinion you should always create real table (data) level relationships with referential data integrity enabled.



Relationship is optional.
You can set the relation later on query, forms reports and vba.
This is correct, but nevertheless a dangerous thing to say without further explanation.
Relationships (with Referential Integrity enabled) are an easy to use, yet very powerful, tool to protect the integrity of your data. Not using them bears a high risk of creating problems further down the line. So, while they are technically optional, logically they almost never are.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
and I am trying to avoid table-level lookups.
You don't need to "try". Just don't use them. They are a crutch for users who would never write code or create queries. The main reason that professionals would not use them is because they mask the actual contents of a column. When you open the table in DS view or create a query, it looks like the field contains "my company name" when in fact it contains the ID of the company which is 123. This causes you to assume that you can write code or criteria in a query that refers to "my company name" but you can't. Since no user EVER sees a raw table or query, YOU are the only person who is actually able to take advantage of seeing a customer name rather than an ID. And since YOU know how to write a query, you can just write a query that joins the main table to the lookup table and that allows you to see everything.

Regarding your specific question. I don't ever allow users to pick their name from a combo to indicate that they did something. If I want to track who did something, I create a log in form with a userID and password. Then I generally hid the login form if the login is successful and open the main menu. That way, whenever I want to track a user's actions I do it in the form's BeforeUpdate event.

Me.ChangedBy = Forms!frmLogin!UserID
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
Thanks for all the replies.

I am aware of the evils of table level lookups and this is my reason for the questions. The advantage of table lookups for novices is a nice wizard which sets everything up, including relationships. They pay for it later with queries etc - and I want to try to avoid this. The steps are not as clear for the 'correct' way though

So the steps I should follow -
Create table with the lookup target data ([ID], [Initials])
Create field in the table used to store the data ([ActionInitials] (data type 'number' - still unsure of other settings)
Add field to form using combobox wizard "lookingup" the target data
Manual create relationship in back end

I can't auto track user actions as sometimes the information is being entered by a data entry person only (from a form), not the person doing the action.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:40
Joined
Feb 28, 2001
Messages
26,996
I can't auto track user actions as sometimes the information is being entered by a data entry person only (from a form), not the person doing the action.

This is a philosophical issue, but based on decades (literally) with the U.S. Navy as a system admin, ...

You obviously don't care who did the action or you would have already added that information as another data item to be entered. But if you are going to audit anything, you should care about who actually touched the database, which is why the data entry person should log in.

Having said that, tracking user actions is easier than you think because if you have a domain-based login environment, you can use Environ("Username") to identify who is using your application.

Auditing isn't only for procedures checking. It is also for determining whom you should question when something goes coo-coo in the database.

Here is the reason you want to use a small table for lookup purposes. We had a thread today about someone wanting to change the options behind a combo box that wasn't table driven. The same issues would apply (though not exactly the same solution) for lookup fields. If you have a table, it becomes easy to add, change, or remove a lookup keyword and value. If it is NOT a table, maintenance of the lookup list is a lot harder.

In your list, you have a "lookup target data (ID)" comment. But the contents of that lookup table don't have to be limited to the two fields you named. If you have the initials, other fields in the same table could be first name, middle name, last name, name suffix, name prefix, phone, etc. (assuming you had need for any of that.) Just because you have a ton of data in a table doesn't mean you have to use it all for the lookup.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
The "person" table should contain more than ID and Initials. In every system I have ever used, Initials were remarkably duplicated.

tblPersons
PersonID (autonumber, primary key)
Initials
FirstName
LastName
ActiveFlg (default to Yes)
EmployeeNum (if you have this from a different system or maybe the person's LogIn)

Create a form to allow updates and adds. Deletes should never be allowed on this table. Set the ActiveFlg to No to prevent future use of the person on new records but you want to keep the history on existing records.

Then in your other table, you would add PersonID which would be the foreign key to tblPersons and you should create a relationship using the relationship window and enforce Referential Integrity.

When you build the combo, the RowSource will be a query that selects from tblPersons. To help people select the correct person, show Initials, FirstName and LastName at a minimum and include the ActiveFlg but you don't have to show it. You might also need EmployeeNum to resolve duplicates.

In the BeforeUpdate event of the combo, you need to check the ActiveFlg to prevent it from being used on new records if it is marked as inactive.
Code:
If Me.cboPersonID.Column(4) = False Then
    Msgbox "This person is inactive and may not be selected.",vbOKOnly
    Me.cboPersonID.Undo
    Cancel = True
    Exit Sub
End If

The .Column property of the combo is a zero based array so by my count the ActiveFlg would be the fifth column in the select clause of the RowSource. Since the first column is referenced as .Column(0) and the second as .Column(1), that makes the fifth = .Column(4)
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
I probably didn't explain it correctly - its not a database action that I am tracking but the actual work order. It's the initials of the person completing the job, not entering or touching the database.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
We understand but for auditing purposes, you might want to also keep the ID of the person doing the data entry. It is quite easy to select the wrong person from a combo.

Why wouldn't the person who completed the work order do his own data entry?
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
Why wouldn't the person who completed the work order do his own data entry?


Some jobs are done on paper in the field then entered by admin at the end of the day/week. Some jobs are done over the phone in the office and directly entered. The person doing the job is what needs to be recorded and it is not always the person entering the data.
 
Last edited:

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
The "person" table should contain more than ID and Initials. In every system I have ever used, Initials were remarkably duplicated.

tblPersons
PersonID (autonumber, primary key)
Initials
FirstName
LastName
ActiveFlg (default to Yes)
EmployeeNum (if you have this from a different system or maybe the person's LogIn)

Create a form to allow updates and adds. Deletes should never be allowed on this table. Set the ActiveFlg to No to prevent future use of the person on new records but you want to keep the history on existing records.

Then in your other table, you would add PersonID which would be the foreign key to tblPersons and you should create a relationship using the relationship window and enforce Referential Integrity.

When you build the combo, the RowSource will be a query that selects from tblPersons. To help people select the correct person, show Initials, FirstName and LastName at a minimum and include the ActiveFlg but you don't have to show it. You might also need EmployeeNum to resolve duplicates.

In the BeforeUpdate event of the combo, you need to check the ActiveFlg to prevent it from being used on new records if it is marked as inactive.
Code:
If Me.cboPersonID.Column(4) = False Then
    Msgbox "This person is inactive and may not be selected.",vbOKOnly
    Me.cboPersonID.Undo
    Cancel = True
    Exit Sub
 End If
The .Column property of the combo is a zero based array so by my count the ActiveFlg would be the fifth column in the select clause of the RowSource. Since the first column is referenced as .Column(0) and the second as .Column(1), that makes the fifth = .Column(4)


From the first post - I do have a "Name" field to identify the person's full name. I just omitted this from my follow up comment to keep it simple.


The Active flag is a great idea - I'm going to use that in another part of the database. What about the case of when there is a large turnover of staff and you don't want the list to grow and grow? Can you hide those inactive instead so they are not even an option as they can't be removed, I assume, from the lookup table as that would break the relationships.


(My quick guess would be to do this in the cbo query and exclude inactive - am I on the right track?)
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
I could filter the active through the query - thanks for the great idea.

When I looked in the query at the back end I did notice that Access autocreates a hidden copy of the field which you select to sort and for some reason doesn't just use the sort of the existing non-hidden field.

Example (it autocreated [Initials] even though this already exists):
query.png


*This query was created using the cbo wizard - then I added the active filter

 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:40
Joined
Feb 28, 2001
Messages
26,996
That is actually an "artifact" of the way the Query grid responds in some cases. For example, if you had SQL such as

Code:
SELECT ID, INITIALS FROM tblINITIALS WHERE ACTIVE=TRUE ORDER BY INITIALS ;

In essence, every time a field name appears in a query it will appear somewhere in the query grid. In this case, INITIALS was used twice - once in the SELECT field list, once in the ORDER BY field list. And ACTIVE wasn't in the SELECT field list but it had to be shown anyway because it was in the WHERE clause.

Therefore I wouldn't worry about that "phantom" appearance. What appears in the grid rarely matters that much. What appears in the SQL? THAT is usually what is most important.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
If you use the criteria to select only Active users, rows where inactive users were entered will go "blank". If the list gets too long and you prefer to not have to scroll through the entire list, sort by the ActiveFlg. The Yes value is -1 so it will sort ahead of the No value which is 0. As the second sort field, use the Initials or name.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Feb 19, 2013
Messages
16,553
there are many ways to do this, some based on personal preference, some on the requirements of the business.

So you might consider using a date field rather than a flag - the reason is that you know an employee is leaving at the end of the month, so you can enter a date now (i.e. first of next month) and the employee will 'automatically' be excluded from the beginning on next month. No need to be first in on the day to tick the flag box.

query to select active employees would be something like

SELECT * FROM tblEmployees WHERE nz(LeaveDate,Date()+1) >Date()

or

SELECT * FROM tblEmployees WHERE LeaveDate is null or LeaveDate>Date()
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
If you use the criteria to select only Active users, rows where inactive users were entered will go "blank". If the list gets too long and you prefer to not have to scroll through the entire list, sort by the ActiveFlg. The Yes value is -1 so it will sort ahead of the No value which is 0. As the second sort field, use the Initials or name.


I didn't find this to be the case - if I set a the Where criteria to be True for active in the cbo query it only renders with those rows, it does not show blanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
The RowSource shows only the people selected. You need to navigate to a record where the person assigned is not active. Their Initials will be blank. So to test this. Create a record and assign a person to it. Close the form and go to the form that manages users. Make that user inactive. Go back to the original form. Do you still see the name of the inactive person?
 

Leyton01

Registered User.
Local time
Today, 18:40
Joined
Jan 10, 2018
Messages
80
OK - I hadn't considered the data view post making someone inactive.


I have added the suggested msgbox warning and I have sorted first by inactive.
I have also added a spacer "--" and everything below and including the spacer is inactive. This separates up the list quite nicely.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
I hate to state the obvious but just in case, make sure you can't select the spacer.
 

Users who are viewing this thread

Top Bottom