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

 

[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.

 

[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.