Multiple date query then append to table

[rmoore]

I deleted the code before uploading... I used the DB that i posted and its still not working

[rmoore]

I was thinking maybe there is a problem because the PaymentAmount in the fAddLoans form is not linked to the PaymentAmount in the tPayments table. It is linked to the PaymentAmount in the tLoans table.

Or Maybe because the LoanNumber is an Autonumber?

Not sure, maybe I'm just grasping at straws at this point

Attached is the newest DB copy with indexing changes and corrected names

[ArviLaanemets]

Attached is an example how to do all this, using saved queries and a helper table tNumbers (the table must have registered all consequent numbers at least to max possible number of payments).

There is selection of 3 queries to append monthly payments (qAppendPaymentsM1 adds payments at 1st of month, qAppendPaymentsMD adds payments at same day of month, and qAppendPaymentsMEnd adds paymens at last day of month), and a query to append daily, weekly, and be-weekly payments. All queries are checking tPayments table for earlier appended payments, so no duplicates are inserted.

User can run those queries at will from button event in form, or they can be called from some form event

[June7]

Your db and code is not working for first 2 records because there is no value in Frequency field. Select a value for record then click button. Records are then created in tPayments. For other records that already have Frequency, code works just fine.

You need to be more explicit about what you mean by 'not working'. I am guessing you mean issue is with new record. New loan record must first be committed to table before payment records can be created.

Add following line to button code just after Dim statement.

If Me.Dirty Then Me.Dirty = False

[rmoore]

Yes, I should of been more specific on what was going on... If i use the code as was, it doesn't insert a new payment record to tPayments on button click. If I close fAddLoan then open again cycle to any record previously saved and click button it will add the payment records.

The

If Me.Dirty Then Me.Dirty = False

has fixed that, Thanks

ArviL suggestion of doing it through queries as another way of doing this is possible, do you have any thoughts on which way might be better (Users have limited computer experience)?

As it stands... The PaymentAmount is not being inserted into the tPayments table. Ive added StartingBalance to the Loan table, My thinking is to use the "PaymentAmount" (Payment table) sums to calculate the remaining balance in a query. What is the best way to do it? Add "PaymentAmount" through editing the code or is somehow linking the field from the fAddLoan form a better option.

[June7]

AFAIK, this is a matter of preference.

User experience is irrelevant. Code can run query objects just as well as SQL statement in code.

I prefer to build as few query objects as possible.

If you want to include payment amount in the INSERT action, then modify SQL statement to include appropriate references.

Could pull payment amount from loan record but what if customer makes a payment different from what is scheduled? Edit the payment data at time of payment.

[rmoore]

Thanks for all the help..... After adding and tweaking I will update my progress..... Again June7 & ArviL you all have been great with the advice, suggestions and patience