Lesson Database

[derlwyn]

I am trying to build a database for a friend who teaches musical lessons in the evening.

I have set up a trial database in order to work out a solution

In a table called tblcustomer I am storing a customerID as a key field, as well as name, phone etc

In a table called tbllesson I am storing a lessonId as a primary key field and the clientID as a foreign keyfield. I also am storing the date, time and wether the client has paid or not.

All works well so far, I am able to add new clients, add new lessone, see what lessons a client has booked etc using forms and subforms.

What I would like to do is two fold. Add a field called number of lessons taken and then increment this automatically every time a client books a new lesson

I would also like to calculate the amount owed by each client, assumimg that say every lesson is £5

I could write all of the above in C or Pascal, however I am not sure how to do it in VBA

Any help would be greatly appreciated

 

[pl456]

If your using a form / subform doesnt the subform already have the total records displaying in the record counter near the nav buttons?

 

[rapsr59]

Hi,

I would set up a totals query that would return both The number of lessons taken and the total amount owing. The following is an SQL statement that would return both.

SELECT tblcustomer.ClientID, tblcustomer.ClientName, Count(tbllesson.LessonID) AS CountOfLessonID, Sum(IIf([Paid]=False,[LessonCost],0)) AS TotalOwing
FROM tblcustomer INNER JOIN tbllesson ON tblcustomer.ClientID = tbllesson.ClientID
GROUP BY tblcustomer.ClientID, tblcustomer.ClientName;

You can then set this as a Record Source for a List Control.

Hope this helps.

Have you heard of Pembrook South Wales. Visited in 1965 when in the Navy. Neat Place.

Richard

 

[rapsr59]

You can also set up two text Controls on your main form with the following Control Source.

This one returns the number of lessons
=DCount("LessonID","tblLesson","[ClientID] = " & [ClientID])

This one returns the balance owed by the client
=DSum("LessonCost","tblLesson","[ClientID] = " & [ClientID] & " AND [Paid] = False")

Hope this helps.


Richard

 

[rapsr59]

Or you can, as you requested, use VBA to accomplish the same thing.

First create two text controls. Name the first text control txtOwing and the second text control txtLessons.

Then place the following code in the forms Current event.

Private Sub Form_Current()
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strsql As String
 
    strsql = "SELECT tblcustomer.ClientID, tblcustomer.ClientName, " & _
    "Count(tbllesson.LessonID) AS CountOfLessonID, " & _
    "Sum(IIf([Paid]=False,[LessonCost],0)) AS TotalOwing " & _
    "FROM tblcustomer INNER JOIN tbllesson " & _
    "ON tblcustomer.ClientID = tbllesson.ClientID " & _
    "GROUP BY tblcustomer.ClientID, tblcustomer.ClientName " & _
    "HAVING (((tblcustomer.ClientID)=" & Me.ClientID & "));"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
    Me.txtLessons = rs![CountOfLessonID]
    Me.txtOwing = rs![TotalOwing]
 
    Set rs = Nothing
    Set db = Nothing
 
End Sub

Using VBA This will accomplish the same thing but is quicker and cleaner than using Dsum and DCount.

Hope this helps

Richard

 

[rapsr59]

If you form has a subform then you can.

1. Place the following code in the Main Forms Current event.

Private Sub Form_Current()
    Call ReturnLessonsAndOwing
End Sub

2. Creat a Public Sub Called ReturnLessonsAndOwing

Public Sub ReturnLessonsAndOwing()
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strsql As String
 
    strsql = "SELECT tblcustomer.ClientID, tblcustomer.ClientName, " & _
    "Count(tbllesson.LessonID) AS CountOfLessonID, " & _
    "Sum(IIf([Paid]=False,[LessonCost],0)) AS TotalOwing " & _
    "FROM tblcustomer INNER JOIN tbllesson " & _
    "ON tblcustomer.ClientID = tbllesson.ClientID " & _
    "GROUP BY tblcustomer.ClientID, tblcustomer.ClientName " & _
    "HAVING (((tblcustomer.ClientID)=" & Me.ClientID & "));"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
    Me.txtLessons = rs![CountOfLessonID]
    Me.txtOwing = rs![TotalOwing]
 
    Set rs = Nothing
    Set db = Nothing
 
End Sub

3. Place the following code in the sub form Current event.

Private Sub Form_Current()
    Call Me.Parent.ReturnLessonsAndOwing
End Sub

and when data is changed in the subform, a new lesson, or the Client pays for a lesson or Maybe his check bounces and the paid field changes to false, then the main form will show these changes in txtOwing and txtLessons.

the Dcount and Dsum will automatically update to show the changes.

There I think I am finished.

Hope you don't take this as overkill. I just wanted to show you different ways to accomplish the same thing.

Richard

Disclaimer: The code examples have no Error Handling. It is just plain Nut and Bolts how to.