WHERE syntax problems

[Guest]

hi - in my form's header I have 2 unbound combo boxes, cbo_A and cbo_B.
cbo_A's Row Source is A_ID, A_Name, A_Count all taken from qry_A. So far, so
good. In the AfterUpdate event I have the following code;

Me!cboB.RowSource = "SELECT B_ID, B_Name FROM tbl_B WHERE B_ID <
Me!cbo_A.column(2)";

I'm trying to restrict B_ID in cbo_B to values less than A_Count in cbo_A
(both are integers), but I just keep getting syntax errors. I don't think
I'm referring properly in the last part of the WHERE statement??? any
clues out there?

 

[John W. Vinson]

hi - in my form's header I have 2 unbound combo boxes, cbo_A and cbo_B.
cbo_A's Row Source is A_ID, A_Name, A_Count all taken from qry_A. So far, so
good. In the AfterUpdate event I have the following code;

Me!cboB.RowSource = "SELECT B_ID, B_Name FROM tbl_B WHERE B_ID <
Me!cbo_A.column(2)";

I'm trying to restrict B_ID in cbo_B to values less than A_Count in cbo_A
(both are integers), but I just keep getting syntax errors. I don't think
I'm referring properly in the last part of the WHERE statement??? any
clues out there?

A Query doesn't recognize the Me! keyword - that's valid only in VBA
code. Try replacing it with Forms!yourformname!, or build the SQL
string by concatenating the value in the combo box:

Me!cboB.RowSource = _
& "SELECT B_ID, B_Name FROM tbl_B WHERE B_ID < " _
& Me!cbo_A.column(2) & ";"


John W. Vinson [MVP]

 

[fredg]

hi - in my form's header I have 2 unbound combo boxes, cbo_A and cbo_B.
cbo_A's Row Source is A_ID, A_Name, A_Count all taken from qry_A. So far, so
good. In the AfterUpdate event I have the following code;

Me!cboB.RowSource = "SELECT B_ID, B_Name FROM tbl_B WHERE B_ID <
Me!cbo_A.column(2)";

I'm trying to restrict B_ID in cbo_B to values less than A_Count in cbo_A
(both are integers), but I just keep getting syntax errors. I don't think
I'm referring properly in the last part of the WHERE statement??? any
clues out there?

The value must be concatenated into the SQL string.
Try:

Me!cboB.RowSource = "SELECT B_ID, B_Name FROM tbl_B WHERE B_ID <
" & Me!cbo_A.column(2)

 

[Guest]

Yes, that works well. Thanks for clearing that up John & fredg. Or is it
"John" & fredg & ";"

 

[John W. Vinson]

Or is it "John" & fredg & ";"

<snork!>

"Two! Four! Six! Eight!
We shall not concatenate!"

John W. Vinson [MVP]