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

[RLehrbass]

I am running my [rptInvoiceBalDue]. (currently running very nicely) I will run this monthly!
Before I do this, I need to update all the member's Balance Due if the members renewal date has passed, so I am invoicing the correct amount.

I am setting up the VBA code (on Load) to search [tblMember].

I've never done a entire table search, only coded from an open form...So I am not sure how to assign items from or to a specific member ID when I don't have a Me! to take values from. I don't know if there is a better way or another approach but... I am trying to learn correctly.

So,


I need it to find each record where today's date is equal or Greater then the [RenewalDate]
add the value of [TotalDues] to [tblTransaction]![Debit];
Assign Date() to [tblTransaction]![TransDate];
Assign [tblMember]![ID] to [tblTransaction]![MemberID];
Assign the value (5) to [tblPaymentInfo]![PaymentMethod]

I already have a form [frmTransactionInput] I use that has all the fields I need to complete all these tasks.

The code I am thinking is: (I am not sure of code with * before it)

Private Sub

Dim CRD As Date 'Current Renewal Date
Dim dblTotalDues As Double
* dblTotalDues = DLookup("TotalDues", "tblMember) ' ", "ID = " & tblMember!ID) How do I deal with the MemberID?
* CRD = DLookup("RenewalDate", "tblMember) ' ", "ID = " & tblMember!ID)

If Date >= CRD Then
DoCmd.OpenForm ("frmTransactionInput") "can this can be done Non-Visable
* Me!MemberID = 'ID from tblMember, do I need to DIM this value???
Me!TransDate = Date()
Me!Debit = dblTotalDues
Me!PaymentMethodID = 5
DoCmd.close
* tlbMember!RenewalDate = DateAdd("yyyy", 1, CRD) 'Increases Members Renewal Date by one year

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

End Sub

[LaughingBull]

Select Case Date() >= [RenewalDate]
Case x = [Total!Dues] + [tbl!Transaction]![Debit]
Case x = [tblTransaction]![TransDate] = Date()
Case x = [tblTransaction]![MemberID] = [tblMember]![ID]
Case x = [tblPaymentInfo]![PaymentMethod] = value (5)
End Select

[RLehrbass]

LaughingBull,
I think you are saying it should look something like below. It appears that the case statement will go record by record, inputting each of the the case x information as it locates a record meeting the select case statement. But I do not understand how the code begins, as to actually start the searching of the [tblMember] records and advance through the records.
also on the line with the astric, there are 2 colomns in the table recieving the 5, the other one is [ID] (PK) which is auto numbered. I need that ID # to be placed into [tblTransactions]![PaymentInfoID]. How do I assign that in the case statement code?

Private Sub



Select Case Date() >= [RenewalDate]

Dim CRD As Date 'Current Renewal Date
Dim dblTotalDues As Double
dblTotalDues = DLookup ("TotalDues", "tblMember")
CRD = DLookup ("RenewalDate", "tblMember")

Case x = [tblTransaction]![Debit] = dblTotalDues
Case x = [tblTransaction]![TransDate] = Date()
Case x = [tblTransaction]![MemberID] = [tblMember]![ID]
* Case x = [tblPaymentInfo]![PaymentMethod] = value (5)
* Case x = [tblTransactions]![PaymentInfoID] = [tblPaymentInfo]![ID]
Case x = [tlbMember]![RenewalDate] = DateAdd("yyyy", 1, CRD) 'Increases Members Renewal Date by one year

End Select

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

End Sub

[RLehrbass]

following code is showing this error: What am I doing wrong here?

Private Sub Report_Load()

Dim strSQL As String
strSQL = "SELECT * FROM" & [tblMember] ' Highlighted in yellow on debug
Set rs = CurrentDb.OpenRecordset(strSQL)


Dim CRD As Date 'Current Renewal Date
CRD = DLookup("RenewalDate", "tblMember")
Dim dblTotalDues As Double
dblTotalDues = DLookup("TotalDues", "tblMember")

Select Case Date >= [RenewalDate]

Case X = [tblTransaction]![Debit] = dblTotalDues
Case X = [tblTransaction]![TransDate] = Date
Case X = [tblTransaction]![MemberID] = [tblMember]![ID]
Case X = [tblPaymentInfo]![PaymentMethod] = 5
Case X = [tblTransactions]![PaymentInfoID] = [tblPaymentInfo]![ID]
Case X = [tlbMember]![RenewalDate] = DateAdd("yyyy", 1, CRD) 'Increases Members Renewal Date by one year

End Select

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

End Sub

[pbaldy]

Try

strSQL = "SELECT * FROM tblMember"

The rest of the code doesn't really make sense, so you may want to explain in words what you're trying to accomplish.

[LaughingBull]

after you search record then put this IF statement with the names of your fields

If Table_Name!RenewalDate <= Date Then
Table_Name!Amount due = Table_Name!Dues
End If
If your code is on the Invoice Form the it is Form_Name!RenwalDate etc....

[RLehrbass]

I am running [rptInvoiceBalDue]. (currently running very nicely) I will run this monthly!
Before I do this, I need to update all the member's Balance Due if the members renewal date has passed, so I am invoicing the correct amount.

I am setting up the VBA code (on Load) to search [tblMember].

I've never done a entire table search, only coded from an open form...So I am not sure how to assign items from or to a specific member ID when I don't have a Me! to take values from. I don't know if there is a better way or another approach but... I am trying to learn correctly.

