How can I use value in current record as criteria to filter source from another table

[telecom5068]

So I have a table called IPOrphans with the following fields:

Extension
AELN
Location
RELN

In a form for this table, I’m trying to create a combo box in RELN that uses the value in Location in that same record as criteria to filter choices in the combo box whose source is a different table (called IMCELN) which also contains a field “Location.” I’m trying to match the Location value in the current record of IPOrphans with the Location value in IMCELN to give me a list of choices. This list is a subset of IMCELN.

Example below

IPOrphans Form
Extension AELN Location RELN
72806 72803 WCLL2S <Combo box>

IMCELN Table
Department Location DELN
Infectious Disease WCLL2S 77899
Public Relations WCLL2S 72007
Pathology WCLL2N 77969

I want, if possible, the combo box in IPOrphans form to have at its source a query which uses the Location field of the same record (Value: WCLL2S) as criteria to return only the records for Infectious Disease and Public Relations.

I have searched for a solution for this and come close, but I just can’t get the statement right. Sometimes it comes up asking for IPOrphans.Location (which, if I enter “WCLL2S” will give me the choices I want. I just want it to use the Location field in that record to supply that. Otherwise it uses that value as criteria for the rest of the records.

[zmbd]

To be honest, I'm not quite following your post, but let us prod the question a tad and see where we go (^_^)

If the table relations are set correctly you should be able to do this with a parent/subform arrangement; however, as you are looking that the comboxes, you might take a look thru the following articles on filtering.

Even if these are not an exact match, they may give you enough to solve the question (I'm thinking the Cascaded Form Filtering), or you will read thru these and can come back and tell use which is closest to your current dilemma it would help us to help you find a solution...

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

One other thing to keep in mind with Combo/list-boxes and often very mis-understood:


[z[edited the following for easier reference from next post]]

• [Control Source] = is the field in a table that the combo/list-box control will read/write the data within the database.
• [Row Source] = this is the information that is displayed in the control. It can be a value list, linked to a table/query, or have the SQL embedded within the property.
• [Bound Column] = This is the actual value returned from the information shown in the control, this value must be the proper data-type for [Control Source]

Therefor, the [Row Source] will be where you will end up in one way or the other to limit the available choices to the user and I think that you almost have the answer here

I want, if possible, the combo box in IPOrphans form to have at its source a query which uses the Location field of the same record (Value: WCLL2S) as criteria to return only the records for Infectious Disease and Public Relations.

which is why I think the Cascaded Form Filtering article is the closest match.

If you don't follow the article, post back here and we'll follow up on it.

[telecom5068]

Thanks, zmbd.

I looked at the Cascaded Form Filtering article but didn't find it to be exactly what I needed (as far as I could understand it... maybe I'll study it a little more). I know I worded this very awkwardly, so thanks for your reply anyway.

Turns out the parent/subform thing was the perfect solution for filtering the choices. At this point I just need to figure out a good way to transfer the appropriate data from the subform to the parent form. Maybe you have a good idea about how to do that. I've attached a jpg of my current setup (the Select field is just something I'm playing with... I don't really know how to do what I need to do)

[telecom5068]

BTW, I have to get the correct DELN value in the subform into the RELN field in the parent form.

[zmbd]

I'm not sure your database is normalizedwhich will make your life very difficult; however, not really sure from what you've provided; however, you shouldn't be storing information twice.

In any case, I'm really thinking that the solution is cascading combo, especially if your database is normalized. Once you've done one it's so easy you'll do the palm-to-forhead-slap ( (^_^) I Promise... very very easy)

Please forgive me today, there's an event this weekend that I co-chair for my Church otherwise I'd put a simple example together for you.

But let me see if I can get you to the cascading thing here:
+ The concept is a parameter based query using the control value to feed the query... read thru this for context and the section that were really after is Creating a form to supply parameters to a query because we'll be taking the value of the first cascading combobox - based upon the bound column

+ This query is then used to feed the rowsource of the combo-box
The trick here, is that in the after_update event of the control acting as the parameter, you have to force the combobox to update it's rowsource, because it only takes a picture.

You're basically doing the parent-child, just in a combobox and feeding the table field shown in the control source directly the value of the cbo's bound column.

this link covers the same information as our article; however, from a different angel. I find that sometimes having the same information from two sources often helps me to understand what is going on: Basing one combo box on another - this is a more hand's on tutorial that if you will create the project I almost guarentee that "a-ha" moment (just don't do that palm-slap too hard (^_^) )