Listbox with just 3 or 4 items I would like to easily add/remove & saved. ValueList would be nice, perhaps avoid tables & subforms?

[bignose2]

Hi,
I cannot work out the best/simpliest way to do this.

I would like a listbox on my form, So I can easily add or remove items directly by clicking on the listbox for ease & clarity.

value list is almost perfect but v.occasionally might want to change those items and that data is not saved when the form is closed.
Unless there is a way?

Its actually a staff calendar table and I want to put into different catagories, times off or working based on what is in those list boxes. Goes through the calendar and counts based on e.g. below.

Sick
Appointment
Late

I will actually have three have similar functioning list boxes so want to keep simple as possible.

Having the source as tables seems OTT although ..
I guess a simple text box to add to the table reflected in the LB, then select & a button to delete.

or is there a way to perminantly add/remove from value list.

Accde in runtime.

thanks I/A

 

[arnelgp]

or is there a way to perminantly add/remove from value list.

you can Save the list to a textfile and when the form opens read the content of the textfile to your listbox.
if the textfile does not exists, just load the defaults they would have.

 

[arnelgp]

here is a crude demo. open Form1.

 

[bignose2]

Many thanks,

A good idea & guess can make very fail safe, just not too keen on having a separate file, no reason I guess but think perhaps table as source is the most natural. I was hoping an integral way to adjust the value list & store more easily than having a number of new tables with just a few items in them.

thanks again, especially for the example DB

 

[Gasman]

Many thanks,

A good idea & guess can make very fail safe, just not too keen on having a separate file, no reason I guess but think perhaps table as source is the most natural. I was hoping an integral way to adjust the value list & store more easily than having a number of new tables with just a few items in them.

thanks again, especially for the example DB

I used to have a lookup table that would hold various information, rather than a table for each category.

 

[MajP]

To go into more detail what @Gasman suggets. It is common to have a single table for lookups. I build mine like this.

tblLookups
-- LookupID
-- LookupCategory
-- LookupText
--LookupSort

LookupID	LookupCategory	LookupText	LookupSort
1	Absence Code	Sick	3
2	Absence Code	Appointment	2
3	Absence Code	Late	1
4	Shirt Size	Small	2
5	Shirt Size	Medium	3
6	Shirt Size	Large	4
7	Shirt Size	Xtra Large	5
8	Shirt Size	Xtra Small	1
9	Task Status	New	1
10	Task Status	In Process	2
11	Task Status	Late	3
12	Task Status	Complete	4

I often have many categories in a large db with 100s of choices. Then your pulldowns always follow the same sql

Select LookupText from tblLookups where LookupCategory = 'yourcategoryhere' order by LookupSort

Single popup form to add / edit / delete. The sort order allows you to sort in a logical order and not force you to enter in order. Example (Xtra Small, Small, ... Xtra Larger) but I forgot to put xtra small first.

On my combos I have a edit button that pops open the list to the correct category and defaults the value to the correct category so I can easily add/edit/delete.

 

[GPGeorge]

Spoiler:

Hi,
I cannot work out the best/simpliest way to do this.

I would like a listbox on my form, So I can easily add or remove items directly by clicking on the listbox for ease & clarity.

value list is almost perfect but v.occasionally might want to change those items and that data is not saved when the form is closed.
Unless there is a way?

Its actually a staff calendar table and I want to put into different catagories, times off or working based on what is in those list boxes. Goes through the calendar and counts based on e.g. below.

Sick
Appointment
Late

I will actually have three have similar functioning list boxes so want to keep simple as possible.

Having the source as tables seems OTT although ..

Spoiler:

I guess a simple text box to add to the table reflected in the LB, then select & a button to delete.

or is there a way to perminantly add/remove from value list.

Accde in runtime.

thanks I/A

Having lookup tables for lookup values has been standard practice since Access 1.0 -- as well as in every other relational database I know of. Nothing unusual about it at all.

I long ago gave up any hope of implementing clever alternatives to most of the native Access functionality, but that's partly because I got used to working for clients who preferred to pay for my time in creating solutions that met their needs, rather than inventing new methods to address already solved problems.

 

[Uncle Gizmo]

Having lookup tables for lookup values has been standard practice

It is often possible to put the lookup information in one table, with a key field which tells you which combo box that particular set of data has to go in....

Then you can have one single form for managing all of your lookups...

 

[MajP]

This demo is based on an Issue Tracker database, but I reuse the same table, edit form, and basic idea in most applications I build.
Here I have lots of comboboxes that pull from a single table.
You can add / edit / delete on the fly. You can do this with as many listboxes or comboboxes you want. It can be used anywhere in the application.
Requires very little code. You can sort lists Alphabetically or provide a sort order. I also can display an abbreviation or full text (S, M, L or Small, Medium Large).

 

[bignose2]

To go into more detail what @Gasman suggets. It is common to have a single table for lookups. I build mine like this.

tblLookups
-- LookupID
-- LookupCategory
-- LookupText
--LookupSort

LookupID	LookupCategory	LookupText	LookupSort
1	Absence Code	Sick	3
2	Absence Code	Appointment	2
3	Absence Code	Late	1
4	Shirt Size	Small	2
5	Shirt Size	Medium	3
6	Shirt Size	Large	4
7	Shirt Size	Xtra Large	5
8	Shirt Size	Xtra Small	1
9	Task Status	New	1
10	Task Status	In Process	2
11	Task Status	Late	3
12	Task Status	Complete	4

I often have many categories in a large db with 100s of choices. Then your pulldowns always follow the same sql

Select LookupText from tblLookups where LookupCategory = 'yourcategoryhere' order by LookupSort

Single popup form to add / edit / delete. The sort order allows you to sort in a logical order and not force you to enter in order. Example (Xtra Small, Small, ... Xtra Larger) but I forgot to put xtra small first.

On my combos I have a edit button that pops open the list to the correct category and defaults the value to the correct category so I can easily add/edit/delete.

Many thanks, this was actually so simple now you mention it, I was over complicating (as usual)

thanks again, implemented as above & works so well & could not have been easier.

 

[Pat Hartman]

Having lookup tables for lookup values has been standard practice since Access 1.0 -- as well as in every other relational database I know of. Nothing unusual about it at all.

It actually predates Access by decades. I was doing this in the days of the mainframe long before Access was invented. I've attached a link to the current version of the software as it is used in all of my current applications.

There are two tables (most people are more comfortable with a table of tables and a table of table items than a self referencing table so 2 rather than 1). Two forms + subforms. Two reports. To simplify using the tables, I create a new query each time I create a new "table". That query is used as the RowSource for any combo that uses a simple lookup.

Once you decide to adopt this method, you never have to worry about lookup tables ever again. You ALWAYS use the mini-app whenever you have a simple lookup table regardless of how many items in the table or whether or not additions/changes are likely over time. Deletes are never allowed.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[GPGeorge]

It is often possible to put the lookup information in one table, with a key field which tells you which combo box that particular set of data has to go in....

Then you can have one single form for managing all of your lookups...

The point I was pressing is that it should be table driven, not ad hoc inventions like writing out to an external file. But, yes, many people do use MUCK tables.