WHERE syntax problems

V

Victoria

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?
 
J

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?
Click to expand...

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]
 
F

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?
Click to expand...

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)
 
V

Victoria

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