Populating a field based on the value of another field using FORM COMBOBOX or Lookup

[ZEVVER]

I am a hack, not a pro programmer, so if yu answer, I need hand holding. I did complex applications in ACCESS 15 years ago and I am so rusty I consider myself a beginner.


I tried writing SQL for a look up and that bombed. So I am trying the FORM route now.

I am having difficulty describing the issue in words , so look at snip pictures first, please.

My main table is (OR) Original Resources
The other tables a LOOKUP tables
OR is autonumber primary key
the 2nd field is CATEGORY type: I am populating it with a simple LOOKUP from the table CATEGORY LIST (there are only 4 categories; only two fields - a primary key & the CATEGORY)
the 3rd field SUBCATEGORY is the one that has me hung up (rust, bigtime). I can get the LOOKUP to list the choices that are stored in the Table SUBCATEGORIES (3 fields= primary key, OR CATEGORY which is the same value as stored in the main table, & subCAT) I could pick the value I want and get by.

But my issue is that all the subCATs for all OR CATEGORYs list. Preferrably, I only want to be able to select from the subCATS "where Me.CATEGORY = the OR CATEGORYs in SUBCATEGORIES
See - I can't phrase this succinctly (rust)

I don't wan to see (in the combobox) all the subcategories for the irrelevant categories. I want code that will check to see which category was selected in field 2, and based on that selection, filter the subcategories that are relevant to that selected category.

I presume I need some event, or an event "after field update" or some policing effort to be sure the user made a category selection, before attempting to populate the subcategory field.

Once I get this working right, then the same issue will present for the 4th field (Media Type) which will be populated based on the selection made in the subcategory field, because each subcategory has a "cascading" number of media types to select from.

Hence, There are three tables and example data
CATEGORY---------------------AUDIO-------------------------------------------------------Document
SUBCATEGORIES--------------Voice note or Interview------------------------------------Memoir or transcript
MEDIA TYPES ----------------cell phone or camcorder or mp3 recorder-----------------Jerry's memoir or Frank's letter or government or professor

all these fields are required. all tables have primary keys

There are singular categories, many subcategories so duplicates are ok, and many media types to subcategories so subcategories have duplicates in that table

I realize I am being dense. It shouldn't be so difficult. Take pity

[Bob Fitz]

Can you post a copy o the db

[Welshgasman]

That is called Cascading Combos.
https://www.youtube.com/results?sear...+combos+access

[Micron]

