469,091 Members | 1,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Combo box dependency

33
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TBL_PROTOCOL.[PROTOCOL NUMBER] 
  2. FROM TBL_PROTOCOL 
  3. WHERE (
  4.    ((TBL_PROTOCOL.[STUDY NAME])
  5.          =[Forms]![FRM_TRAINING_PROTOCOL]![Combo42])
  6. 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!
Dec 18 '15 #1
8 936
zmbd
5,400 Expert Mod 4TB
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.
Dec 18 '15 #2
abcrf
33
Perhaps not. Currently, Combo42 is populated like so:

Expand|Select|Wrap|Line Numbers
  1. SELECT TBL_STUDY.[STUDY NAME] 
  2. FROM TBL_STUDY 
  3. 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?
Dec 18 '15 #3
zmbd
5,400 Expert Mod 4TB
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. :)
Dec 18 '15 #4
abcrf
33
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?
Dec 18 '15 #5
zmbd
5,400 Expert Mod 4TB
+ 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
Dec 19 '15 #6
abcrf
33
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo42_AfterUpdate()
  2. Combo33 = Null
  3. Combo35 = Null
  4. Combo33.Requery
  5. Combo35.Requery
  6. End Sub
The immediates pane does display the value I've selected for 42.


Thanks so much for your help with this!
Dec 21 '15 #7
abcrf
33
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!
Dec 21 '15 #8
zmbd
5,400 Expert Mod 4TB
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 :)
Dec 21 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Kyle Root | last post: by
13 posts views Thread by mr_burns | last post: by
2 posts views Thread by Chris Capel | last post: by
9 posts views Thread by Brett Romero | last post: by
1 post views Thread by LinDaOopD | last post: by
1 post views Thread by =?Utf-8?B?SmFzb24gUmljaG1laWVy?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.