SQL statement in Data Control RecordSource property

[vb6novice]

Form frm3Comb has 2 data controls and 2 DBCombo boxes.

DataControl1 is connected to an Access database. RecordSource is a table that lists names of classes. DBCombo1 provides the list returned by DataControl1.

DataControl2 is connected to same database. DBCombo2 displays the list from DataControl2. I want to use a SELECT statement to return a list of the names in table tblPVM where the field SSClass is equal to the value of DBCombo1.

Can't figure out how to refer to the value of DBCombo1 in the statement.

If I use the statement

SELECT tblPVM.PVMID, tblPVM.FullName, tblPVM.SSClass FROM tblPVM WHERE (((tblPVM.[SSClass]) = frm3Comb.DBCombo1)) ORDER BY tblPVM.FullName;

I get the error "Too few parameters. Expected 1."

If I change the statement to:

"SELECT tblPVM.PVMID, tblPVM.FullName, tblPVM.SSClass FROM tblPVM WHERE (((tblPVM.[SSClass]) = " & frm3Comb.DBCombo1 & ")) ORDER BY tblPVM.FullName;"

then I get the error "The Microsoft Jet database engine cannot find the input table or query..." followed by the statement.

What am I doing wrong?

 

[PaulBricker]

First try changing this
frm3Comb.DBCombo1

to this
Forms!frm3Comb!DBCombo1

using the dot . between the form and the control tells access to look for a property of the form, frm3Comb. DBCombo1 isn't a property of the Form, it's a control on the form so you need to reference it with the !
Also, unless you specifically Dim your form as a form Access isn't going to know what frm3Comb is so you identify it with the Forms qualifier.

Paul

 

[vb6novice]

Thanks Paul.

I meant to post this on the VB5 & 6 forum. Your answer is good for Access, but VB6 is giving me grief.

Thanks again.

 

[PaulBricker]

Sorry, I can't help you there. Good luck.

Paul