Cascading Comboboxes Refuse To Work

[Soule]

Hi, Everyone,

I'm trying to create a dynamic rowsource for my Combobox B, so that when a user makes a selection in Combobox A, B will give relavent choices.

My environment is:
- Access 2007
- .accdb file
- Button code in standard module pointed to with event procedures using Call subs
- Am in datasheet view when compiling/stepping through/running
- Early binding w/Intellisense
- Libraries (in this order):
VB for Apps
MS Access 12.0 Obj. Lib.
MS Outlook 12.0 Obj. Lib.
MS OFC 12.0 Access db engine Obj. Lib.
MS VB for Apps Extensibility 5.3
MS VBScript Regular Expressions 5.5
MS ActiveX Data Objects 2.8 Lib.
OLE Automation
- No compile, step-through or run-time errors

Form: A1 Onboarding Tracking Form
Table: A1 Movie Code Table

Unbound Parent Combobox A: A1 Form RawMovieTitle View Control
Combo A Row Source:

SELECT DISTINCT [A1 Movie Code Table].RawMovieTitle FROM [A1 Movie Code Table] WHERE [A1 Movie Code Table].MovieCodeSendDateClone IS NULL ORDER BY [A1 Movie Code Table].RawMovieTitle;

Combo A Row Source Type: Table/Query

Bound Child Combobox B: A1 OT Movie Code Control

Combobox A's Row Source query uses null/not null on the MovieCodeSendDate field in the table and populates Combobox A with movie titles with at least one null return (empty date field).
Combobox A populates fine based on its query row source. The problem I'm having is the Event Procedure SQL in my AfterUpdate() event:

Private Sub A1S1_Form_RawMovieTitle_View_Control_AfterUpdate()
On Error Resume Next
   Me.[A1 OT Movie Code Control].RowSource = "Select [A1 Movie Code Table].RawMovieCode " & _
   "FROM [A1 Movie Code Table] " & _
   "WHERE (IsNull([A1 Movie Code Table].MovieCodeSendDateClone)) " & _
   "WHERE [A1 Movie Code Table].RawMovieCode = '" & [A1 Form RawMovieTitle View Control].Value & "' " & _
   "ORDER BY [A1 Movie Code Table].RawMovieCode;"

Is there something wrong with what I've done here? The cascade Combobox B shows no values at all after the AfterUpdate() event coming out of Parent Combobox A.

I can't put my finger on what's happening. The smallest advices will help. Thanks for reading!

This post is also here http://www.accessforums.net/showthr...oboxes-Sql-Refuse-To-Work?p=108815#post108815

Frank

 

[pbaldy]

As posted on two other sites:

You can't have 2 WHERE clauses. You'd separate conditions with AND or OR:

WHERE Field1 = 123 AND Field2 = 'ABC'

http://www.excelguru.ca/node/7

 

[John Big Booty]

The sample here may give you some pointers. It demonstrates cascading combo boxes with a dynamic Row Source.