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

[friva345]

Hi Everyone,
I'm new to this forum and i would really appreciate some of your expertise. I'm intermediate level access except in forms. So I built this form/subform based off two tables (loans and policy_file, 1 to many, respectively) See attached image of form. The form table (Loans) has one record per loan. The subform table (Policy_File) has multiple records (policies) per loan number. The form works fine and they're updating the records and selecting a status once they work the record.


Now i'm replicating that form/subform to use it as a review form for the records already worked. Once reviewed, the reviewer selects his initials from drop down, clicks the review button.

• Subform needs to only show policies marked with one of five completion codes (Complete - Refund Issued, No Refund Due - Complete, Loan PIF – New coverage amount higher, and so on).
• Some loans may have only 4 policies completed out of 5 so we only want to display the 4 policies since policy 5 still needs to be worked.
• Reviewed by drop down in the attached image will be pulling from the User_Name table.
• "Reviewed" Button on image can only be enabled if the reviewer has selected his initials from drop down. On click, the button will populate the Reviewed by field in the Policy_File table (subform), save the records, and move to the next sequential record (next loan).
• I have also attached an image of the table relations

Relationships.zip
changes 9.5.15forum.zip
Please help! I must complete this during the weekend so that it can be ready first thing tuesday am. Thanks in advance for your assistance. This is greatly appretiated.

[June7]

Not understanding table Completed2. Why is it linked to Loan table but has Policy field? What is being completed - Loan or Policy? What is relationship type?

[friva345]

