Enter Parameter Value

I am struggling to modify a query to fit another combo box

My original 'row source' query works fine for my combo box 'cboContraint'

SELECT DISTINCT tblConstraints.Department, tblConstraints.Constraint
FROM tblConstraints
WHERE Department=forms!frmConstraints.cboDepartment.value;

But I created another combo box 'cboConstraint2' based on the same lookup table, only difference is that its based on another combo box 'cboDepartment2'

SELECT DISTINCT tblConstraints.Department, tblConstraints.Constraint
FROM tblConstraints
WHERE Department=forms!frmConstraints.cboDepartment2.value;


the error: Enter parameter value: forms!frmConstraints.cboDepartment2.value
It does not seem to read the parameter ''cboDepartment2'' automatically


Please Help

I would suggest separating Form control values from your VBA (and SQL) code a bit, using variables. The way you have coded it could work, but if anything goes wrong it is a nightmare to figure out at what layer the failure is at.

I would suggest something like this to read the form controls:
"Safely read form field text controls"
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

Then you can validate that you have the proper variable value prior to telling the DB to go off and run some SQL with the value of the variable.

Additionally you may use VBA debugging tools such as the Watches window and Debug.Print which sends output to the VBA Immediate window to also inspect variables.

Thank you for your reply

i have absolutely no experience with vba and sql

is there another way of explaining this? i have not used debugging tools. is it a function somewhere in access? is it a separate download?

What I want to do seems simple, not sure if i am in over my head. the idea is to: add similar combo boxes to my original ( cboDepartment which is the independent master with a slave cboConstraint which is the dependent ) ex Department2 ; Department3 ; Department4 each with their own slave. so it can be read as one record and not 4 different.

frizzel_fry said:

i have not used debugging tools. is it a function somewhere in access? is it a separate download?

Click to expand...

Yes, in the Visual Basic for Applications (VBA) screen all of those capabilities are at your disposal.

frizzel_fry said:

What I want to do seems simple, not sure if i am in over my head. the idea is to: add similar combo boxes to my original ( cboDepartment which is the independent master with a slave cboConstraint which is the dependent ) ex Department2 ; Department3 ; Department4 each with their own slave. so it can be read as one record and not 4 different.

Click to expand...

It is quite simple, yes.

Just in place of hard coding references to form controls within the SQL, prior to building the SQL I would read the form controls into variables, perform any validation required on the variables, then once sure all is in order build the SQL and tell the DB to execute it.

Example of what I am talking about... This is on a form which provides part number based searching: The last bit which "calls the Validation object to perform the query" is lingo within my architecture / design. Think of that as merely "paste in SQL here and run it!"

There are two ways I run SQL's in Access, using ADO objects or DAO objects.
1) If I need the query results in VBA variables, then I use ADO objects
2) If I need the query results in Access tables, then I use DAO objects

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149