How do I Modify a Combo List Box in a Many-to-Many Relational DB?

[thulot]

Hello, everyone, I am using Access 2013 and need a little bit of help. (I’m not really well-versed in databases.)


I’m not 100% sure how to edit and modify a combo box. I’ve figured out how to create a query that displays the list, but I’m unable to delete anything in it, add anything to it, or modify it. I’m obviously doing something wrong.


I’ve attached a copy of the database in .zip format to this message. The combo box in question is named DVD Prefix. Here’s a screen shot of the box.


After reading some documentation and watching a couple of You Tube videos, I am a little more confused. I’m sure this is something easy, but I’m not grasping it. Can anyone please point me in the right direction? Thank you. J. Danniel

[June7]

If you want to modify the combobox list, you must edit the source, then requery the combobox. If you want to add items to the list 'on-the-fly' during data entry, use its NotInList event. Review http://www.fontstuff.com/access/acctut20.htm

Instead of building a query for each color, suggest you build one query and use a dynamic parameter to filter records. Or code that applies filter to report when it opens. Review http://www.fontstuff.com/access/acctut20.htm

[orange]

Here is info on comboboxes from techonthenet.

[thulot]

If you want to modify the combobox list, you must edit the source, then requery the combobox. If you want to add items to the list 'on-the-fly' during data entry, use its NotInList event. Review http://www.fontstuff.com/access/acctut20.htm

Instead of building a query for each color, suggest you build one query and use a dynamic parameter to filter records. Or code that applies filter to report when it opens. Review http://www.fontstuff.com/access/acctut20.htm

Thanks for replying. I'm going to tweak the color queries. I don't need all of them. But I'm not 100% sure what you mean by editing the source. Can you elaborate further, please? I can add items on the fly, but I still haven't figured out how to edit the list. Thank you.

[June7]

I had assumed you have a table of DVDPrefix descriptions. But I now see you are pulling these values from tblMovies with a DISTINCT query.

What do you mean by 'edit the list'? What items do you not want to show in the list?

[thulot]

I had assumed you have a table of DVDPrefix descriptions. But I now see you are pulling these values from tblMovies with a DISTINCT query.

What do you mean by 'edit the list'? What items do you not want to show in the list?

I'm not really well-versed in database design. I managed to design this database with a LOT...a LOT...of help. I'd never be able to do it by myself.

Let me try to explain what I want to do. The list has the word NONE in it, and I want to delete that. I do not know how. I know how to add items on the fly, but I want to modify some of the items in the list.

For example, instead of having the letter B in the list, I want the word BLUE. Or instead of R, I want the word RED.

That is what I mean by editing the list. I want to delete something, I want to alter a few things. I hope that makes sense. I have no idea what a "DISTINCT" query is.

If it is easier to have a table of DVDPrefix descriptions, then I'd be interested in changing things. However, would that be too late? Would I have to re-enter a LOT of data into the database if I do that?

[moke123]

Your distinct query is qryDVDColor.

SELECT DISTINCT tblMovies.DVDColor
FROM tblMovies
ORDER BY tblMovies.[DVDColor];

The keyword Distinct means that your query will pull only one instance of each value in tblMovies.DVDColor.

You can create a lookup table with only the values you want. You would then change the rowsource of the combobox to something like

Select DVDColor from tblDvdColors orderby DVDColor

[davegri]

thulot-Movie Database 2018 davegri-v01.zip
Take a look at the attachment.
You were storing the actual text prefix in tblMovies.
I added a new table, tblDVDPrefix with the prefixes and a primary key.
tblMovies is now storing a foreign key pointing into tblPrefixes (I changed the dvdprefix field from short text to number in tblMovies).
On form Movies, if you right click on the dvd Prefix field you get an option 'Edit list items'. It will pop up a simple modal form to edit the codes in tblDVDPrefix.
With this setup, if you edit the combobox, say, to change W to White, EVERY record that was formerly W will now show WHITE. If you want to see
both W and WHITE, you will have to add a new code to do that (now showing both W and WHITE in the dropdown).

The query that was formerly the rowsource for combobox dvdprefix is no longer needed. tblDVDPrefix replaces it.

The new code module, basConversion, contains the code to convert the tblMovies dvdPrefix field from the alpha codes to the numeric key codes in tblDVDPrefix.
It can be deleted. I Left it there as an example.

[June7]

The combobox RowSource is an SQL statement that references query object named qryDVDPrefix. Look at the SQL statement for that query and you will see the DISTINCT keyword.

Could have just made the combobox RowSource statement the statement from that query object instead of building a query from a query.

Since the combobox RowSource list is based on data in tblMovies, you would have to fix the data in the table. However, as the combobox is currently set up, users can enter anything they want and the entries become items in the list. This is an aid for users to select items that have been previously entered but doesn't restrict their input.

If you want to restrict what is allowed for input then do what moke123 suggests (and what I had assumed you already had), build a lookup table. Use that as the source for the combobox RowSource.

You will then have to edit the data in existing tblMovies records to comply with the restricted list.

And now I see davegri has provided you a modified db to incorporate this suggestion. Enjoy!