Completed2 is a query that brings all the fields from policy file table but it filters for loans that completed. here's the SQL for the query:
SELECTPolicy_File.Loan, Policy_File.[Source Description], Policy_File.Policy, Policy_File.Cov, Policy_File.[Prop Type], Policy_File.[Coll Code], Policy_File.[Eff Date], Policy_File.[Exp Date], Policy_File.[Cov Amt], Policy_File.Policy_Status, Policy_File.[Doc #], Policy_File.FHCF, Policy_File.[Reason Code], Policy_File.[User Error Comments], Policy_File.[LP Policy Prior], Policy_File.[Revised Coverage Amount], Policy_File.[Coverage Methodology Change], Policy_File.[Debit Amount], Policy_File.[Credit Amount], Policy_File.[Refund Amount], Policy_File.[Refund Date], Policy_File.[New Poliy Number], Policy_File.[Modified By], Policy_File.Status, Policy_File.[Reviewed By], Policy_File.[Date Reviewed]


FROM Policy_File


WHERE (((Policy_File.Status) Like "*Comp*" Or (Policy_File.Status) Like "Loan*" Or (Policy_File.Status) Like "*$10k*") AND ((Policy_File.[Reviewed By]) Is Null) AND ((Policy_File.[Date Reviewed]) Is Null))
ORDER BY Policy_File.Sort;

Each loan's policies (1-many) are worked by selecting one of the Statuses from the Status table. When they work the policies, they pull a loan# and go through each policy and try to come up with a resolution (these are loans that are being disputed by customer).
So the only table that gets updated is the Policy_File table. The Loans table is left intact. There are several fields that the employee can tab into and update but the main field that determines if the policies are ready for review is the Status field.

[June7]

Don't think I've ever seen anyone include queries in the Relationships builder. Really not necessary.

Usually a form can be used to enter/edit data for one table.

These tables have a master/dependent (1 to many) relationship. Should use a form/subform arrangement for data entry/edit.

[friva345]

I already have the form/subform structure. The problem is that I can't figure out a way to filter the records that are displayed on the subform. I only want to see completed records for the reviewers to check. I also can't figure out how to make the button on the form populate the various records on subform belonging to the one record on form 1-many. in other words, can the child table control the parent table in a form/subform design.

[June7]

No, child does not dictate to parent.

What determines that subform record is 'completed'? Apply static filter criteria in the RecordSource.

Subform container control Master/Child Links properties will synchronize related records.

[friva345]

The status field in the subform determines if record is complete. So there are ten status that can be selected, five of which are completed. So the subform table(policy_file) contains the field with the completed codes.
Where would I apply the filter? In a query or on the firm?

[orange]

the field with the completed codes.

Are you saying that status is a multi-valued field?
Agree with June, apply a static filter to the recordsource. In fact I would try setting up a specific filter to restrict the subform to only a few records -- just to get some handle on what is happening. That is, rather than solving the big issue in one go, I would try to focus on how the form and subform are interacting by means of a few records. Once you get a clear picture of the actual interaction, you can adjust with some designed steps.

Further, if you have a table with discrete statuses ("ten status that can be selected",) -- why are you using "Like..."? It seems these statuses should be populated with some value in your status table, if the design and relationships are well designed.

Also, I see some spelling issues in the SQL which are suspect -- the SQL must have been written/copied rather than cut and paste???
-SELECTPol
-Policy_File.[New Poliy Number]

[friva345]

One of the attachments shows a fully functional form/subform with the changes I need. The status is a multifield that is a selected from drop down. I im not good with forms so I'm confused with what you said about applying a static filter to source. Is that something I do in the forms macros or is it done on the table itself or do I use a query as the subform source. And what's the code to make the button and drop down work as described in my original post.
Right now my form/subform works very nice. Now we want to review the work done and only see completed records. The subform contains the field with the completed statuses. How can we use the same form subform that will only show completed records. Again, I'm not sure what you mean by static filter guys. Where and how do I apply the static filter.
The form subform shown in attachment is built off the two tables described in original post. The query was an attempt at building the same form subform but using a query for the subform section. And that's why the query is also in the relationships section. I could attach the database if need be.

[orange]

I do not use macros, multi-valued fields nor lookups at the table field level.

By static filter, I meant to restrict your mainform to only 1 record, then you have only a few records in your subform. With vba, (I don't use macros) you could step through the code and verify/monitor the flow and the values in fields/controls/variables. This would help you identify the problem area abd try some corrective action.

I would have expected a tblStatus with 10 records based on your earlier post. The fields would be something like:

StatusId autonumber PK
StatusName text
StatusDesc text (may not be necessary)

With this setup, you could include tblStatus in your relationships and use StatusID Long FK in the related table.
This would replace the use of Like.....

If you would post your db that could be helpful. Also, if you have any specific instructions to highlight the problem you are having would be helpful. Oh yes, a description of the "business involved" -- what exactly is going on -in plain English. You know your application much better than any reader.

Good luck with your project.

[friva345]

Frontend.zipBackend.zipI'm attaching DB. Please open Frontend DB.
Form: QC
Table: Loans
Subform: Completed2subform
Query: Completed2

Loans table has one row per loan. Completed2 query has many rows per loan since each loan can have multiple policies which some have been updated and now need to be reviewed.
Completed records are identified by having a status of Complete - Refund Issued OR No Refund Due - Complete OR Loan PIF – New coverage amount higher OR Less than $10k - Adjusted Going Forward OR Less than $10k - No Adjustment (see Status table).

QC form now shows only the completed records in the subform but when i'm navigating through the records, if the form brings up a loan that hasn't been completed then sub shows blank record so instead of navigating through 5,000 completed records, the form goes through all 16,000 records.

Can someone please help with the following:

Have a drop down on the QC form named "Reviewed by" that selects the reviewers initials from the "Reviewed_by_lookup" table

Add a button on the QC form named "Reviewed" that once they review the record, they click the button and their initials selected on the form's Reviewed by drop down will populate the records reviewed (completed) on the subform and enter Date() on the Date reviewed on subform and their initials on the Reviewed by field on sub, as well as navigate to the next completed record (skipping all the incompletes)

Have access always lock the record employee1 is reviewing so that employee2 can hit reviewed button and be taken to the next complete and available record for review

[orange]

??? The tables are not set up 1 to many as per your earlier post?
I see 22 loans and 22 policy.
What does Completed2 mean? I know it's a query, but what is it in your Business. Relationships should show all your tables and relationships (at least to show us your database in support of your business).
Your tables are not well structured and are not making use of the inherent parts of relational database.

Some of your tables do not have primary keys so preclude proper joins -- in my view)
Status
Reason Code
Reviewed ByLookup
UserName

What is LP Policy Prior?
What is Cov_method_Change?

I have a suspicion that the Policy_File is masking other entities. (probably other relavant entities within this table)

[friva345]

The 1-Many relationship is between the loans table and the policy file table. Loans table has less than 22 records.
the completed2 query I created so that I could filter the subform by only completed records.
I apologize for the database structure but i'm not an expert in access.
The LP policy prior is just a True/False field they select while working the records and the Cov_Method_Change is used as a drop down field in the Policy_File table.

[orange]

What is this application? Is this really an operational system, or is it something you're trying to develop?

You are using Access, but not as relational database as intended.
Your deadline, whether real or self-imposed, is not helping your learning exercise.

I don't know your business, but I can tell you that every table should have Primary key. The purpose of the pK is to provide unique identification for each record in each table.

I really have no idea what the Policy File table is about --seems like several different concepts in my view.
Access can ensure referential integrity if you design and use it as intended.

Your ReasonCode table would be better for your purposes if designed along this line.

ReasonCodeID autonumber PK
ReasonCode text

then Access would assign the ReasonCodeID and would use it to establish Foreign Key in related table(s).
This would simplify things and would ensure consistent spelling of ReasonCodes wherever used.
Same for Status, Reviewed ByLookup, UserName

Your database design should really be based on your Business Rules/Facts. And readers have not seen any of these.

Do you have a list of rules, along this line:

Customers have 1 or Many Loans
A Loan can be associated with 1 or Many Policy(ies)

Why is a loan reviewed?
Is there some mechanism for assigning Reviewer to a Loan?
Is there some number of things a Reviewer checks?
Is there some progression as to the Status that a Review can go through?

I don't know what to advise you. In my view you need a business overview such that anyone offering assistance can understand what you are trying to do. Based on the business overview you would design the database and set up some test cases and test data and work it against the "evolving model". You reconcile every "issue" that arises - data, scenario, relationship, attribute/field. Then retest your scenarios. Once all your test scenarios were successful, you now have a database to support your "business". Now, you can work on forms, queries, reports knowing that the underlying database structure matches your data requirements.
But, you have a Tuesday AM deadline....

[friva345]

I couldn't agree more. This project started 3 weeks ago and I was asked on a Friday to build a database and I was given an excel file with two tables. it was to create a form/sub where emp could look at each loan and update the fields of the policies where applicable. I was given a deadline of monday. now the executive who gave me this assignment with insane deadlines, called me friday and told me he needed a db to review the work done thus far in the policy_file table.
The QC form is the one to review and where i'm having some problems. i know the db is far from perfect but it's as good as my expertise in access, unfortunately. So if anyone could help me figure out what i described above:

Have a drop down on the QC form named "Reviewed by" that selects the reviewers initials from the "Reviewed_by_lookup" table

Add a button on the QC form named "Reviewed" that once they review the record, they click the button and their initials selected on the form's Reviewed by drop down will populate the records reviewed (completed) on the subform and enter Date() on the Date reviewed on subform and their initials on the Reviewed by field on sub, as well as navigate to the next completed record (skipping all the incompletes)

Have access always lock the record employee1 is reviewing so that employee2 can hit reviewed button and be taken to the next complete and available record for review

with the current structure of the db that would be awesome.