Expense Totals

[Nosaj08]

Hey Everyone,


Hopefully I am in the right section for this. What I am trying to do is create an expense section for my events database and I am not sure how to set it up. Currently, I have each event, along with its information set up as a row in my events table. However, now that my boss wants to be able to add in a section to itemize expenses for each event and list its totals, I am not sure how to integrate it into my current table. Any ideas? TIA!

Jason

[RuralGuy]

Use another table for expenses and put the EventID PrimaryKey in this table as a ForeignKey. Then have a MainForm bound to the Event table query and a SubForm on this MainForm that is bound to the Expenses table query with the EventID and the LinkChild/MasterFields properties. As you add expenses for each event, the ForeignKey will be filled in automatically by Access.

[Nosaj08]

Great, I will try that out and see if it will work. Thanks!

Jason

[RuralGuy]

Let us know how you make out.

[Nosaj08]

Hey RuralGuy,
Everything worked perfectly. However I am having a little trouble with adding a totals section to the main form. Here is what I did:

After creating the main form & table and the sub form & table, I created an extra field in the sub form that would take the sum of the amounts column. That worked just fine. However, I would like to hide that column and call it out in the main form. I created a text box in the main form with the following expression:

[Forms]![Events]![Expenses].[Form]![Totals]

Where as Events is my main form, Expenses is my subform, and Totals is the name of of the field that I want to call out. When, I plug this into the text box, all I get is "#Error." Any idea, where I went wrong? Thanks!

Jason

[RuralGuy]

Did you put the Totals control in the footer of the SubForm because that is where it belongs.

[RuralGuy]

The syntax for the ControlSource of a TextBox on your MainForm would be:
=Expenses.Form!Totals

[Nosaj08]

Yes, I had the Totals control in the footer of the subform. I just plugged in your expression and am now getting #Name?. Does this mean that there is a problem with one of the titles? I double checked everything to make sure there were no typos and all names matched. Any Ideas? Thanks!

[RuralGuy]

You know that a form is displayed on another form by the use of a SubFormControl, right? This SubFormControl has a name of its own that usually defaults to the name of the form it is displaying, but it does not have to be that way. It is the SubFormControl that is in the syntax. =SubFormControlName.Form.Totals

[Nosaj08]

Yup, thats where I was going wrong. I was referring to the Sub Form that I created and not the Sub Form Control. Works great now. Thanks for all your help RuralGuy!

Jason

[RuralGuy]

You're very welcome. You will still have an issue when there are no records in the SubForm. If we're done then try out the link in my sig.