Help with "Filter On:" using a combo box.

[generallyconfused]

Hello all,

Forgive the cross post, but I orignally posted this in "Access Other Topics" when it is far more suited for this forum.

I have a form with a combo box. The combo box looks up entries from a table that is not the recordsource for the form. It grabs two pieces of criteria (the key and a descriptor) and suppresses the key (which is assigned as a foreign key to the recordsource table).

I need a way to filter through the entries using the descriptor inside the combo box (column 2), but the bound column needs to stay column 1 because it is the foregin key in the recordsource.

Is it possible to filter the database entries a form shows using an unbound column of a combo box? If so, how?

Thanks,
GC

 

[Eupher]

In general terms, yes, it is possible. However, as you describe your setup, this doesn't quite make sense to me. Unless you're duplicating the descriptor field in the form's record source, the key is what you would filter on, as that is the value that's actually being saved in your record source. I would put the code in an event procedure in the After Update event of the combo. Something like this:

Me.Filter = "[keyfield] = " & Me![combo]
Me.FilterOn = True

Ken S.

 

[generallyconfused]

It would be nice if I could filter on the key, but the first column is suppressed (ie. it's width is zero) and the key is in no way related to the displayed value (it is an autogenerated field).

So it boils down to my end user is right clicking the combo box and selecting "Filter For:". The user inputs a filter value related to the displayed value not the key. This produces the "data type mismatch in criteria expression" error becuase the displayed value is text but the key is an integer.

So now I'm looking for a way around this little dilema. The user wants to use the built in filter functions of access (and I really don't want to build a custom filter button anyway).

I realize I'll probably end up building the custom filter, but I'm still asking. Thanks for your help.

 

[Eupher]

Hmmm... what version of Access are you using? I'm using Access XP and don't have the difficulty you describe. I just tried it on a form with a combo, where the combo displays a person's name, but the bound field is a hidden auto-number, and it filtered no problem.

Ken S.

 

[JeremyNYC]

I'm not sure what your setup is, or why you would have things keyed to the non-bound bit of data in the combo box, but I have seen situations where that does make sense (though it's usually not recommeneded).

But you can get the data you need with the column property of the combo box. I just made a test form with cmbContact and txtContact, both unbound. cmbContact has two columns, contactID and ContactName. I added this to the afterUpdate event of the combo box:
Me!txtContact = Me!cmbContact.Column(1)

Columns is a zero-based array, so column(1) gets you the second column.

Hope that helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[generallyconfused]

Ok. I've created a much simpler example to test my problem. I created two tables...

Tables
Table1 - ID (Autonumber); Text1 (Text)
ID Text1
1 dcba
2 cbad
3 badc
4 abcd

Table2 - ID (Autonumber); FKID (Number); Text2 (Text)
ID FKID Text2
1 4 selected abcd
2 3 selected badc
3 2 selected cbad
4 1 selected dcba

Relationships
Table1![ID] <-Ref Int and Cascading Updates-> Table2![FKID]

Form
Form1 - Combo1 (Table1.ID; Table1.Text1) bound to Table2.FKID; Text1 bound to Table2.Text2

When I select combo1, right click, select &quot;Filter For:'abcd'&quot;, the form filters to record 1 of Table2. This is exactly how I want my form to work.

Even more perplexing, this setup is essentially how everything is built in my database (just add a whole lot more fields to the tables, and a lot more tables too).

However, when I run &quot;Filter For:&quot; on the form of my real database I receive a &quot;Datatype mismatch in criteria expression&quot; error.

MS Help says...
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.

I was making the assumption that meant the &quot;Filter For:&quot; right click selection was comparing bound columns only, so I was attempting to compare text data to integer data. Additionally, the &quot;Filter By Selection&quot; and &quot;Filter Excluding Selection&quot; work fine, which sort of supported my hypothesis. My test example does not support my hypothesis however.

So now I'm really lost lol. Anybody ever experience a problem like this? Could this be a form corruption?

Thanks in advance for any assistance.
GC

 

[lewie]

Maybe you could use a msgbox to print out what is being compared, that is how i found my error.