Access Database - problems creating combo/list box

jasmin10

Not sure if any one can help.

I know how to create a combo/list box but it's not doing what I want. I'm sure it is something fairly straight forward but I just can't seem to get it.

I have a form which is bascially a list of companies and their addresses. Next to the address columns and lots of others I have created a column called Business Type. so for example: I can type in Council, School, retailers, solicitors etc. But to save me keep typing the same thing in I wanted a drop down box so I can jsut click. I have tried various ways to try and get it to work and the outcome has been either it puts everything as a retailer or I can get a list with everything in but duplicated list entries , so for instance 4 banks, 5 schools etc. The only way I have had success and that was by selecting the thing myself and manually typing in a few which gave me the drop down list I needed but I found that when I wanted to add one that wan't there I couldnt. How do I add to the list? or is there another way.

I can't imagine what I'm doing.

Any help as usual gratefully received

x

Chippy_Minton

My understanding is that you want a listbox containing unique values.

Either store the business types in a separate table and query that, or create a query which filters unique business types from the main table - see http://support.microsoft.com/kb/292634. Then set the listbox's row source property to the query. You might need some code placed in one of the event handlers to repopulate the listbox, using lstBusinessTypes.Requery.

alanclose

When you created the table and added the field did you specify that field as a list. If so you just enter the values you want that list to display.

jasmin10

No Alan, I don't think I did. All I did was create the box in the form design which then showed through when I went into table view so I started inputting in the table view.

alanclose

I would edit the original table and add the field in and make it a lookup wizard under type, it should ask you if you want to look up a table or type the values you want. then when you are using a form view just add the new field in.

when I have lots of lists that will be updated quite a lot I usually create an new table and add details to that and create a a box and link the data source to the table. Then when I am creating a user interface for the database I have a maintenance section which is a form with shortcuts to the lists so people can add to the options easily. If you know all the options you want though creating a list in the original table is the best way

jasmin10

I have done what you suggested and created another table which I can easily add to.

One question though. I have tried to sorted this new table so that when I click the drop down box I was hoping it would list the business types alphabetically so I didn't have to scroll all the way through but it didn't work. Any ideas on this one.

alanclose

In the form you want to add the list to.

create list box - it will ask you if you want to look up a table, type the values you want or find a record.

You want to look up the table you created. When you pick that and choose the table you want and add the field in it should ask you if you want to sort . Pick your field in the box and it should sort it for you

Money_Saver

jasmin10 wrote: »

I have done what you suggested and created another table which I can easily add to.

One question though. I have tried to sorted this new table so that when I click the drop down box I was hoping it would list the business types alphabetically so I didn't have to scroll all the way through but it didn't work. Any ideas on this one.

Either just type the first charater ie c or f and it should go to that part or right click on the field in the table and enter the sort range, failing that create a query and save the query with it already sorted you need to pull down something in the query (long time since i used access)!

HTH