Probably easy - How to add a "total" button to a form

[Larry Smith]

Hi there,

I'm an experienced C++ programmer but I don't use Access very often. Can
someone set me on the right path to creating a "Total" button on a form I've
created. Besides the "Total" button itself, the form has a "Start" and "End"
date field (text box) and a "Total" field. I simply want to fill the "Total"
field in with the "Sum()" of a given column in one of my tables for each
record whose start and end date fall between the dates entered on the form
itself. The "Total" button should therefore ensure that the "Start" and
"End" dates are properly filled in and if so, run the appropriate SELECT
statement to fill in the "Total" field. Presumably I can simply create a VBA
function and wire this up to the button's "On Click" event but where to
create this function and how to set the "Total" field with the SELECT
results is eluding me (note that creating the form itself isn't an issue).
Can anyone offer any advice or link on the subject. Thanks in advance.

 

[Jeanette Cunningham]

Larry,
you would use an update query to update the values for Total in the
appropriate table.
The button on the form would have code on its click event something like
this:

Note: q is either an update sql string built in code using the start and end
date from the dates entered on the form
OR a saved update query.

Private Sub TotalBtn_Click()
Dim strSQL as String
Dim db as DAO.Database

Set db = db.engine(0)(0)
strSQL = "q"
db.Execute strSQL, dbFailOnError
Debug.Print strSQL
Set db = Nothing
End Sub

Note: usually in Access it is problematic to store Totals in tables (some
types of database need to store a calculated total).
Totals can be very easily calculated to show on forms and reports -
calculating them when needed keeps the calculated value up-to-date.

Jeanette Cunningham

 

[Larry Smith]

Thanks very

Larry,
you would use an update query to update the values for Total in the
appropriate table.
The button on the form would have code on its click event something like
this:

Note: q is either an update sql string built in code using the start and
end date from the dates entered on the form
OR a saved update query.

Private Sub TotalBtn_Click()
Dim strSQL as String
Dim db as DAO.Database

Set db = db.engine(0)(0)
strSQL = "q"
db.Execute strSQL, dbFailOnError
Debug.Print strSQL
Set db = Nothing
End Sub

Note: usually in Access it is problematic to store Totals in tables (some
types of database need to store a calculated total).
Totals can be very easily calculated to show on forms and reports -
calculating them when needed keeps the calculated value up-to-date.

Thanks very much. That's helpful and reinforces what I've been reading.
Could you briefly elaborate on the basic mechanics however. I've added a
function similar to yours to the "Modules" section of my ".mdb" file. If I
now simply set the "On Click" property for my button to "=TotalBtn_Click()"
(quotes not included), I subsequently get a "function not found error" when
I click the button (not literally the message but that's the problem).
Obviously I haven't wired this up correctly so what might I be doing wrong.
Once I pass that problem however, note that the "Total" field I'm
calculating isn't in any table. It's just a text box on my form which I want
to fill in with the results of the SELECT statement I'll be running in my
function. How do I actually target (fill in) this "Total" field from my
function? Thanks again.

 

[Albert D. Kallal]

Hi there,

I'm an experienced C++ programmer but I don't use Access very often. Can
someone set me on the right path to creating a "Total" button on a form
I've created. Besides the "Total" button itself, the form has a "Start"
and "End" date field (text box) and a "Total" field. I simply want to fill
the "Total" field in with the "Sum()" of a given column in one of my
tables for each record whose start and end date fall between the dates
entered on the form itself.

Ok, assuming 3 text boxes

1) txtStartDate
2) txtEndDate
3) txtDateSum

the code behind the buttion would be as follows:

Dim strWhere As String
Const df As String = "mm/dd/yyyy"

strWhere = "[DateField] between #" _
format(me.txtStartDate,df) & "#" & _
" and # " & format(me.txtEndDate,df) & "#"

Me.txtSum = DSum("FieldToSum", "tableName", strWhere)

The "format" command is used to "cast" (spacing in c terms) the format of
the entered date to usa format (all internal code in ms-access needs to use
sql dates in usa format).

but where to create this function

Place the button on the form, and then make sure the property sheet for that
button is showing
(from menu go view->properties when the button is highlighted in design
mode).

Now, on the properties sheet, choose the event tab, and then click on the on
click field (you see that [...] button appear..and then click on that...you
get a choice to create a expression, macro, or code builder...choose code
builder...

You then paste in the above code (that above code is air code..but, is in
fact close).

Note that you can get ms-access to "display" totals (sums) on the bottom of
a continues form without using any code (you can use a text box and a
expression). Here is some screen shots of continues forms, some of which
show total values...

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

In your case, it is likely/possible that your form is un-bound, and perhaps
you intend to output this result to a report with the filtered date range?
Reports can easly be filtered..and produced totals without any code. It all
depends on the context of what your trying to do..but, there is a *lot* of
different ways to generate totals..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


and how to set the "Total" field with the SELECT

 

[Larry Smith]

Thanks very much for the comprehensive feedback. The code you provided is
generous and (greatly) appreciated. I'll run with that and should likely get
things working. If there are no further posts then you can assume so thanks
to your assistance (appreciation to Jeanette as well). Thanks again!