Combo Filter Not Working 1

[LarryDeLaruelle]

I have a combo control on a form that I want to filter based on a value in another form.

The main form allows the user to select a record and open an edit form for that record and also contains the value for the filter. A combo on the edit form needs to be filtered for values appropriate to the type of record displayed for edit.

The filtered combo is bound to a control on the edit form.

I set the combo's filter in the Row Source using the Expression Builder and came up with this syntax:

[Forms]![frmName]![cboName]

When I click on the combo's drop down I get a parameter prompt popup. Its as if Access does not recognize the reference to the form.

I checked my references thinking that could be the problem but they are set as I have other db's set which use this same type of process without a problem. The references are in order:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
OLE Automation
Utility

I'm using Access 2K.

I've also tried to set the filter directly in the query that is the row source for the combo with the same result; the odd thing is, if I have the main form open and run the query directly (with the filter set) it produces the correct filtered list.

Any help would be greatly appreciated.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[willir]

Larry

A parameter prompt usually means that there is a typo - it does not have to be the name of a field - it could be a syntax error. Access has to be able to "see" the field(s) or it assumes the unknown is a parameter. One gotcha is that your target form does not have all the fields in it.

Open up the target form.

Grab the SQL statement for your combo box and put it in a query. I find it is easier to trouble shoot SQL statemnets with the query builder than in a combo box.

Richard

 

[LarryDeLaruelle]

Richard:

Thanks for the response.

I did as you suggested and the query ran fine. (Had the main form open in view and copied the edit form combo box's row source SQL into a new query's SQL view.)

If I open the combo's row source in query edit and run it, it works fine also.

If I use the Expression Builder to set the criteria in the source query it works fine.

I copied and pasted the criteria in the VB Editor Immediate Window and it returns the correct value.

The only time it burps is when I try to open the edit form's combo box by clicking on the drop down. It's as if the edit form's combo can't 'see' the main form. Is that possible? If so, what would cause it and what would be the cure?

I have a similar arrangement on the main form: a primary selection combo box is used as the filter criteria for a set of other combo boxes and those work fine.

I have the feeling that I am missing something simple but what that might be totally escapes me.

This is driving me nuts. If I weren't already bald, I'd be pulling out my hair.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LarryDeLaruelle]

Richard:

Here's a twist.

I plugged in a message box in the edit form's On Open event:

MsgBox "The value of the filter is: " & [Forms]![frmEditMaster]![cboSelect]

When I open the edit form I get an Error 13; Type Mismatch.

Any idea why this would occur on the form when I can run the SQL without a problem?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[willir]

Mismatch would mean tryig to use one variable type for another. Typically...
Using a numeric or date field to accept text entry

There are a few gotchas with combo boxes. One point is that a combo box can use a SELECT clause to retrieve multiple fields / columns, and may display a different columnt than the primary key depending on the setup.

Example:[tt]
SELECT EmpID, EmpLN, EmpFN from tblEmployee[/tt]

Three columns - 0, 1 and 2
Data types are numeric, text, text

Column width set to 0";1";1"
The first column is bound.

Because of the column width for first column set to 0", it is hidden. The combo box displays the second column - employee's last name.

Take this further...
Somewhere else in your code, you set the value of the combo box to "Smith" - now you have a conflict - the bound column is set to use EmpID, a numeric value, but you have passed a string or text value as the first column.

Does this shed some light?
Richrd

 

[LarryDeLaruelle]

Richard:

I think I have it resolved.

I created a new, unbound form and added a combo box to it based on the same underlying query and set the criteria using Expression Builder. Worked fine.

Created a second, bound form similar to my original, added the combo box and set the criteria using the Expression Builder. Again, it worked fine.

My conclusion is that there is something peculiar with the first form and you were correct that it is not 'seeing' the main form where the criteria resides.

Its a moot point now that I have a working form, but I am curious why that one form would not recognize that another form was open.

Oh well, that what makes this business interesting.

Thanks again for your help.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org