Subform in Access

[uksigma]

Hello

I'm currently doing an Access database for an A Level ICT project. The system is a combined appointment booking and payments system. The tables are:

Patients (the personal details of the patient in which the patient ID is the primary key)

Dentists (the same as the patients table but for the dentists, the primary key is dentist ID)

Appointments (the fields are start time, duration, patient ID, dentist ID, total cost and whether the patient has paid)

Treatment item (the details of each treatment item - the treatment ID which is the primary key, the name, cost and duration in minutes)

Treatment received (the appointment ID, treatment ID and quantity)

The dentist table is related to the patients in a one-to-many relationship. The patient table is related to the appointment table in a one-to-many. The appointment table is related to the treatment received in a one-to-many and the treatment item is related to the treatment received in a one-to-many.

The aim is to create a form with a subform, whereby the main form would display the appointment information, whilst the subform would give the details of the treatment items, quantities, durations and costs.

I'm confused because I was under the impression that it would be possible to be set up so that the user could enter the details in the main form (patient ID to bring up the patient details, dentist ID for the dentist information and the time) and then use the subform to enter the individual treatment item ID numbers so that all the details of each treatment item would be displayed, along with its cost and duration. Then the TOTAL cost and TOTAL duration could be displayed in the main form.

This would save the user entering the treatment information in multiple times for two occurrences.

I've tried for over two hours on three separate occasions to implement this but the truth is that I just don't understand enough about the application to design the forms properly. It may not even be possible to do what I'm trying.

I would really appreciate any help or advice from anyone on this. Sorry if I've not made this clear enough, if so just tell me and I'll try and clarify.

Thanks a lot.

[Delerna]

Hi
It most deffinitely is possible and if it wasn't I would be uninstalling access from my computer in disgust.
A couple of questions that may help you
1) Is the main form bound to the patients table before you add the subform?
It needs to be because the subform has the treatments which are linked to the patient and the subform will link to the main form.

2)When you add the subform does a message popup asking you which fields on the subform and the mainform should be linked?


If you are having difficulties then I suggest you simplify your task and drop the dentist from the equation. Get a form and subform working for the patient/treatment first. Once you have that mastered then try and figure out how to include the dentist. I find that advice invaluable for tasks I am having trouble with, break it down into simpler steps and then tackle them 1 at a time

[uksigma]

Hi, thanks for replying.

I took your advice and I think I've got it set up properly, but for some reason I can't seem to total the fields in the subform. I've tried to duplicate the NW Traders example and do a =sum([TotalCost]) in the subform footer but it just won't work. Even when you open the subform on its own, it just keeps saying '#error'. I don't suppose you've got any ideas on this one? It's kind of stumped me because now I can't transfer the subform totals to the main form.

Anyway thanks for your help on the earlier problem. :]

[Delerna]

Well you are doing it correctly
Textbox in the footer
the ControlSource property of the textbox setting has =sum([FieldName])

where Field name is the ControlSource setting of the control on the forms body that you want to sum.

#Error usually means that it can't perform the sum because 1 or mor values in the control on the body that you are summing is a non numerical value or I think even a null value does it as well. Not sure about the null but I think it does cause it.

Anyway its probably due to something like that so I would check the data in the control you are summing, because you seem to be doing it correctly and summing in the footer definiteley works.

[sierra7]

Well you are doing it correctly Textbox in the footer
the ControlSource property of the textbox setting has =sum([FieldName])

where Field name is the ControlSource setting of the control on the forms body that you want to sum.

Hi UKsigma
I agree with the advice that Delerna has given but I would also check the name of the control which is holding the Sum is not the same as the FieldName (it sometimes happens depending upon what you have copied from where) You will get #Error if this happens.

I'm also attaching a demo system which might help. It's nothing to do with Dentist, Appointments and cost of Treatments, but Contracts, Consignments and Quantities shipped, so similar ideas can apply.

It uses the idea of having TWO sub-forms on a Main form. In your scenario the Main form would represent the Patient and the first sub-form a list of Appointments he has had. The second sub-form could then list the Treatments that had been administered at each appointment.

To make this work effectively though, you will have to have a TreatmentCost field in your Appointments table, which is updated when you add moreTreatments in the second sub-form.

S7

[uksigma]

Thanks a lot for that, I'll take a look at it.
I've got until late April to finish the database, so I should be able to improve it before then.
Thanks again! :]

[LyndaS]

I have run across this problem many times myself. I have found a solution that seems to work for me:

Subform footer (not page footer) create textbox [Total]
Control source should be: =Sum(Nz([fieldname],0))

Main Form: create textbox [TtlAmt]
Control source: IIF(IsError(sub frm.Form![Total]),0,Nz(subfrm.F orm![Total],0))

Hope this helps if you haven't already resolved. :)

[sierra7]

Hi Lynda
Interesting code and may very well suppress those nasty error messages, but this seems to be displaying 'zero' when an actual value should be displayed.

I would sooner resolve the error and display the right value, or have I missed the point?

S7

[LyndaS]

Hi Lynda
Interesting code and may very well suppress those nasty error messages, but this seems to be displaying 'zero' when an actual value should be displayed.

I would sooner resolve the error and display the right value, or have I missed the point?

S7

Hi S7,
No, I don't believe you've missed the point.
As Delerna mentioned in the post from 2/17, often null values will create #error and if you are using the "nz" as part of your sum it may resolve the issue.
Sorry I didn't add much explanation to my last post, When I read it (actually skimmed through it), I was preparing to head into a "marathon" meeting for the rest of day and just thought I would add my 2 cents for whatever it's worth.

Lynda