Combo Box Row Source Code

[BillyS]

On the same form, I am trying to limit the choices in
Combo Box 2 (cboBox2) based on the selection in Combo Box
1 (cboBox1) with the cboBox2 Row Source as:

SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2
= 'VAL(Me.cboBox1.value)'

My result is an empty list in Combo Box 2. Is there
something wrong with my syntax, or is there another issue?

Thanks.

 

[Cheryl Fischer]

Billy,

The first thing I noticed about your SQL statement is that you have quotes
around an expression which should return a number:

VAL(me.cboBox1.value).

If you are passing a new RowSource to a second combo box using VBA, try
coding it as follows:

Me!cboBox2.RowSource = "SELECT Table1.Field1 FROM Table1 WHERE Table1.Field2
= " & VAL(Me.cboBox1.value)

hth,

 

[Van T. Dinh]

If the posted SQL is the RowSource Property in the Properties Window of the
cboBox2, then it won't work since JET doesn't know "Me". "Me" is only used
in VBA code in the context of your current Form / Report.

Try the RowSource:

SELECT Table1.Field1
FROM Table1
WHERE Table1.Field2 > = [cboBox1]

I presume you are aware that you need to use the cboBox1_AfterUpdate Event
to requery the cboBox2.

For an example of this code, see The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm