Solved How to add more data in a drop down list box in form

[hrdpgajjar]

Hi there,
I've created a form with a list box which contains all my dealer names.. I've set the list box property to locked to list box value only and able to edit list box items manually.

Now my dealer list goes very long and after some count i am not able to edit or add more dealers to my list box.


Kindly guide me how can i get rid of this problem.


Thanks

 

[ebs17]

What is the RowSouce type?
A value list is delimited, a table/query is not.
Data in a table is also easier to maintain.

 

[The_Doc_Man]

I'm with Eberhard on this one. A value list property is limited in length. You can decide for yourself by examining the "full" list box to see what is in it, but you will probably be limited to 255 characters for that property. If you convert the list to a table and fill the list box by a .RowSource query, you become limited by the maximum number of rows in a table, which in turn depends on what else is in the database - but you can easily get a few thousand entries in a list box. Oh, they become difficult as heck to manage when it gets that big - but it works. My personal biggest list box was over 1500 entries long.

 

[Ranman256]

the combo should be bound to a table/query. Do NOT use hardcoded value list
add items by entering them into the table
refresh the combo if it was left open to get the new values.

 

[MajP]

I assume you are referring to a Combobox and not a Listbox. Comboboxes have Limit to List property, not listboxes.
1. Either way the names should be in a table. The combo or listbox bound to a query based on the dealer table.
2. Build a form for entering new dealier information
3. You can use the Not in List event, but I find that a pain and very limiting. I would simply put a little command button next to the list or combobox. Click on that button to pull up your edit form open it ACDIALOG

docmd.OpenForm "PopUpAddEditDealerFormName",,,,ACDIALOG
me.ComboOrListBoxName.requery

 

[Pat Hartman]

Most applications have a number of simple lookup lists. This isn't actually one. Along with Dealer name, don't you also need an address and contact info?

 

[hrdpgajjar]

What is the RowSouce type?
A value list is delimited, a table/query is not.
Data in a table is also easier to maintain.

Sorry for the late reply. But my RowSource type is Value List

 

[ebs17]

As you have probably read and realized by now, the limit for the value list is narrower than for the query. So what's stopping you from changing that?

Besides, it is much easier to maintain a table as the basis of the query. Tables are native elements in a database.

 

[hrdpgajjar]

As you have probably read and realized by now, the limit for the value list is narrower than for the query. So what's stopping you from changing that?

Besides, it is much easier to maintain a table as the basis of the query. Tables are native elements in a database.

I have only one table with all details and dealer name is one of the columns in my table. Now how can i've tried to create a separate table for dealer name only but i want to store dealer name in my table too. i am confused how can i do that as i am not a pro in access. Kindly Help me on this ... Thanks

 

[MajP]

You have a dealers table with a record for every dealer and a field that includes the dealer name. There is no need to create another table and that would be a bad idea. You can simply make a query that only selects the name column and sort by the name column. See demo

 

[The_Doc_Man]

I have only one table with all details and dealer name is one of the columns in my table. Now how can i've tried to create a separate table for dealer name only but i want to store dealer name in my table too. i am confused how can i do that as i am not a pro in access. Kindly Help me on this ... Thanks

It sounds like you need to study a bit on queries. They are the absolute work-horses of Access. MajP suggested it first, but I'll explain it differently just so you get more than one viewpoint on the way to approach this problem.

If you have a table of dealers, addresses, and other dealer info, and all you really want is dealers, you just make a SELECT DISTINCT query and use it as the .RowSource of your combo or list box. If you use it in a combo, the .LimitToList allows you to run code asking you for the info to update the table, which you can do invisibly. Then, you .Requery the combo and select the row that you just added.

This whole thing works because what forms and reports and selection-oriented controls all REALLY want is called a recordset. You can get recordsets from tables - but you can get tailored recordsets from queries. Queries can present lists of things for you with multiple fields where needed, and can reformat fields for presentation purposes. They can impose order where order didn't exist. They can filter out unwanted data before presentation. All in all, queries are your friends. There are a FEW things you shouldn't do with queries - but there are a lot MORE things you can and should do with them.

 

[Pat Hartman]

The limit to list property will allow you to specify a form to show when you want to add a new item to the list. The form is a standard form and should include all the necessary validation to ensure that the entry is valid and not a duplicate.

 

[hrdpgajjar]

The limit to list property will allow you to specify a form to show when you want to add a new item to the list. The form is a standard form and should include all the necessary validation to ensure that the entry is valid and not a duplicate.

does it stores unlimited data or i need to create a separate table to store data ?

 

[Pat Hartman]

I have an entire mini-app that I add to all new applications that is used to manage simple lookup tables so I don't create new tables and forms for each lookup I have to manage. However, many people do. If you want the mini-app, it is in the database section where you can download it.

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

Whenever you have more than a couple of items in a combo or listbox OR you need to allow users to add/change/delete items from the list, you should use a table. Otherwise, you have no way of sorting the list and you have no way of preventing users from deleting or modifying items in the list. At least with a table, you can use a form that controls what the user can do with existing items in the list. They should NEVER, EVER be able to delete or rename existing items. Renaming is allowed in the mini-app since it isn't the text that is stored, it is the uniqueID. Changing the text doesn't change the ID so no existing record ever gets changes if you change the text. You see the new text when you look at the record but the ID that is stored is still the original value.

 

[hrdpgajjar]

It sounds like you need to study a bit on queries. They are the absolute work-horses of Access. MajP suggested it first, but I'll explain it differently just so you get more than one viewpoint on the way to approach this problem.

If you have a table of dealers, addresses, and other dealer info, and all you really want is dealers, you just make a SELECT DISTINCT query and use it as the .RowSource of your combo or list box. If you use it in a combo, the .LimitToList allows you to run code asking you for the info to update the table, which you can do invisibly. Then, you .Requery the combo and select the row that you just added.

This whole thing works because what forms and reports and selection-oriented controls all REALLY want is called a recordset. You can get recordsets from tables - but you can get tailored recordsets from queries. Queries can present lists of things for you with multiple fields where needed, and can reformat fields for presentation purposes. They can impose order where order didn't exist. They can filter out unwanted data before presentation. All in all, queries are your friends. There are a FEW things you shouldn't do with queries - but there are a lot MORE things you can and should do with them.

Sorry for the late reply, but it worth it. Finally I've learned a lot about applying table/query in row source in combo box and my problem solved finally . Thanks for the great suggestion.