You might want to halt and refresh your memory wrt design principles. Some changes might be best. Hard to say from your posts but if you are using lookup fields in tables, that's generally considered a no-no. So are spaces in names. I also wonder if you need to break out what looks like repeating fields into related tables (category stuff). If a subcategory can be related to more than one category, I'd say that's a pretty strong case for doing so. Note that when tables are not linked in a query, you're liable to get way more records than you expect (it's called a Cartesian query) because records can be created based on possible combinations rather than real records/data. I'd advise reviewing the links in post 14 here
https://www.accessforums.net/showthr...575#post515575

[ZEVVER]

That is called Cascading Combos.
https://www.youtube.com/results?sear...+combos+access

Your video link was great. Give a starving man a fishing pole not a fish.
Thanks !!!!!! I brushed up.

But the 2nd COMBOBOX in the cascade will not read the value in the first combobox.
Yes, the query pulls the correct records if I manually pass it the right value.
But the second bug is that the requeries are not working
and the third bug is that the value I passed - or clicked in the second combobox is not being stored correctly.

I am attemtping to upload the DB because it will be easier for you and Mr. Fitz asked me to

I really think my table setup mirror Rost's exampl ewith State and City, but his code worked great and my "mirror"
doesn't . . . . I really tried . . for 10 hours. . . . can you please figure it out and spoon feed me on this,
so I can apply the knowledge to the next step ahead?

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications.

[Micron]

I'm guessing that your issue is that you get a parameter prompt? That would have been so much more helpful.
Look at the spelling of the form in the query and compare that to the spelling of the form.

Now you have another reason to not use long convoluted object names. Fixing that will get rid of the parameter prompt but there is more to do since the 2nd combo produces no results. Getting late here now so I will refrain from digging deeper for tonight. You have table design issues beyond names. In a related/child table you don't store the primary key as a foreign key AND the value that the key represents. In other words, if 2 means video, in the child table you store 2, not 2 and "video".

EDIT -forgot to mention that the same error keeps popping up - something about requerying a query in form events. Why do that?
And I guess I'm too curious. The empty combo is because you have 2 fields in the underlying query yet you have 3 columns in the combo. Cut the count to 2 and remove the last dimension value for column width.

You have to understand that binding combos means that if you play around with values you can change the record field value by accidentally changing the combo selection. So beware.

[ZEVVER]

I took all the great advice, things are better. But not quite right. I attached the revised DB

So at this point, I have the afterupdate event requery the second cascaded combobox (as well as FORM ON CURRENT)

After I select in The first combobox, I observe the vaule of the bound key is not stored in the table until I advance to the next record. That is not good, if the user makes a change to the record and does not advance. Requery the combobox after update won't do anything there.

Then when I click the second combobox, the correct subcategory does not run based on the first combobox selection. But if Irun the query, the correct subcategories display. In other words the query works, but despite that query being listed as the row source, it does not execute correcly, despite requery on 1st combobox after update.

I am stuck again.

I don't know how to delete previous uploads

[ArviLaanemets]

I have adviced this several times in those forums here - why are people making it so difficult?

You have a lookup table tblCategories: CategoryID, Category;
You have a tookup table tblSubCategories: SubCategoryID, CategoryID, SubCategory (there must be at least one subcategory for every category);
You have a table tblYourTable: YourTableID, SubCategoryID, ... (NB! No need for CategoryID here!);
You have a form fYourForm, based on table tblYourTable.

On form fYourForm, you have a combo cbbSubCategory, linked to field SubCategoryID of table tblYourTable, and having RowSource property like

SELECT subcat.SubCategoryID, cat.Category & ": " & subcat.SubCatecory FROM tblSubCategoryID subcat LEFT JOIN tblCategory cat ON cat.CategoryID = subcat.CategoryID ORDER BY 2

When you open the combo to select subcategory, you can scroll down to category this subcategory belongs to, or you can start typing the text for this category. With every character you type, you are moved down in combo list to first entry starting with entered string. At some moment the category you did want will be displayed in combo list, and you can select a subcategory you want from there. And no need for any VBA!

[ZEVVER]

Sir, please revisit my persisting problem and updated db uploaded again.

ArviLaanemets answered, but he complicates the matter by introducing joins and insisting on showing all categories and allowing the user to select the wrong category.

I prefer ROST's cascading solution and your advice.

I just cannot find why the table is not updating immediately and hwy the held value for combobox one is not being past to the cascade immediately even though if I run
the cascade query manually the result is correct.

[Micron]

Probably not updating because you don't save the changes. This happens automatically when you move off of the record. If not doing that, use Me.Dirty = False at some point.
Either you posted the wrong db in post 8 (both versions have the same create date/time or missed comments I made or simply ignored them - because you've fixed nothing in your latest version that I mentioned.

the same error keeps popping up - something about requerying a query in form events. Why do that?

The empty combo is because you have 2 fields in the underlying query yet you have 3 columns in the combo. Cut the count to 2 and remove the last dimension value for column width.

Look at the spelling of the form in the query and compare that to the spelling of the form.

[pbaldy]

I don't know how to delete previous uploads

You should be able to edit your previous posts. Click on the Go Advanced button, then the Manage Attachments button. You should see the existing attachments and be able to delete them.

[ZEVVER]

CASCADE combox issue:

Once the user selects a new value in the first combox, when the second box runs the query - the correct value is returned,
yet the second box does not populate with the correct choices from the underlying table

It appears that the second box is pulling the value from the field in the table (which does not change until the user moves to the next record or closes the form)
even though the second box uses the query as the row source (and the query doe shav ethe correct value if run independently from the box)

I tried FORM and CONTROL level Me. dirty commands and those did not update the field value iin the table whatsoever.

I am really confused

1) why won't the query give the second box the correct value ?
2) why won't the field in the table change unless the record changes?

I uploaded the correct DB this time for sure

[Bob Fitz]

Try This:

Private Sub COMBOcatTP_AfterUpdate()    
    If Me.ActiveControl.OldValue <> Me.ActiveControl Then
       Me.COMBOsubC = Null
    End If
    COMBOsubC.Requery
 
End Sub

[pbaldy]

Regarding your PM about deleting attachments, you should see this: