Updatin field in a 2nd table 1

[firebolt]

Hi, Im doing some calculations in a table for school registration. When a student purchases a book, I want to also charge the outstanding registration fee. Once I do this, how can I update the table containing the amount paid with the outstanding fee added on? I'm using a query to select fields on the form.

Thanks,
Firebolt

 

[willir]

Firebolt

That time of year again -- oh man, get out the cheque book!

Okay, ideally, I would have the registration fee and the book charges perhaps on the same table -- sort of like an invoice table for each student.

But since it is apparently is not, can you provide a bit of info on your table strucutre for students, registration and student book purchases. There are a number of ways you may have approached this, and I dont want to steer you in the wrong direction.

Richard

 

[firebolt]

Thanks Richard...The tblestudent has fields for amountpaid...coursefee...basicfeee. Also amount_due. Amount_due=[coursefee]+[basicfee]-amountpaid. The record is stored with the studentid. My problem is that some students who owe $60.00 may pay only $50.00. I want to have them pay this $10.00 when they buy books. Iuse a query, subform and a checkout form to get student book purchases. On the checkout form is a registration fee textbox and bookfee textbox. I want to add the registrationfee paid paid here to the totalpaid in tblstudent, subtract it from the amount_due and hopefully=0. Then I want to save this in the tblstudent.

Complicated?? or poor construction? The principal wants to know when fees are paid up..this is one way to get reg fees during book sales.


Thanks,
Firebolt

 

[willir]

Firebolt

Thanks for the additional info, and thanks for the star although I have not yet provided you with a solution.

There are several solutions. Here are two...

Minimal coding - display amount owing, and use a command button to open up the employee record.

Create a function. On the menu, select "Insert" -> "Module"

Copy this code. The code may have to be tweaked to accomodate differences in table and field names.

Option Compare Database
Option Explicit

Function CalcOwing(lngStudentID As Long) As Currency

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Nz(lngStudentID, 0) > 0 Then
    Set dbs = CurrentDb()
    
    strSQL = "Select * from tblstudent where studentid = " & lngStudentID
    Set rst = dbs.OpenRecordset(strSQL)
    
    With rst
        If .RecordCount Then
            .MoveFirst
            CalcOwing = !coursefee + !basicfee - !amountpaid
        Else
            CalcOwing = 0
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

Else
    CalcOwing = 0
End If

End Function

A bit of bullet proofing
- must have a studentid
- must have a record for the studentid
If either fails, the calculation returns 0.

Amount owing is per info you provided.
I did not retrieve the value from amount_due. Although this would be another approach, I calcualte the amount based on the other fields.

Save the module - give it a meaning full name such as MyFirstModule ;-)

Now open the book payment form and make sure the Properties window is open. (From the menu, "View" -> "Properties")

Add an unbount text field. Click on the "Data" tab in the Properties window and select the ControlSource and add...
=CalcOwing([studentid])

I am assuming the studentid exists on this form -- since you are tracking which student is buying which book.

Then add a command button and use the wizard to open the Student form and find the specific (student) record. You can recieve the payment from this screen for outstanding fees.

The advantage of the function is that you can use it anywhere - in a form, in a report, in a query as long as the studentid is available.

More code - treat the amount owing as a special "book" and apply the payment against the student table.

The first part of the solution is like the previous one -- the CalcOwing function should be created.

Create a second function, also in the MyFirstModule"...

Function ReceivePayment(lngStudentID As Long, curPayment As Currency) As Boolean

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, curAmountPaid As Currency

If Nz(lngStudentID, 0) > 0 Then
    Set dbs = CurrentDb()
    
    strSQL = "Select * from tblstudent where studentid = " & lngStudentID
    Set rst = dbs.OpenRecordset(strSQL)
    
    With rst
        If .RecordCount Then
            .MoveFirst
            curAmountPaid = !amountpaid
            .Edit
            !amountpaid = curAmountPaid + curPayment
            !amount_due = !coursefee + !basicfee - !amountpaid - curAmountPaid
            .Update
            ReceivePayment = True
        Else
            ReceivePayment = False
        End If
    End With
    
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

Else
    ReceivePayment = False
End If

End Function

Previous assumptions apply.
The code updates both the amount_due and amountpaid. The amount_due is not adjusted by the payment amount, but by re-calculating the amount due based on the other three fields.

To actually call this update this function is tough to explain since I do not have details on your system where students pay for their books.

One way would be to create a "book" called "PayFees" with a variable prices that the cashier can adjust per the amount owing. Code would then call the aforementioned function with the payment amount.

One other tool.
DLookup is pretty cool.
=DLookup("[Amount_Due]", "tblStudent", "[studentid] = " & studentid)

Where the "blue" studentid is a value on the form.

...Moving on
A comment on the table design.
I suspect you want students to come back for more than one year, and take more than one course. You should create a "Fee" table that includes...

tblFee
FeeID - primary key
StudentID - foreign key to student table
CourseTerm
CourseCode - foreign key to course table
CourseFee - fee for course

Note that for the ...
CourseCode CourseFee
BasicFee 500.00
Access101 300.00
Access201 350.00
Access401 750.00

CourseTerm could be...
Year - 2003, 2004, 2005
Year+Season - F2003, W2004, S2004 (fall, winter, summer)

Now you can see what the fees were over several terms, etc for one student, or BasicFees for all terms, or all fees for Winter 2004.

Richard

 

[firebolt]

Yes, that's great Richard this worked for me. The minimal coding works for me. However, as I build more into this program your total solution may provide the best format in the end. The code gives me other ideas as well for certain features we have currently worked on.

Great, thanks for ALL YOUR INPUT
Firebolt