Adding data from a drop down list without editing the list

[Sialia]

I am a new Access user, and new to these boards. Apologies if this has been asked before.

I'm building a photo indexing database.

I've set up a many to many relationship so that each "photoID" can have multiple "Keywords," and each Keyword can be applied to multiple photos.

So far, so good, this works fine.

I've got one table for the PhotoIDs, and the Keywords in another, and a third table that links them to each other ("junctionKeywordPhotoID.")

The problem occurs now that I am building a form to display the linked data.

I want to be able to have a drop down list show the user the available Keywords (it's a controlled vocabulary) so the user can select a term and apply it to the photoID.

This is not working so well.

The drop down list is not happening at all. For the moment, I've set that problem aside and am working on manual entry.

I can get data which has been previously manually entered to junctionKeywordPhotoID to display correctly in the form, but I can't seem to push data to it.

Worse, I find that in trying to add data to the junctionKeywordphotoID table, I'm actually editing the Keywords table.

I don't want the Keyword list to be editable at all.

Is there a way to lock a table so that it can no longer be edited?
Does anyone have suggestions about what I might be doing wrong in trying to to set up the form?

 

[dcx693]

Is junctionKeywordPhotoID the recordsource for your form?

And what is the problem with the drop-down list? It should be a bound control to one of the fields in the junctionKeywordPhotoID table.

 

[Sialia]

Is junctionKeywordPhotoID the recordsource for your form?

And what is the problem with the drop-down list? It should be a bound control to one of the fields in the junctionKeywordPhotoID table.

OKay. . . go easy with me here . . .I'll come up to speed on the vocabulary in a bit, but I'm still new at this.

Each keyword has an autonumber ID# ("KeywordID").
Each photo has a unique ID# ("PhotoID").
junctionKeywordPhotoID has three fields: an id# for the junction, the keywordID, and the photoID.

So the subform in my "Photo Data" form that shows the keywords which have been applied to the photo is connected to the junctionKeywordPhotoID table. (If I connect it to the Keyword table, all I get is the whole list of Keywords, right?) But I've set the subform to display only the "Keyword" field from the Keyword table, becasue my user doesn't want to see an ID number, he wants to read the word "truck" (or whatever the chosen keyword is).

I'm not sure I set it up in just the right way, but it seems to display the right sort of data, as long as I've pre-entered the data in the junctionKeywordPhotoID table.

I haven't set up a drop down list at all, because I'm not quite sure how to do that so that I'd be seeing the "Keywords" and not the "KeywordIDs."

And then, how do I point to the "Keyword" list for the drop down, and have the data chosen from it entered to the junctionKeywordPhotoID table?

Maybe I need to be thinking about this form a differnet way. I'm open to suggestions about any other means for
1. letting the user see what keywords are available
2. letting the user select a few keywords and apply them to the photo
3. letting the user see what keywords have been applied to a photo which has already been cataloged

Eventually, I will need to be able to set something up so the user could search for all records which have been cataloged by a certain keyword . . .but let's let that pass for the moment. I'm not ready for that yet.

 

[Sialia]

Is junctionKeywordPhotoID the recordsource for your form?

And what is the problem with the drop-down list? It should be a bound control to one of the fields in the junctionKeywordPhotoID table.

Ok, picking away at the parts of the questions I understand: when I tried to create the drop down list, I did try to make it a bound control to a field in the "Keywords" table. (Because I didn't want the values to be the KeywordID numbers, but rather the actual Keywords.) So the problem, perhaps, is that the bound control is not tied to a field in in the junctionKeywordPhotoID table.

But if I put the "Keyword" field over in that table instead of in a table by itself, will I be able to create the many-to-many that I need?

 

[WayneRyan]

Sialia,

You can have a multi-column combo-box that selects the
key and the keyword from your table. You can bind that
value to the first column, but set their display widths to:

0"; 1"

That way you can work with the ID, and they can see
only the name.

Wayne

 

[Sialia]

Ahhhhhh . . . I see. Ok, I think that makes sense.

I will try that.

Many thanks!

 

[MaryAnn Mattis]

I have initiated an important inventory of A/V storage of past, current and future mediums, audio cassette, VHS, movie film, etc. to work as indexing Access database. There are five large tables that total over 35,000 collection items. I need to use the drop down lookup in many columns in all the tables with relations for reports and status. Since, I am at the design stage, have about 500 items done, do not want to lose that data by making a new column. I need to edit the drop down lists which gives me error; there is a column with the same name. How do I edit or add more selections or do I have to input manually all the changes? I have been an Access user, but new to these boards. I am sorry if this has been asked before. However, only reference is the book and it only explains the lookup wizard to set up the drop down lists, nothing to change or add data to the table lists.
I want to be able to have a drop down list show the selected size, 60 Min, or 90 Min, or 120 Min Tape, for medium VHS, Movie film, Audio Cassette, CD, DVD. Then need to be able add any new types, such as 16MM. 8MM, etc.