Combo box dependency

[abcrf]

Hi everyone,

I'm trying to limit the values in a combo box (Combo33) based on the previously-selected value of a different combo box (Combo42) in the same form (FRM_TRAINING_PROTOCOL). In the row source for Combo33, I have:

SELECT TBL_PROTOCOL.[PROTOCOL NUMBER] 
FROM TBL_PROTOCOL 
WHERE (
   ((TBL_PROTOCOL.[STUDY NAME])
         =[Forms]![FRM_TRAINING_PROTOCOL]![Combo42])
) 
ORDER BY TBL_PROTOCOL.[PROTOCOL NUMBER];

(TBL_PROTOCOL is a list of protocols by study that users need to be trained in, and the training record is contained in TBL_TRAINING_PROTOCOL.)

Back in form view, though, Combo33 is not updating based on the value of Combo42. What do I have wrong in the above code? FYI, I do have Combo33 set to requery after update of Combo42.

Thanks for your help!

[zmbd]

Forms basically use a snapshot of the current data, you have the right idea to force the control/form to requery; however, keep in mind, the value of the combobox is going to be the bound column. So is the value of [Forms]![FRM_TRAINING_PROTOCOL]![Combo42] actually what is stored in TBL_PROTOCOL.[STUDY NAME]

Follow the example given in:
home > topics > microsoft access / vba > insights > cascading combo/list boxes this goes into much more detail.

[abcrf]

Perhaps not. Currently, Combo42 is populated like so:

SELECT TBL_STUDY.[STUDY NAME] 
FROM TBL_STUDY 
ORDER BY TBL_STUDY.[STUDY NAME];

I did that because there are multiple entries with the same study name in TBL_PROTOCOL. Is the solution then to have it pull from TBL_PROTOCOL but restrict it from showing duplicates?

[zmbd]

Sounds like your data isn't normalized:normalize your data first

If you're reading from the same table with multiple entries of the same text over and over and over again you might eventually run in to the same issue as here:
>>moving between records in a form is slow in certain combo boxes >

Being pulled on multiple projects today so I'll have to re-read your posts again to make sure I'm on the right track. :)

[abcrf]

I am, yes. Specifically, TBL_PROTOCOL is a record of protocol versions, and each study has several different protocol versions. I'll look into what you mention, for sure. This alone shouldn't be causing my problem, though, correct?

[zmbd]

+ so just be sure I understand,
Combo33 (cbo33) has a row-source based on TBL_PROTOCOL that you want filtered by the value selected in combo42 (cbo42) so that the only choices shown in Cbo33 are those where [TBL_PROTOCOL]!STUDY NAME]) match cbo42's value.

?? What is actually happening with Cbo33 ??
Are you seeing nothing or everything from [TBL_PROTOCOL] as the available options?

+ Double check that Cbo42's bound column is 1

+ Please cut&paste the after_update event code from Cbo42 maybe something malformed in there.

+ Let's see what is actually being returned CBO42:
In Combo42's after_update event insert
Debug.Print Me!Combo42.value
Run your form selecting Cbo42 a few times
<ctrl><g> will open the immediates-pane so that you can see what is being set by the control just to be sure what you think you selected is actually the value set for the control.

+ This shouldn't make a difference; however, you might change your SQLs replacing dots with bangs between table name and field names; thus,
TBL_PROTOCOL.[PROTOCOL NUMBER]
becomes
TBL_PROTOCOL![PROTOCOL NUMBER]

I really don't think this will make a difference; however, it shouldn't hurt anything.

-z

[abcrf]

Hi Z,

Yes, you've got that correct. Combo42 selects a value from the table "study". I'm trying to have Combo33 offer the values from the "protocol number" column of the table "protocol" for all records where the "study" column of the table "protocol" corresponds to the study they just selected.

Combo33 is displaying no options.

The bound column of both 42 and 33 is 1.

The after update code for 42 is:

Private Sub Combo42_AfterUpdate()
Combo33 = Null
Combo35 = Null
Combo33.Requery
Combo35.Requery
End Sub

The immediates pane does display the value I've selected for 42.


Thanks so much for your help with this!

[abcrf]

Turns out that the problem was with the data my colleagues had entered thus far into the tables. There was nothing wrong with my lookup or code, and I've got it working now. Sorry for the wild goose hunt, but thanks for engaging in it so willingly!

[zmbd]

The mis-entered data is one of the reasons for normalizing your data - one table, one entry, and then CBO or other methods for validation against that information. :)

>Just a suggestion> Rename your controls from the default to something that indicates the usage or the data

For example, a textbox bound to say, [tblUser]![user_pk], becomes, "z_txt_userpk" where the default is "user_pk"
Unbound controls follow along the same trend for example if I had a unbound text box for a new user badge number, default might be [text39] which becomes "z_txt_NewUserBadge" etc...

This just makes the coding much easier to follow.
So in your case:
cbo42 might be renamed to "cbo_tblSTUDYName"
cbo33 might be renamed to "cbo_tblPROTOCOLNumber"

Looks like I cross posted with you :)