Form/Subform-Filter, and add two command buttons. PLEASE HELP!!!!!!

[friva345]

Business Rules:


Each Loan can have many insurance policies (fire, wind, etc) for a single customer.
The loans/policies are being audited by the financial institution for whom we service those loans.
The loans/policies have been updated to ensure compliance with financial institution via form/'subform i built for them to use for making changes.
These changes need to be reviewed by the form/sub i'm trying to build.

Issues: when navigating form, subform only shows completed records except that when you navigate to loan that is not completed, then subform shows blank rows (need to skip those that are not complete but i don't know how).
I need to add a button on the form that will populate fields in the subform (Date reviewed and reviewed by)

[orange]

Have you (or anyone else) considered a review of the current business process to review info in the subform?
You could identify records that need to be reviewed, and assign them such that 1 reviewer works with all records related to a Loan. This would eliminate potential duplicate processing of a record.

Or, you might be able to have a table with reviewers and record they are working on. You could set this up so that no 2 reviewers could be working on the same detailed record. When they hit ReviewCompleted, since it knows the current Reviewer and Record, you could update the record to show completed and update the ReviewerRecord to remove that ReviewerRecord from the table. You could only put a record in the ReviewerRecord table if the record was not Complete and the record was not already in the ReviewerRecord table (or some similar logic).

This sort of constraints (programming) could prevent:
-multiple users from working on the same record,
-working on records that are already identified Complete


When I look at the QC form, I do not see any policy records (detail records) in Completed2 Subform. Is that expected?
What exactly should
-populate the MainForm
-populate the Completed2 Subform?


You posted while I was typing.

In your Issues paragraph ---what records should be presented in the subform. It appears that Status relates to Loan and also to Policy -- seems to be a design problem raising its head.

I think you need a Loan table and a Policy table that are linked in your 1 to Many.
Are you talking about a Policy being Complete or a Loan being Complete

Your Form/subform would be linked by Access based on Loan number, and the subform data would only be for those Policies associated with the Loan in the MainForm.

When I look at the database and look for Completed Policies - that is Status Like "Complete*"
I only see these

ID	Key	Loan	Policy	Status
8852	5457122F	5457122	AOR0000057	Complete - Refund Issued
9274	5457122F	5457122	AOR0042494	Complete - Refund Issued
9571	5457122F	5457122	AOR0051579	Complete - Refund Issued
10082	5457122F	5457122	AOR0064692	Complete - Refund Issued

So it makes me think you have multiple fields/conditions to represent Completed in your
Completed2 query????
Tell me about Status and Policy_Status.

[orange]

It appears from your lack of response to my post that you have abandoned project or stopped.

A few things:
I noticed that your Policy_File has an ID field that is unique. So in my copy I made ID the PK for a new table I called Policy_File_JED. I then changed the record source of subform2 to

SELECT PolicyFile_JED.*
FROM PolicyFile_JED
WHERE (((PolicyFile_JED.Status)  Like "*Complete*"));

This is filtering so that only records with Complete in their Status field and (by proper use of Form/subform) the Policies associated with the Loan in Main form.

Only Loan 5457122 with Policies AOR0000057, AOR0042494, AOR0051579 and AOR0064692 populate the subform. All other Loans show No Policy records in the QC form.

Good luck with your project

[friva345]

I didn't abandon project. I left the office. I've been working over 20 consecutive days on various projects so I'm pretty exhausted. I'm heading out there in a bit and will take a look at the policy file jet and everything else. Today is my last attempt at this as its due tomorrow.

[ssanfu]

I played around with your project...... Actually I spent about 4 hours on it. Made lots of changes - removed most of the spaces and special characters in field names.
Fought with the data macro on a table. Not one of Microsoft's best ideas.

You MUST use the FE/BE in the attached zip file because of the changes I've made.



Good luck with your project......

[friva345]

Thank you so much. I will look at it in a couple of hours when I get to the office. I really appreciate your efforts.

[friva345]

I am trying to relink the front end to our backend which has been updated. the sample i posted only had a few records. actual db has 16k records. can i ask why the name was broken by first, middle, last? some names are institutions with several words making up the name.

[June7]

Name parts of people are customarily in separate fields to assure consistency and facilitate sorting/filtering. So that some entries are not FirstMiddleLast and others Last,FirstMiddle.

Mixing people and institutions does present a quandary.