Trying something NEW to me, Auto Updating membership renewal fees, prior to running Invoices.

[RLehrbass]

Moderator,
1) tblPaymentMethods can be added to by user to add new methods as they become available. it has the following fields: PaymentMethodID(PK) and PaymentMethod.

tblPaymentInfo stores the PaymentMethodId and is relate to tblPaymentMethod with a join type 1. PaymentMethodID (5) = Applied Fees/Dues.

tblTransaction stores PaymentInfoID to identify the details of the transaction, ei: TransDate, CheckNumber, CreditCardNumber, CCVC#, ConfirmationNumber, and PaymentMethodID...

any new transaction need to record the PaymentMethodId in tblPaymentInfo to identify the PaymentMethod selected.

2) Because of economics today, we do sometimes allow members to make partial payments on agreement. Dues are annual, but some renewal dates are based on join date or business type.



3) I am not recording a payment or Credit, I am creating a debit by applying membership dues to a members account. That is why I am invoicing the member.

4) Yes, I need to increase the renewalDate by 1 year at the end of the transaction.

I use this code to accomplish that in my frmTransationInput : [tlbMember]![RenewalDate] = DateAdd("yyyy", 1, CRD) where 'CRD = (Dim CRD As Date 'Current Renewal Date); (CRD = DLookup("RenewalDate", "tblMember)) but am not sure how this would work with this new code...

[June7]

1. understood

2. Does tblTransaction have both billing and payment entries? Still trying to understand relationship between tblTransaction and tblPaymentInfo.

3. Then why do you need to create record in tblPaymentInfo at the time of billing? From post 9:
Assign the value (5) to [tblPaymentInfo]![PaymentMethod] and Assign the [ID] created on this line in [tblPaymentInfo] to [tblTransactions]![PaymentMethodID] '(5 = Applied Dues/Fees)

4. CurrentDb.Execute "UPDATE tblMemberInfo SET RenewalDate=DateAdd("yyyy", 1, [RenewalDate]) WHERE RenewalDate>=Date()"

[RLehrbass]

2)
tblTransactions fields: ID(PK-Autonumber); MemberID; TransDate; Debit; Credit; PaymentInfoID.

tblPaymentInfo fields: ID(PK-Autonumber); PaymentMethodID; CheckNumber; CardHoldersName; CreditCardNumber; CreditCardExpDate; CreditCardAuthorizationNumber; ConformationNumber.

tblPaymentMethod fields; ID(PK-Autonumber); PaymentMethod

tblTransaction stores PaymentInfoID; tblPaymentInfo stores PaymentMethodID.; PaymentMethodID says what method was used for the transaction. IE: Cash, Check, CreditCard, PayPal, Applied Fees/Dues, Other. This table can be added to if a new methode is adopted such as automatic bank withdrawl, or ApplePay etc... When a transaction is made, tblPaymentInfo needs to record what method was used to process that payment. they are all related in series, ID to xxxxID by a type 1 join (when both are equal).

3) 5 = Applied Fees/Dues. This shows up on queries and reports identifying the Payment details. EI: Members Name, Transaction Date, credit amount, Debit Amount, Payment Method, and the rest of the payment details...

4) Does this code go before or after the Wend?

[June7]

2. & 3. Still don't understand why you want record in tblPaymentInfo at the time billing is processed.

4. after

[RLehrbass]

2-3) to show what the debit is from.
I hope this will help you understand this DB.

On my form Members, I have a sub-form view that show each transaction applied to your account, both payment received and Debit applied. I can see in 1 view, all your member info and your transaction history. Upon bringing up your Members information via frmMember and performing a search for your name. I can easily see all the necessary data. I can then say to you, "Mr. Moderator, I see that you made a $100.00 payment on 8/15/14 by credit card ending in 3456 and we applied Fees/Dues on 8/3/15. Your Renewal Date was 8/1/15 and we show your account balance is currently at $100.00." Both these bolded fields are derived from the [tblPaymentInfo].[PaymentMethodID].

[RLehrbass]

Perhaps the term APPLIED is the confusing factor. I can change this word to read something like: Debit-Fees/Dues.

[June7]

I understand viewing data on form/subform.

What I don't understand is creating a record in tblPaymentInfo when payment has not be received.

You want a process to automatically process monthly fee charges and send billing to members. I don't understand why at the same time billing transaction record is created you want to also create record in tblPaymentInfo. Payment has not been received, why should there be a record?

[RLehrbass]

Getting: Compile error- Syntax error. on the line after Wend

Private Sub Report_Load()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblMember WHERE RenewalDate <= Date();")
While Not rs.EOF
CurrentDb.Execute "INSERT INTO tblTransaction(MemberID, TransDate, Debit, PaymentMethodID) VALUES(" & rs!ID & ", #" & Date & "#," & rs!TotalDues & ", 5)"
rs.MoveNext
Wend
CurrentDb.Execute "UPDATE tblMemberInfo SET RenewalDate=DateAdd("yyyy", 1, [RenewalDate]) WHERE RenewalDate>=Date()"


DoCmd.Requery ("qryTransactionBalance") 'I get my Invoice report's balance values from here.

End Sub

[RLehrbass]

I am not creating a payment receipt.
I am creating a record of the debit. I Actually adding a debit to the record that the payment will cover.
We need to know why a payment is being received, if we don't have record that a debit occurred why are you making a payment?

[June7]

Sorry, try:

CurrentDb.Execute "UPDATE tblMemberInfo SET RenewalDate=DateAdd('yyyy', 1, [RenewalDate]) WHERE RenewalDate>=Date()"

Then what does this statement mean:
Assign the value (5) to [tblPaymentInfo]![PaymentMethod] and Assign the [ID] created on this line in [tblPaymentInfo] to [tblTransactions]![PaymentMethodID] '(5 = Applied Dues/Fees)

[RLehrbass]

This is where I assign the PaymentMethod "Assign Fees/Dues" we have been discussing in the past several reply's above.

Assign the value (5) to [tblPaymentInfo]![PaymentMethodID] "this will create an auto-number for [tblPaymentInfo]![ID] for this new record."
I need the AutoNumber [ID] generated from that action to be assigned to: [tblTransactions]![PaymentMethodID].

I found that you had added a value of 5 in the "Insert Into" code line above. That should have been this auto-numbered [ID] placed there, not the value (5).

[June7]

I edited that post (#15), probably after you read it, to change the 5 to DMax() expression and also to show the SQL action for saving a record to tblPaymentInfo and some other comments. Might read it again.

I am still lost as to why you want to create a record in tblPaymentInfo at this time since no payment has been received for this newly created billing record. But as long as you know what's going on, so be it.

[RLehrbass]

Moderator,
Code is working great.
Thank you again for your assistance.
You have helped me so much!
I appreciate you!