Conditional Quiry based on field value in current record

[Art Barnwell]

Can a query be created that limits values in a table-field based on a value in another table-field value in the same row

Example: Assume you have named fruit info and this table is related to two other tables, Fruit Colors and Fruit Names. If you are in the fruits info table and you select “Red Fruits” in a field named fruit color (a lookup field from the fruit colors table) and wanted the next field to only show a lookup selection list of red fruits (i.e.: Red Apples Cherries and strawberries). How can this be accomplished

 

[bat17]

You use the afterUpdate Event of the first combocontrol to set the recordsource of the second control.

Peter

 

[Art Barnwell]

Can this be done in a table view or do you have to use a from?

 

[Art Barnwell]

Can this be done in a table view or do you have to use a from?

 

[SydneyGeek]

To use ComboBoxes in this way, I think you have to use a form.

Are you trying to show all red fruits (separate records, best displayed in a sub form) or do you want one list to limit the options in another list?

 

[bat17]

It should be done at form level. Data entry should always be done at form level anyway to get proper control of the data being put in.

Peter

 

[Art Barnwell]

I was using the apples & oranges scenario because I did not want to bore you guys with specifics. I am tying to build a database that tracks Loan Applications. This requires numerous tables with one to many relationships and some of these relationships are more that three levels deep. What I am tying to do is learn how to create queries that limit data in a combo-box based on a selection from another combo-box.

I have a group of tables related as such.

tblBrokerCompanies to many tblBrokerLoanOfficers

tblBrokerCompanies to many tblBrokerLoanProcessors

I have another table called tblLoanApps. This table has 2 “look-up” combo-boxes for selecting a BrokerLoanOfficer and BrokerLoanProcessor, respectively. BrokerLoanOfficer(s) are unique and can only be associated with one of many different BrokerCompanies. BrokerProcessors have the same sort of relationship.

The BrokerLoanOfficer combo-box in frmLoanApps is populated with all BrokerLoanOfficers where status = -1 (active). Currently, the Loan Processor Combo-box lists all processors from all Broker Companies without respect to the Broker Company associated with the selected loan Officer. Not Good!

When a BrokerLoanOfficer is selected in the first combo-box, I need two things to happen.

First, I need certain information from the BrokerCompany, associated with the BrokerLoanOfficer via tblBrokerCompaies, to be displayed on the form.

Second, I need the list of LoanProcessors in the LoanProcessor combo-box to list only the LoanProcesors where the BrokerCompanyID field in the tblBrokerProcessor, associated with the selected LoanProcessor, matches the BrokerCompanyID field in tblBrokerLoanOfficers based on the BrokerLoanOfficer selected in the BrokerLoanOfficer combo box.

I can accomplish this with a parameter query but I do not know how to pass the BrokerCompanyID value, associated with the BrokerLoanOfficerID, selected in the first combo-box to the select query for the second combo-box. I will also have to keep the BrokerLoanProcessor combo-box deactivated until after a BrokerLoanOfficer has been selected.

Piece of cake, r I g h t !!

SORRY about the long version and THANKS for any help. I usually to stick to Ms-Excel applications, which is how I originally found this board. It has always proven to be the best out there and very responsive. THANKS, THANKS, THANKS!

 

[bat17]

If you have the full install of Access then you should have a copy of the Solutions.mdb database. This has a good example of using one Combobox to limit the choice in another. If you don't have the Db I believe that it is downloadable from Microsoft but I do not have the url.

peter

 

[SydneyGeek]

tblBrokerCompanies to many tblBrokerLoanOfficers

tblBrokerCompanies to many tblBrokerLoanProcessors

I have another table called tblLoanApps. This table has 2 “look-up” combo-boxes for selecting a BrokerLoanOfficer and BrokerLoanProcessor, respectively. BrokerLoanOfficer(s) are unique and can only be associated with one of many different BrokerCompanies. BrokerProcessors have the same sort of relationship.

The BrokerLoanOfficer combo-box in frmLoanApps is populated with all BrokerLoanOfficers where status = -1 (active). Currently, the Loan Processor Combo-box lists all processors from all Broker Companies without respect to the Broker Company associated with the selected loan Officer. Not Good!

The way to build this is via a loop.
(1) The downstream Combo has the upstream Combo's bound field as a criterion.
(2) You set the upstream Combo to refresh the downstream after you pick a new item.
So...

First, cmbProcessor (my shorthand )
In the Rowsource, hit the Builder (...) button. You will need to add the CompanyID field to the query if it's not already there -- it can be the last field in the query because it won't be visible anyway.
In the Criteria row for CompanyID, type Forms![MyFormName]![UpstreamComboName].
Close and save.
Don't forget to increase the Column count by 1 if you have added a new field.

Now, cmbCompany.
In the AfterUpdate event, insert the code Forms![MyFormName]![DownstreamComboName].Refresh (or you could use Requery instead).

That should do it.