populate combo box based upon another combo box selection tied to subform

[mdewell]

[Form_sbfHIPAAdatasheet].RecordSource = SubFormSQL & WhereStatementSQL
 
  Set rs = db.OpenRecordset(SubFormSQL & WhereStatementSQL)
  If rs.RecordCount = 0 Then
       ' Don't add ending ")"

[mdewell]

OK, there's a little bit of missing code becauuse for some reason it won't let me post it here. But anyway, the last section of code is creating the SQL statement to update the ComputerLookUp combo box.

Anyhow, June7, your code looks a lot more clean, and perhaps more efficient, but is it possible to add all the filters I have into the SQL statement and get it to work. I also have some more I need to add to the filtering process, so I guess I need to get a good understanding of the SQL statement you are making with :

SELECT Computer.Computer
FROM (HIPAA_relational LEFT JOIN OU ON HIPAA_relational.OU_ID = OU.ID) LEFT JOIN Computer ON HIPAA_relational.Computer_ID = Computer.ID
WHERE (((IIf([Forms]![HIPAA_datasheet]![OUFilter]="<All>",[OU_ID] Like "*",[OU_ID]=[Forms]![HIPAA_datasheet]![OUFilter]))<>False));

I'll post my current version so you can perhaps see what I'm up to, and maybe give me some help with my new issue of getting an unbound textbox to update a table.

[June7]

You certainly have expanded the Set OUFilter function.

Well, yes, I suppose you can have as many criteria in that SQL as you want. However, I don't understand the <>False. That was not in my version.

What is it you do not understand about the query?

[mdewell]

I guess I need to look at how your SQL statement is made. I'm a little more concerned with my other topic I started concerning the 'Textbox record source for datasheet subform view'. So if you could reply to that one, as you started, and we can perhaps close this thread.

Also, just for clarification, I know I don't have that many records, but should I have more records, is my building the SQL statement a slower process than just writing out the SQL staement that does all the processing with in the statement. I.E., your statment is the somewhat simple:

SELECT Computer.Computer
FROM (HIPAA_relational LEFT JOIN OU ON HIPAA_relational.OU_ID = OU.ID) LEFT JOIN Computer ON HIPAA_relational.Computer_ID = Computer.ID
WHERE (((IIf([Forms]![HIPAA_datasheet]![OUFilter]="<All>",[OU_ID] Like "*",[OU_ID]=[Forms]![HIPAA_datasheet]![OUFilter]))<>False));

Where as mine isn't that advanced, and just uses a WHERE and AND, without a LEFT JOIN.

Actually, looking at your statement, I guess I need to do some research into the IIF statement. I'm just curious how much faster your statement is, compared to the one I build?

Thanks