473,382 Members | 1,468 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

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 1182
zmbd
5,501 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,501 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,501 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,501 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

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

Similar topics

9
by: Kyle Root | last post by:
I'm working on a little practice program, but I've become stuck and am now utterly confused :? I've created a jumble of python modules, in each one is a tuple that goes something like, deps =...
13
by: mr_burns | last post by:
hi, is it possible to change the contents of a combo box when the contents of another are changed. for example, if i had a combo box called garments containing shirts, trousers and hats, when...
6
by: MX1 | last post by:
OK, here's one that's driving me nuts. Three tables. They are... Master Table JobRef Reference Table ToolRef Reference Table I've got a form for...
2
by: Chris Capel | last post by:
Say ... does anyone know of a good (perhaps free) .NET assembly dependency walker? Because when you have a project A with a reference B which is also a reference of C, and C is also a reference of...
5
by: Jay A. Moritz | last post by:
Error: The dependency '<my dll>' in project '<my project>' cannot be copied to the run directory because it would conflict with dependency '<my dll>'. I am getting a dependency error building...
3
by: DJTN | last post by:
I'm getting the following error when I try to compile my setup project in VS 2002. I have re-installed the .net framework 1.1 and it didnt solve the problem. WARNING: Unable to find dependency...
9
by: Brett Romero | last post by:
I have two projects in one solution - a library and executable. Each have a namespace such as: ROOT.myapp.Functional ROOT.myapp.UI However, each cannot reference the other. myapp.UI is...
1
by: LinDaOopD | last post by:
its easy to place an array values in the combo box. but i need to know how can i do it if i have to retrieve the values from a database. my platform is PHP MySql. please i need help :( combo box...
1
by: =?Utf-8?B?SmFzb24gUmljaG1laWVy?= | last post by:
I came across a code sample in a book that I am reading that uses a dependency property. I looked at some of the documentation for dependency properties but, for some reason, the concept is not...
1
by: vincentt | last post by:
Hi, We code DLL's and so far it was done using VS6.0. We are planning to migrate the VS.NET 2005 and use the VS6 VC++ code which generated the DLL to VS 2005 VC++.NET. However we donot plan to...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.