So,
I need it to find each record where today's date is equal or Greater then the [RenewalDate]
add the value of [TotalDues] to [tblTransaction]![Debit];
Assign Date() to [tblTransaction]![TransDate];
Assign [tblMember]![ID] to [tblTransaction]![MemberID];
Assign the value (5) to [tblPaymentInfo]![PaymentMethod] and Assign the [ID] created on this line to [tblTransactions]![PaymentTypeID]


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

[June7]

Conventional approach is to calculate sum of billed and sum of paid and the difference of sums is balance. So members should be billed each month - these are transaction records, then their payments would also be transaction records. Is this the model you are using? Are you trying to automate creation of monthly billing records?

NOTE: Merged threads

[RLehrbass]

Moderator,
Yes, I am automating my monthly billing , this is being done (On Load) when I open the [rptInvoiceBalDue].

[qryTransactionBalance] has [MemberID], [TransDate], [SumOfCredits], [SumOfDebit] and calculates [Balance]. This is where I am getting my balance due for my invoice.
but before I run my Invoice report, I need to have current balance due which need to include new fees if the date I run the report is after the members renewal date, and create a record of applying the members renewal fees in [tblTransactions]:

I want to automatically find each record in [tblMember] where today's date is equal or Greater then the [RenewalDate];
For each record found, I need to:

add the value of [TotalDues] to [tblTransaction]![Debit];
Assign Date() to [tblTransaction]![TransDate];
Assign [tblMember]![ID] to [tblTransaction]![MemberID];
Assign the value (5) to [tblPaymentInfo]![PaymentMethod] and Assign the [ID] created on this line in [tblPaymentInfo] to [tblTransactions]![PaymentMethodID] '(5 = Applied Dues/Fees)
'([tblPaymentInfo]![ID] is related to [tblTransactions]![PaymentMethodeID] with a type 1 join (both are equal).)

After i have made the previous adjustments, I need to:
DoCmd.Requery ("qryTransactionBalance") 'I get my Invoice report's balance values from here.

It was suggested by 'LaughingBull' that I need to use "case statements" instead of my first approach, so I wrote the code accordingly.
Can you help?

[June7]

I've never had to build any kind of accounting db - I would not want to reinvent the wheel. I have used QuickBooks and Great Plains accounting apps.

I would be inclined not to do what you are trying - which is to create a record with a balance value - I would calculate the balance and send the customer a monthly Statement. The statement would show any transactions processed since last statement period and then show the calculated balance due.

I suggest you need a procedure that automates creating transactions for monthly charges.

[RLehrbass]

Moderator,
I have made edits to the previous post reply.
Please reread my above reply.
I am doing exactly what you are suggesting, but to calculate the balance due, I simply need to add a debit to the members transactions if the renewal date has passed.

I have a form [frmTransactionInput] that I use when I recieve a payment from a specific member that currently does all this, on a single members record when FrmMember is open, but not on an entire table scan without a ME!XXXX to pull from...I really can't see anything different except how I address the recordset.
I am so close on this finishing this database, this is the final major element. The invoice work great I just need to update a few transactions first...

[RLehrbass]

What I am doing here is writing a procedure that automates creating transactions for charges if renewal date >= date().
It just happens to runs (onLoad) of my rptInvoiceBalDue>>>

[June7]

I am confused. If you just want to create transaction records for monthly charges, why would this procedure care about the balance due?

[RLehrbass]

Ok,
I obviously am complicating this thing way beyond what I need to say.
For that I am Very Sorry.

All I am doing here is writing a procedure that automates creating a debit transaction if renewal date >= date().
It just happens to runs (onLoad) of my rptInvoiceBalDue>>>

"I need a procedure to find any records in [tblMember] where today's date is equal or Greater then the [RenewalDate]"

For each record found, I need to create a Debit transaction including the following steps:

add the value of [TotalDues] to [tblTransaction]![Debit];
Assign Date() to [tblTransaction]![TransDate];
Assign [tblMember]![ID] to [tblTransaction]![MemberID];
Assign the value (5) to [tblPaymentInfo]![PaymentMethod] and Assign the [ID] {autonumber} created on this line in [tblPaymentInfo] to [tblTransactions]![PaymentMethodID] '(5 = Applied Dues/Fees)
'([tblPaymentInfo]![ID] is related to [tblTransactions]![PaymentMethodeID] with a type 1 join (both are equal).)

Period.

[June7]

Something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblMember WHERE RenewalDate >= Date();")
While Not rs.EOF
CurrentDb.Execute "INSERT INTO tblPaymentInfo(...)"
CurrentDb.Execute "INSERT INTO tblTransaction(MemberID, TransDate, Debit, PaymentMethodID) VALUES(" & rs!ID & ", #" & Date() & "#," & rs!TotalDues & ", " & DMax(...) & ")"
rs.MoveNext
Wend

However, somewhat confused by "Assign the value (5) to [tblPaymentInfo]![PaymentMethod]" - why does a new record need to be created in tblPaymentInfo? Why does this ID have to be in tblTransaction? What is this table for? Are you associating payments to specific charges? Members never make partial or over payment? But why create the record if payment not yet received? Saving the record is fairly simple. Pulling the just created ID value can be done with a DMax() expression. More conventional approach may be to save the tblTransaction ID to tblPaymentInfo and create record when payment received or to not bother relating the two. Just sum and calculate difference to determine balance.

Now do you need to modify those members' RenewalDate value?

Probably need some way of making sure this code is not run again for the same billing period. Don't think I would put code behind report.