I am designing a database and I was wondering if there was a way to utilize the IIF function to chose what record source a combo box uses based on the value of another field within the the same record.

For example the first field is Manufacturer which is a combo box that draws from tblManufacturers. Based on that value, Field 2's combo box would use record source X if Manufacture X was selected, if Manufacturer Y was selected then it would use record source Y.

Thanks for any help

You can:

put an event procedure in the "after update" of the field "Manufacturer".

Specify your individual row source SQL in there for both Manufacturers,

Then set the RowSource to the selected SQL stmt.

Requery the second combobox

something like:

Dim strSQL As String

Select Case Me.Model

Case Is = "Spitfire"
strSQL = "SELECT T1.* FROM tblCounty T1"
Case Else
strSQL = "SELECT T1.* FROM tblCountry T1"
End Select

Me.County.RowSource = strSQL

Me.County.Requery

Switching datasources like this suggests to me that your design is wrong. I would expect the data to be selected from a single table using a query that depends on the value selected in the first combo.

Actually, it just sounds to me like it really is just a cascading combo box (and someone who doesn't know the lingo) - correct me if I'm wrong. See here for an example:
http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

Thanks Boblarson

That was exactly what I was trying to do. How ever I can't get things working properly. Here is my layout.

tblManufacturers
----------------------
ManufacturerID, PK
Manufacturer, Text

tblBrands
----------------------
BrandID, PK
ManufacturerID, FK
Brand, Text

tblSets
----------------------
SetID, PK
BrandID, FK
Set, Text

I am trying to create a form to add Sets to the Sets table. I added a Combo Box to lookup the Manufacturer and added the requery event procedure. However, the Brands Combo Box does not update upon selecting a manufacturer. Any help on where I went wrong would be appreciated.

...added the requery event procedure. However, the Brands Combo Box does not update upon selecting a manufacturer. Any help on where I went wrong would be appreciated.
Did you add it to the first combo's AFTER UPDATE event? Also, what is the second combo's rowsource? Does it have the first combo as the criteria under the MFR ID column?

The first Combo Box's row source looks like this:

SELECT [tblManufacturers].[ManufacturerID], [tblManufacturers].[Manufacturer] FROM tblManufacturers ORDER BY [Manufacturer];

The second combo box's row source looks like this:

SELECT [tblBrands].[BrandID], [tblBrands].[Brand] FROM tblBrands ORDER BY [Brand];

Here is the database if you want to take a look

two things -

1. You didn't include MfrID in the second combo in order to limit it.

2. You didn't get the requery on the first combo after you renamed it.

See attached.

Thanks a million, I have never gone this deep into Access.