cascading combo boxes - help with syntax

[luciegiles]

I'd like to start off with an apology - I posted a question on a similar matter sometime ago and didn't respond to those who took the time to answer my question. At the time i gave up but have used the responses I got to revisit this now - thanks to those who reposnded originally. I'm nearly there but need help with some syntax (I think).

I am filtering the option in one combo box cboStatus based on the entry to another combo box cboLocalityTeam. cboStatus uses lookup list tlkpStatus with StatusCode (PK), StatusDesc and LocalityCode (FK) while cboLocalityTeam uses lookup tlkpLocality with Localitycode (PK) and LocalityDesc.

I've done the filter with a query qryServiceLookup:

SELECT tlkpStatus.StatusCode, tlkpStatus.ServiceDesc, tlkpStatus.LocalityCode
FROM tlkpStatus
WHERE (((tlkpStatus.LocalityCode)=[Forms]![frmSub]![cboLocalityTeam]));

It is requeried to reflect any change to the record with a macro.

frmSub is a subform that sits within frmMain; the above works fine when I open frmSub directly but when I open it within frmMain I get an "'Enter Paramaeter Value' = [Forms]![frmSub]![cboLocalityTeam" error. I assume [Forms]![frmSub]![cboLocalityTeam needs to be changed to reflect that frmSub is a subform within frmMain but I'm not sure and haven't managed to change it successfully. Appreciate any guidance!!

[NeoPa]

I'm not sure exactly where you're getting hung up, but these may help point you in the right direction (Referring to Items on a Sub-Form & Cascading Combo/List Boxes).

I wouldn't do the filtering in the SQL as you do, but if you are to, you need to get the reference correct. Remember (after reading the linked article) that a subform control is not the same as the form that it contains.

[luciegiles]

Thanks NeoPa - I just needed help with the syntax and the first article you mention helped with that - it is now working.

Why not filter in the SQL? I tried working through the same problem with your Cascading Combo/List Boxes article but couldn't get it to work.

[NeoPa]

The filter is something that needs changing most frequently. The rest of the query is the fundamental design. If you include the filtering within the query then when you need to change it you will have to revisit the whole query's SQL. Separate it out however, and you can leave the worry about the complications of the whole query to the original design. The main query can be stored simply as a QueryDef and only the Filter part need be designed directly in a string in your code.

As far as putting references within SQL to form controls goes, I try to avoid that as that makes the form being open already a dependency for the query to work. When you are building the SQL in your code there is no need to use a reference when you already have the current value available.

PS. I'm glad you managed to get that working from the linked article Lucie. Good for you.