combo box value list

[thewabit]

I have a combo box ("severity")that uses a value list as it's row source type. The
options in it are 1, 2, 3, or 4. I want have one of these items to
automatically populate this combobox depending on what is selected in a box
called "Comments".

The "Comments" combobox is based on a query with a SQL of: SELECT tblComments.
Comments, tblComments.CmntAreaCode, tblComments.CommentID FROM tblComments
WHERE (((tblComments.CmntAreaCode)=Forms!frmObservations !frmLO_Details.Form!
CmntAreaCode)) ORDER BY tblComments.CmntAreaCode;

My tblcomments looks like this:


commentID comment otherstuff Severity
1................blahblah...................2
2.................blahblah..................1
3.................blahblah..................3

[MAF4Fam6]

Just to ensure I am understanding correctly:
Are you trying to use a Combo Box to dynamically update the value (not the group of values) of another field called "Severity" which is also a Combo Box?
If I'm reading this correctly, then...I am not quite sure I understand, why the Severity field would be set as a Combo Box.
A Combo Box is mainly used to make a selection from a drop menu list, thus ensuring data integrity of a particular field or group of fields (not all inclusive).
Was the original intent a conditional intent perhaps?
Thanks.

-RC

[thewabit]

Thanks for the response.

I understand your question. The "Severity combobox has a row source tyoe of value list and has only numbers 1, 2, 3, and 4. I think the intent is to have it dynamically update the combobox with one of those numbers depending on the selection in "Comments". BUT I also think it is a combobox because it gives the user an option to change what it was danamically populated with.

I've seen this work so I know it is possible...I just don't know how.

Let me know what other info you need.

[MAF4Fam6]

A few questions for you:
1. When you make a selection using the Comments Combo Box, what value are you storing? i.e. commentID, comment, Severity
2. Is your Combo Box updating a value to a field bound to your form's record source?
3. Any way you can provide a list of your form's bound field names, screenshot of form, or a stripped down version of your db?

-RC

[thewabit]

A few questions for you:
1. When you make a selection using the Comments Combo Box, what value are you storing? i.e. commentID, comment, Severity
I believe I am just storing the Comment.
2. Is your Combo Box updating a value to a field bound to your form's record source?
Yes...the comment selected updates the "Cooments" column in the record source
3. Any way you can provide a list of your form's bound field names, screenshot of form, or a stripped down version of your db?
Attached

Thanks!

[MAF4Fam6]

Thanks for the image. Makes it easier to work with since I have a later version of Access.
I understand you have the Primary Key set to the customerID in the customer table. Can you tell me if you have any duplicate values in the comment field in the comments table?

-RC

[thewabit]

I'm sorry...I'm so new at this I don't know if I have duplicate values. How could I check that?

[MAF4Fam6]

You should be able to use the Find Duplicates Query Wizard in Access 03. Can you see if you can use the wizard to find any duplicate comments in the comments table? The values in your comments field must exactly match to be considered a duplicate.
If your table does not contain duplicate comments, do you mind making this field a Foreign Key (No Duplicates allowed)?

-RC

[thewabit]

I see what you're asking. I do not have any duplicate comments in "tblcomments". Right now, it looks like the "CommentID" has a primary key.

[MAF4Fam6]

I understand the commentsID field has no duplicates since it is assigned the primary key. Can you tell me if the [comment] field name where you store your text comments has any duplicates as well?
The reason I ask, is because...in order for us to dynamically change the [Severity] field name's value, your comments field name will need to be a foreign key. In your tblcomments design view...go to [comment] field...can you tell me what Indexing is set to (located in general Tab at the bottom)?

-RC

[thewabit]

It says "Yes (No Duplicates)"

[MAF4Fam6]

That's great! That means we can update the Severity field on your form via the AfterUpdate event in your ComboBox's control.
You may find out later as you gain experience in Access, that there is usually more than one way to do this. The way that I know...is by building an update query and then running a Macro from the Combo Box's AfterUpdate event on your form to execute the query, hence, dynamically updating the Severity field.
It would be better if you could post a stripped down version of your database...if feasible.
Thanks.

-RC

[thewabit]

let me know what you mean by "stripped down"?

Thanks

[MAF4Fam6]

Sometimes, we may feel there may be sensitive, private information in your database. Therefore, as a courtesy, we may ask for you to provide a stripped-down version of your database (making a backup copy of your database, and then removing records containing private, sensitive information and replacing with made-up data). Enough made-up data (records) though that still ensures full functionality. Or...perhaps there are thousands of records and many tables causing the database to be extraordinarily huge. Then, you could delete unnecessary tables, just enough for us to work your current issue. Lastly, of course, would be for you to do a Compact and Repair prior to zipping for upload.
If you are comfortable with posting your database "as is", then that is perfectly fine too.
Thanks.

-RC