In Over My Head 3

[CluelessRink]

Being a "kind of" Access user and thinking it would be easy I volunteered to make a database to track tips that servers receive in our restaurant.

I have a table set up with the following fields:
totalsales, chargedsales, chargedtips, totaltips, tipoutscash, tipoutscharge. (all are set at number/currency)

What I would like is a "box" that would keep track of each running sum.

I do believe that once that is in place I can make a query for a report to get totals based upon a time frame.

Is it as simple as making a text box that will pull the data from the appropriate field?

 

[CluelessRink]

=DSum("totalsales","tips","[employee] = '" & [Forms]![tips]![employee name] & "'")

And yes, [totalsales] is a legitimate field. I see where your coming from on that.

 

[scriverb]

CluelessRink: I have gone back to your original posting to refresh myself with what the original request was. Your system has this table where you are entering tips, etc for each employee? Or is it entered by Date. How and what frequency are you entering this data. Is the ultimate outcome to have it displayed on a form or to print it out in a report?

Sorry, but at this point I am still confused as to what and how this is being used. I can certain help you with it but I need to understand how you are entering the data and how you want the form to be used.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Here's the structure...

[Tips] table has three fields (actually more, but if we get these defined I can copy and change as necessary to meet my needs)
-emname (text)
-emnumber (text, primary key)
-totalsales (nunber/currency)

[Employees] table has two fields
-employee (text)
-emnumber (text, primary key)

There are no relationships currently defined.


There is one form called [tips], it's control source is the table [tips]

-It has a combo box named [employee name] that draws it's data from the [employees table] and it's control source is [emname] from the [tips] table

-There is a text box called [totalsales] who's control source is [totalsales] from the [tips] table that is used to
enter dollar amounts of the sale.

-We have a text box called [runningsum] using a control source of [=DSum("totalsales","tips")]. Works wonderfully, gives me a running sum of everyones sales.

-Then there is a text box called [emsalessum] using the control of [=DSum("totalsales","tips","[employee] = '" & [Forms]![tips]![employee name] & "'")] which doesn't work so well.


I would like the form to enter data into my table by employee and keep a running sum of everyone's sales (this already works) as well as have a running sum of the individual employee I am currently working with.


As for reports, I've gotten that figured out using a query with a large amount of help via searching this forums archives. But they work fine.

Man! I sure hope this makes sense!

 

[scriverb]

I think the problem here is that when you are creating a new record the combobox does not yet have a selection. But, the control executes the DSum function anyway trying to use a Null value to select the employee. Thus the ERROR# occuring. Does the error go away after you have entered a new record and selected the employee from the comobox?

Try changing last text box to the following:

=DSum("totalsales","tips","[employee] = '" & Me![employee name] & "'")

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

The error stays regardless, and using the new code I get
"#Name?"

 

[scriverb]

Got the database and the controlsource of the textbox now reads:

=DSum("totalsales","tips"," [emname] ='" & [Employee Name] & "'")

Shouldn't have had the Me! designation. Didn't like that.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Excellent! That is just beautiful! And I see now how it works.

It was making me crazy. I felt like a caged bear, just running around in circles going bonkers.

Thanks again!

 

[CluelessRink]

One more question....

After I enter data into a field and press the tab to go to the next one, why doesn't the running sum update?

I've tried a couple of different macros like requery or repaintobject in the afterupdate but it won't update till I
go to a new record or exit and re-enter.

 

[PHV]

Because your current record is not yet saved and the DSum aggregate function reads the table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

I've tried to use the "save" setting in the macro but still nothing. I've tried save form and save table.

Form doesn't do anything and table generates an error because I don't have the table open.

 

[PHV]

Have you tried this ?
DoCmd.RunCommand acCmdSaveAllRecords

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

I did and received "Run-Time error '2501' the run command action was cancelled."

I have it set on the afterupdate property.

 

[RoyVidar]

Try just saving the current record, and, in case of error (which might occur sometimes when trying to save a record without unchanged values), something like this:

[tt]if me.dirty then
docmd.runcommand accmdsaverecord
end if[/tt]

- i e only saving when there are unsaved changes...

BUT - if you have other controls on the form bound to required fields in the recordsource, this will fail, perhaps with the 2501.

So another thing to try, is issuing a recalc, which recalculates all calculated controls:

[tt]me.recalc[/tt]

- in the after update of the control the sum is derived from.

Roy-Vidar

 

[Taff07]

I tend to use

me.refresh

This will also save the record for you.

Just put the line in the AfterUpdate property for the Control you are tabbing from.

Hope this helps

Ian

 

[cranebill]

Hey Bob... I am also giving you a star since instead of giving just the solution you gave a very detailed explanation and Clueless actually learned from that.

Michigan Power...
Go Pistons....

Bill

 

[scriverb]

Thanks Bill I do appreciate that. I had a little trouble getting my ideas across here. More my fault that Clueless.

Yes, Go Pistons!!! [HIGHLIGHT]Wallace[sup]2[/sup][/highlight] DDDDDDDDDDDDfense!!!

Sure was fun watching the LA boys get frustrated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

Like Bill said, I prefer to learn how to do it rather than having it done for me.

Jay

 

[scriverb]

Good luck, Jay. I took us a little off course here but we got it back on track. Thanks for the star also.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[CluelessRink]

It's time to wake this sleeping giant....

How do I do the calculation based upon a date field?

Using

=DSum("totalsales","tips"," [emname] ='" & [Employee Name] & "'")

where emname and Employee Name are text fields works wonderfully.

How about if I replace those with transdate and transdate respectivly but it is a date/time field?

I kind of understand the concept of enclosing date/time in #'s but from what I've seen I couldn't find where it applies to matching it to a certain field.

 

[scriverb]

This should work:

=DSum("totalsales","tips"," [transdate] = #" & Me![transdate] & "#")

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]