Speculate why macro not working completely

[kwelch]

I am using several ApplyFilter macros in a split form that is based on a query. They are working quite well except for a peculiar error in how one of them works.

I have a database of patients, and one of the fields is the diagnosis. You can see the list of patients in tbl_PatientsRecord below. Direct your attention to the patient highlighted in blue with the DiagnosisCategory of Tumor.



That value of "Tumor" is pulled from a table called tbl_DiagnosisCategory as seen below.



And you can see that after I enter the data through a forum, the two patients are correctly assigned to their respective categories.





I made a simple query that pulls data from tbl_PatientsRecord as well as another table (tbl_Surgeries) and set up a bunch of ApplyFilter macros. You can see the layout here in the split form:



When I select the Diagnosis Category combo box to select the various diagnoses, it pulls from tbl_DiagnosisCategory and then through a macro, filters the query/split form. This works for the most part with all the specified combo boxes you see. However, if I select CRSwNP as a diagnosis, you can see that after the update, there are two patients with two different DiagnosisCategory fields. The single patient with a DiagnosisCategory of "CRSwNP" shows up, but so does this patient with DiagnosisCategory of "Tumor."

If I select "Tumor" from this combo box, only the single patient with DiagnosisCategory of "Tumor" is shown.

The row source for the combo box is

SELECT [tbl_DiagnosisCategory].[DiagnosisCategoryID], [tbl_DiagnosisCategory].[DiagnosisCategory] FROM tbl_DiagnosisCategory ORDER BY [DiagnosisCategory]; 

Here is the actual macro:



The applyfilter macro uses the same syntax for all other combo boxes on this split form query, and so far, all of them seem to work...so far.

Thoughts on what may be causing this error? BTW, the original table tbl_PatientsRecord uses a "lookup" under the field DiagnosisCategory to get the data from tbl_DiagnosisCategory. (I realize using lookups at the table level is not preferred, but I thought I'd throw that one out there just in case it could be a possible cause of this error.)

Could it be that I have too many fields named "DiagnosisCategory" despite the fact that they are in different tables?

Thanks!

[ssanfu]

Let me preface this by saying: I NEVER use Look-up FIELDS in tables. They hide what is actually being stored. (I also never use macros or split forms).

What I would start troubleshooting:
On a COPY of the dB, open the table in design view, click on the "Lookup" tab and change the "Combo box" to "Text Box" to remove the Lookup.
Save the table.
Open the table in datasheet view and find the patient that had "Tumor" in the "DiagnosisCategory" of the split form and see if 25 is in the "DiagnosisCategory" field.
Then find the patient that had "CRSwNP" in the "DiagnosisCategory" of the split form and see if the data is 5.

Close the table and open the split form and filter it, trying to replicate the error......

[kwelch]

OK. I understand -- no lookups! I really intend on doing this in the future. BUT -- is that the cause of the error????

Now, when I changed combo box to text box, I see 25 for the patient who had "Tumor" and 5 for the patient who had "CRSwNP" when looking at tbl_PatientsRecord. When I run Query qry_sortbydate, I see the appropriate names listed with DiagnosisCategory of 5 and 25, as they should be. Now, when I run the split form, I still see the two patients (CRSwNP, Tumor) listed when I use the filter for "CRSwNP," but only the Tumor patient when I select "Tumor."

I added a new combo box to the split form, and I used the ID value of tbl_DiagnosisCategory, and when I look up "5" (CRSwNP), it still shows both 5 and 25.

[Micron]

BUT -- is that the cause of the error????

Most likely. Here's why you should not use lookup fields: http://access.mvps.org/access/lookupfields.htm
If you get to reason #2 and a little light doesn't turn on in your head, start over until it does.

[kwelch]

Most likely. Here's why you should not use lookup fields: http://access.mvps.org/access/lookupfields.htm
If you get to reason #2 and a little light doesn't turn on in your head, start over until it does.

Good reference.

Some follow-up: I duplicated the database and deleted all those fields in tbl_PatientsRecords that used lookup fields and recreated them as text fields. I then reconstructed my form used to input patient data and query, constructed a new split form and generated some new ApplyFilter macros...they worked!

I have some work to do .... <grumble, grumble>

[Micron]

but do you grasp the reason for the failure?

[kwelch]

I believe so - still learning concepts here.