X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Is It Possible To Perform Search-filter On Calculated Fields Of A Continuousform, Access 2016    
post Mar 27 2020, 09:49 AM

Posts: 89
Joined: 19-February 20
From: Mumbai, India

For a Continuous Form (pls see the attached image) I am able to create combobox filters when the field is directly in the Form's RecordSource table. But I have a challenge in creating Filter ComboBox when the field is a calculated field. So my problem here is that the filter works but the filter ComboBox does not display correctly. Here are some details.

The form's RecordSource table is: InterviewList. The Fields are IntID, RRID, and some others as seen in the image.
There is another table called RecrReqn. The fields are RRID, Client Name, JobRole and some other fields.

Using a field RRID as connecting field I can get the Client name and the Job Role from the RecrReqn table. As seen in the image, I have used [cmbRRID].[Column](3) method to display the Client name and similarly JobRole.

Now to filter the Client, a simple method is to use RRID as the filter. But the Recruitment dept needs to display the Client name and the Job Role AND be able to filter using Client Name.

For the Filter Combobox (the first filter in the image) I used the Row Source query which is an Inner Join of two tables(the SQL is given below).
SELECT DISTINCT InterviewList.RRID, RecrReqn.Client, RecrReqn.JobRole
FROM InterviewList INNER JOIN RecrReqn ON (InterviewList.RRID = RecrReqn.RRID) AND (InterviewList.RRID = RecrReqn.RRID);

The result of this query when viewing in datasheet mode is fine - as required. The query results in a table having three fields. Hence, I have kept the properties of the ComboBox Filter as follows
ColumnCount = 3
Column Widths = 1cm;3cm;3cm
List Width = 7cm
Bound Column = 1

So I am expecting the drop down to display the three Columns:

RRID | Client Name | JobRole

However, it displays only RRID. The other two columns are blank. So although the filter works but the filter Combobox dropdown does not display the Client name and Job role. Any idea where I am going wrong?

Attached File(s)
Attached File  Search_on_Calculated_fields.jpg ( 200.93K )Number of downloads: 12
Go to the top of the page
post Mar 27 2020, 12:47 PM

Posts: 1,512
Joined: 25-January 16
From: The Great Land

All 3 columns should display in dropdown.

However, normally the ID field would be 0 width so it is hidden. This will cause the next field value to display in box. User types client name to make selection but code would use ID value to filter. No need for a textbox to display client name.

Column index begins with 0 so column 1 is index 0. There is no index 3 for your combobox.

If you want to provide db for analysis, follow instructions at bottom of my post.

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
post Mar 29 2020, 11:51 PM

Posts: 54
Joined: 8-May 19
From: USA

And BTW probably no big deal but this seems a little redundant:
(InterviewList.RRID = RecrReqn.RRID) AND (InterviewList.RRID = RecrReqn.RRID)

This post has been edited by Bullschmidt: Mar 29 2020, 11:51 PM

J. Paul Schmidt - Freelance Web and Database Developer
Sample Database on the Web
Sample Access Database

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    3rd July 2020 - 09:31 AM