Problem with reference to another form control

[JeremyI]

The form I am working on at the moment is designed to allow a data entry person to add new questions for the assessment of some properties. I am trying to set up cascading combo boxes that force the user to specify a category of assessment ("Management" or "Building"), and then choose a section/question type from within the relevant category, before they can save a new record.

However, when the form loads, I receive the error "Enter Parameter Value: Me!cboMgtBldg".

The row source for cboAssessmentSection is the following query:

SELECT tblAssessmentTypes.AssessmentTypeIndex, tblAssessmentTypes.AssessmentTypeName
FROM tblAssessmentTypes
WHERE (((tblAssessmentTypes.AssessmentMgtBldgIndex)=[Me]![cboMgtBldg]))
ORDER BY tblAssessmentTypes.AssessmentTypeOrder;

I've had the same problems in similar situations using this machine. Sort of an Access newbie... but all the posts I've seen on this sort of technique make it seem straightforward and easy, so either I've missed a critical step, or perhaps something is missing from the local copy of Access. Thanks for any help!

ETA: Sorry, I forgot--this is Access 2003, but the database window says "(Access 2000 file format)".

[MikeTheBike]

Hi

I assume that the combo data source is a stored query, and the query posted is copied from the query designer SQL view.

If that is the case you need to reference the form explicitly (ie. not with the 'Me' Object) ie.

SELECT tblAssessmentTypes.AssessmentTypeIndex, tblAssessmentTypes.AssessmentTypeName 
FROM tblAssessmentTypes 
WHERE (((tblAssessmentTypes.AssessmentMgtBldgIndex)=[Forms]![YourFormName]![cboMgtBldg])) 
ORDER BY tblAssessmentTypes.AssessmentTypeOrder; 

(this assumes AssessmentMgtBldgIndex is numeric)

Note: If you construct the Combo Data Source in VB code then you would concatenate the criteria value into the string (when the Me object could be used - or implied - as desired).

HTH


MTB

[JeremyI]

Thanks, Mike. No, sorry to be unclear, but it is not a stored query; just the SQL statement stored in the Row Source property. AssessmentMgtBldgIndex is indeed numeric.

I've been playing around with this problem in the meantime, though, and the method of referring to the exact form name does seem to work, with the addition of code to requery the dependent combobox at appropriate times. If the exact form name is the best option, that won't be a problem here or in other simple spots. I guess I am really trying to figure out and practice how and when "Me!" can be used, because I think I'll need it for some of the more complicated forms in the database.

(For some reason, I also couldn't adapt the VBA from the Cascading Combo/List Boxes tutorial, which does use "Me!" programmatically. The error disappeared but so did the combobox options!)

Are there any circumstances in which it would work to use "Me!" in the rowsource SQL of a combobox on a form?

[MikeTheBike]

Hi

Yes, if you have typed the SQL into the CoboBox RowSource in design view then this is prctically the same as specifying a stored query in the RowSource, hence the need for explicit reference to the form.

To use th Me object you need to construct the SQL in code and then assign it to the row source, ie.

dim sql as strong
 
sql = "SELECT tblAssessmentTypes.AssessmentTypeIndex, "
sql = sql & "tblAssessmentTypes.AssessmentTypeName  "
sql = sql & "FROM tblAssessmentTypes  "
sql = sql & "WHERE (((tblAssessmentTypes.AssessmentMgtBldgIndex)=" & Me.cboMgtBldg & "))  "
sql = sql & "ORDER BY tblAssessmentTypes.AssessmentTypeOrder;"
 
Me.ComboBox.RowSource = sql
 
Me.ComboBox.Requry

You will need to assign the sql and requery each time cboMgtBldg is changed/updated.

(In fact you do not have to use the Me object as this is the default object and is automatically implied.)

HTH


MTB

[JeremyI]

Oh, right. Good stuff to know. Thanks very much, Mike!