Payment query

[Preacha]

Payment Form

I was to have a database that will track individual payments of a due payment (i.e. Customer pays a bit one day, and still has more remaining for another day, etc) . I have a "customer" table, a "Payments" table and a "CustomerPay" table (that links the 2).
The Structures of the tables are as follows:
Customer
MembershipNo (PK) , Name, etc
Payment
PaymentNo (PK) , prevBalance, paidAmt, datePaid, balanceForward, etc
CustomerPay
MembershipNo (PK) , PaymentNo (PK)

I have tried to implement the following code on form load. Has anyone seen any examples of have any ideas that would help. How do I check if a record exists with a specific criteria? I would be much appreciative.

 'On Error GoTo 1
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim SQL_prev As String
    Dim SQL_curr As String
    Set dbs = CurrentDb
    Dim prevPayNo As Integer
SQL_curr = "SELECT * " & _
      "FROM Payment " & _
      "WHERE (PaymentNo = " & paymentNo & ")"
Set rst1 = dbs.OpenRecordset(SQL_curr)
prevPayNo = rst1!paymentNo.Value - 1
If (prevPayNo > 0) Then
SQL_prev = "SELECT * " & _
      "FROM Payment " & _
      "WHERE (PaymentNo = " & prevPayNo & ")"
Set rst2 = dbs.OpenRecordset(SQL_prev)
If (rst1!prevBalance > 0) And (rst2!paymentNo <> Null) Then
rst1.Edit
rst1!prevBalance = rst2!forwardBalance
prevBalance = rst2!forwardBalance
rst1!forwardBalance = rst1!prevBalance - paidAmt
forwardBalance = rst1!prevBalance - paidAmt
rst1.Update
End If
Else
        prevBalance = price
End If
'1:

Btw, the SQL code of the query used on my payment form is as follows . . . . .

SELECT CustomerPay.membershipNo, CustomerPay.paymentNo, Customer.firstName, Customer.lastName, MemberTypes.memberType, Payment.typeNo, Payment.prevBalance, Payment.paidAmt, Payment.datePaid, Payment.forwardBalance, Customer.dateJoined, MemberTypes.price, Payment.passesRemaining, DateAdd("m",[memberTypeLength],[dateJoined]) AS dateExpiry
FROM (MemberTypes INNER JOIN Payment ON MemberTypes.typeNo = Payment.typeNo) INNER JOIN (Customer INNER JOIN CustomerPay ON Customer.membershipNo = CustomerPay.membershipNo) ON Payment.paymentNo = CustomerPay.paymentNo;

(if that helps any )
Thanks

 

[ecniv]

Customer
MembershipNo (PK) , Name, etc
Payment
PaymentNo (PK),MembershipNo, paidAmt, datePaid, etc

One the form you'd have selected or entered the membershipno;
- Use a query to total the payments made so far (positive and negative)
eg:
Select Sum(paidamt) as TotalPaid from payment where membershipno=<membershipno selected>

Then have a listbox with a list of transactions (possibly filtered to the last 20 or so) and the textboxes for adding new payment details etc under that.
When entered, the textboxes are validated then the payment added to the table, the list box refreshed, and the total so far refreshed.

Just a thought.
I don't know if the totals sql above could be incorporated, it may freeze the input of the form sql (locking it) so may have to be separate.

I personally wouldn't use a brought forward/current balance held in the tables.


Vince

 

[Preacha]

Here is a copy of what I am doing. Mind the god awful colour of the forms (It's for a womans gym). The payment form is what I am focusing on atm.
ISIS Database

 

[Preacha]

Improvements to the database

Just wondering how I would implement this into the database, I am looking into it but haven't really had time cos of other work. Any other enhancements anyone think need to be added? Cheers