Help with Form

[June7]

Okay, I get the idea but not the table structure. Current arrangement would mean creating records in the two reconciliation tables.

Will T_FeeSettlementReconciling2 and T_MonthlyTotals2 each have unique record for receipt number?

[Ray67]

Not all payments in T_MonthlyTotals2 will have a receipt number. Reason is because most of the payments come in a lockbox and there's isn't a receipt for it. Not sure if this answer your questions. Both tables have there own auto number.

Thank you

[June7]

You want:

Now i would like for some off the data to be transfer from F_ReconciliationList to F_Fee Settlement Reconciling once the form opens Provider name, Date, Receipt number and Amount.

If there is no receipt number and field is left blank, associating payments with reconciliation seems more complicated. The identifying criteria would be combination of Provider, Date, Amount, and that assumes provider doesn't make multiple payments of same amount on same date.

I have made payments by mailed check or cc auth and business has followed up by sending me a receipt. They apparently want a receipt associated with every payment, regardless of method.

[Ray67]

Yes you are right every payment should have a receipt. But some payments go to a lock box which don't have a receipt number. Take a look at T_Source of Payments this table tells me where all payments come from. I have another db that stores all payments that come in from lock box with there own batch number. All payments that would be put in this new db would be summary of payments. I would also need to transfer Source of payment to new form.

[Ray67]

Can it be done???? I just need this part to complete.


Thank you

[June7]

Consider the attached revision of your project. Highlights of changes:

1. renamed field Provider Name to ProviderID in T_Fee Settlement Reconciling and T_MonthlyTotals. Modifed queries and forms as needed to handle the name change. Also removed the lookups from those fields in tables because were annoying me.

2. removed default 0 for all number fields. In Access 2007/2010 number fields no longer have default of 0 when field is created - a good thing.

3. removed all spaces and special characters from field names after I discovered an extra space in Cash Cleared name.

4. set some fields in T_Fee Settlement Reconciling as required.

5. don't know what MonthOf_F_S field is for so I have it populating with current date.

6. built a new query Q_MonthlyTotals2 that is used by code to automate record generation.

7. new VBA event procedures behind F_Fee Settlement Reconciling and F_ReconciliationList.

[Ray67]

Thank you very much. It works great. Just one question, Why does F_Fee Settlement Reconciling only open's when there is a receipt number?

[June7]

Receipt number is criteria I used to determine if the record has already been reconciled so records are not duplicated in the reconciliation. Without receipt number what unique identifier can be used? A combination of date, provider, month of F_S? Maybe the primary key of T_MonthlyTotals2 could serve that purpose. Would need corresponding field in T_FeeSettlementReconciling2. Look at the query parameters and the VBA code. Modify as you need to.

[Ray67]

The only other unique identifer is ID number on T_MonthlyTotals2. Can i use id number???

[June7]

That was suggested in my previous post.