cascading combo box on subform

[dark11984]

Hi,
I'm trying to do a cascading combo box from my main form onto my subform with the criteria as an iif statement.

If mainform combo box = "NAT" then i want all records to be avaialble in subform combo box.
If main form combo box = anything but "NAT" then i want it to limit the records by the appropriate region.

the sql code ive got in subform 2 combo box is:

SELECT TblContractSite.ID, TblContractSite.CMSRef, TblContractSite.Site, TblLysRegion.SiteName, TblLysRegion.Region, TblLysRegion.BusinessUnit
FROM TblContractSite INNER JOIN TblLysRegion ON TblContractSite.Site = TblLysRegion.Site
WHERE IIf([forms]![frmcontract]![cboregion]="nat",([TblLysRegion].[Region] Like "*" & [forms]![frmcontract]![cboregion] & "*"),([TblLysRegion].[Region])=[forms]![frmcontract].[cboregion]);

This works ok for the anything but "NAT" condition but not the other.

Thanks.

 

[Galaxiom]

A field in the recordset cannot be used in a Where clause like this. The right hand side of the Where clause comparison is evaluated before being applied to the form's recordset.

Use a dynamic RecordSource query. Trigger a procedure to change the RecordSource property of the subform with the AfterUpdate event of the mainform combo.

Refer to the RecordSource property of the subform from the main form as:
Me!subformcontrolname.Form.RecordSource

 

[dark11984]

My SQL does not necessarily need to contain an IIf statement, i just want there to be two conditions in the combo box
1. if mainform combobox = NAT then subform combo box shows all records
2. if mainform combobox <>NAT (i.e. NSW, VIC) then subform combo box should show only sites within the selected state from mainform combo box.

Are you able to give me an example of a dynamic recordsource query?
Do i remove my cascading criteria from the combo box row source on the subform?

Thanks

 

[vbaInet]

Like GalaxiomAtHome mentioned, you need to build the sql string and apply that sql string to the record source of the combo box. Here's a link on building sql in vba.

The following code will do what you want but the LIKE operator is slow (so use at your peril):
Like IIF((Forms!... & "") = "NAT", '*', 'Forms!...')

 

[dark11984]

As always thanks vbaInet and also to you too GalaxiomAtHome.

That does the job for me. I just had to remove the apostrophes on the second condition to get it to work.

Cheers

 

[vbaInet]

Glad we could help