Solved Lookup Wizard

[sctb0825]

I am using the lookup wizard in a form. What I need is for the user to be able to type a value that is not in the list and have it automatically added to the lookup.

example would be, I have a lookup for States, the list only has the 50 US States. The user needs to enter Alberta which is not in list. How can I have a lookup that can be added to by data entry?

 

[theDBguy]

You might try using the Not In List event to use code to add the new item or create a data entry form and select it in the List Items Edit Form dropdown of the Combobox.

 

[June7]

Common topic. Use combobox NotInList event. https://blueclawdatabase.com/notinlist-event-code/

Could just go ahead and add all Canada territories and provinces.

You are using a table as source for list?

 

[sctb0825]

I am using a table as the source, I just used States as an example but the items in the list are not states but items so the values would be unknown and not something I can pre populate. What I have in the table is just a starting point and the most common.

should I use a combo box instead?

 

[June7]

This essentially is a combobox. When you build a form bound to table and drag field from field list, it will automatically create combobox.

I never use the wizard for lookup field in table. Strongly advise against lookup fields at all. I generally don't use any design wizards because they do things I don't like. I manually build combobox on form.

 

[oleronesoftwares]

https://docs.microsoft.com/en-us/office/vba/api/access.combobox.notinlist

This article explains how to go about it.

 

[Gasman]

What you need to be careful of is if I enter Ablerta ?

 

[jdraw]

Here's a video on Edit List Item.

 

[CJ_London]

depend on the actual situation but you can set a combo rowsource bound to a text field called States on your form recordsource called say tblAddresses as follows

SELECT DISTINCT States FROM tblAddresses ORDER BY States

and set the control 'limit to list' property to no

advantage is no lookup table required
disadvantages - no initial population and if you need to correct a spelling, you need to do it for all records

I use it in things like accounting apps where I have transaction descriptions with small variations so I can select a general description and append a bit more information. I accept it is not really normalised but does mean that I can provide users with a simpler reporting base

 

[Pat Hartman]

Lookup values should be kept in tables and accessed via combos on forms, NOT lookups defined on a field in a table. Using the not in list event gives you some control over new data because you can do at least minimal validation before you save the new value.

Gasman's question is one of the primary reasons I don't like to allow users to add items on the fly. The point of the combo is to make the data consistent and to make entry simple. If you are just going to allow people to add typos on the fly, you don't accomplish the primary purpose.

I get that there might be some situation where you want to allow every user to add items but to add them without validation is not wise and if you don't store the values in a table, you would be forced to use a table level lookup otherwise new items would only be seen by the user that entered the value in a multi-user environment and they would be lost when the FE is updated.

 

[sctb0825]

Thanks all you have been very helpful, read all the links but Pat's comment cleared up all the confusion.

 

[June7]

In most cases a lookup table is advised. However, I do have a db that uses CJ's concept. Control could be a textbox but I made it a combobox to assist users with input because even though free typing input is allowed, inputs can be very similar. It's an oddball db that breaks many 'rules' but the design facilitates data entry and production of reports of laboratory test data which is really its primary goal. Very little statistical analysis is performed.

 

[Pat Hartman]

Yes, you can select distinct values from the data in the table but that does nothing to prevent typos and other spelling variations. So, it facilitates data entry by providing a pick list but it does nothing to improve validity/consistency of the data. Show me an application that does that and I'll show you many variations in codes. If you are not doing any analysis on the various discrete values, it won't matter but if you are, I would not recommend this method at all.

 

[sctb0825]

Thanks Pat, not sorting this particular data just providing most common defaults but allowing Agent to add additional values. They will have some rules to follow but it's not important the Format although I do have some format rules set.

 

[Pat Hartman]

If you don't sort them, the combo will not work correctly.