Trouble Building Expression in Macro

[Robin Evans]

I have created a database that my nonprofit company will use for tracking members monetary gift levels. The main table and form contains personal (name, address, phone, etc.) information about each member. Information about each donation a person makes is kept in a subtable and in the main form I have put a subform, so I can look at both a persons personal and monetary data at the same time. I use the field “Member Number” as the key field in the main database to ensure that the records appear properly on the main form.

In my main table I have a field called: Total Mem Amt and the Data Type is set to Currency. I have placed this field on my main form. I would like this form to show the total amount a person has given to our organization over the entire course of their membership.

In an attempt to do this I created a macro called: Total Membership Amount. I have set the action to: SetValue and the item is: [Member History]![Amount]. Now here comes the part that seems to be giving me trouble, the expression.

I have been able to enter the expression in both of the following manners (at different times of course) and have the macro accept it:

DSum([Amount],[Member History],[Member Number]=[Forms]![Members and Donors]![Member Number])

DSum([Member History]![Amount],[Member History]![Member Number]=[Forms]![Members and Donors]![Member Number])

After I entered an expression (that I hoped would work) I went into form design of my main form and in the field property event tab I put in the macros name in the “On Got Focus” property. However, when I go to form view and click on the Total Mem Amt field I get the following error:

The object doesn’t contain the Automation object ‘Member History.’ You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn’t make the property or method available for Automation operations. Check the component’s documentation for information on the properties and methods it makes available for Automation operations.

My experience with Microsoft Access puts me basically just above the beginner level and very far away from being considered efficient with the program, so basically I don’t understand the error message at all and I might be going at this all wrong. Any advice any one could give would be really helpful. This is my last hurdle before the database is up and running. Thanks in advance.

Robin Evans
Membership Coordinator
Tohono Chul Park
robinevans@tohonochulpark.org

 

[directormac]

I'm not sure you need a macro, if I follow you correctly.

It sounds like you are storing each of the donations separately AND storing the total, which is a waste of resources. Just store the individual gifts, then total them up whenever you need to.

In the form/subform example you gave, if your subform is showing all of the person's donations, just add an unbound textbox to the footer section of your subform design, call it txtSubTotalGifts, and in the Control Source property type "=Sum([Amount])" (no quotes). If you're viewing the subreport as a datasheet, this box won't show up. If you're viewing it as a form, you can set the visible property to No for it. Either way, back on your main form, create another unbound textbox, txtTotalGifts. In the control source for that one, type "=[frmMemberHistory].Form!txtSubTotalGifts" (again, no quotes).

The NorthWind sample database has examples on this. The same thing can be done when you need to sum for reports.

--Summy Mac

 

[Robin Evans]

Summy- Thanks for the advice. I have set up both text boxes as you have suggested. I am viewing the subreport as as a form, so for right now I have set the visible property for the text box to yes, just to double check that the totals are adding up correctly (which they seem to be).

While the text box on the subform is working fine, the text box on the main form is not working. The box only displays #name? I have doubled and tripled checked that I have entered the name correctly, and I have. Can I pry one more helpful hint from you, please?

Robin Evans
Membership Coordinator
Tohono Chul Park
robinevans@tohonochulpark.org

 

[Robin Evans]

The problem has been solved. Thanks for the advice!

Robin Evans
Membership Coordinator
Tohono Chul Park
robinevans@tohonochulpark.org

 

[directormac]

Access is pretty picky about the way some things are ordered (imagine that...) Use the expression builder to make sure you've got it "just that way." When you do this, be sure that you select the subform that is loaded into your main form. In other words, go into your main form first and select the subform inside it:

[-]Forms
| || [-]Loaded Forms
| || | || [-]frmMemberInfo
| || | || | || [V]frmMemberHistorySubForm
| || | ||
| || [+]All Forms


rather than picking it from the "All Forms" folder. If this doesn't help, let me know and I'll e-mail you a sample.

--Artistic Mac