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]

So why do I need the me! in this statement and not the other?

 

[CluelessRink]

Well, now I am getting #Name? in the text box

 

[CluelessRink]

Got it!

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

Dang! Your a good teacher!

 

[scriverb]

Sorry about the Me! thing. I was thinking because the names were the same between the field name and the form control name that we might need to make that designation. Obviously, not.

Thanks for the star and the comments. Good luck.

[COLOR=006633]Bob Scriver[/color]

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

 

[CluelessRink]

Maybe because it's late here but I'm stuck on another (probably) simple issue.

I have a combo box set up to pull an person's name from a table and then place it in another. Works fine.

How do I get corresponding text boxes to show data based upon the persons name that is selected?

For example, you select Smith from the combo box and the box next to it should automatically show his file number.

 

[willir]

CluelessRink

May be worth a new post, instead of tagging on to the same one.

In case Bob has gone to bed, and your need is urgent...

If you are selecting from a unbound combo box to apply a filter. For example, "find" Mr Smith or "find" Neil, after you selected the name from the query, use the AfterUpdate event for the field.

Assuming field name for the combo box is NameQry

Private Sub NameQry()
'Name query returns EmployeeID, a long interger

If Nz(Me.NameQry, 0) > 0 Then
   Me.Filter = "EmployeeID = " & Me.NameQry
   Me.FilterOn = True
Else
   Me.FilterOn = False
End if

End Sub

Now assume EmployeeCode is a text string...

Private Sub NameQry()
'Name query returns EmployeeCode, a text string
Dim strQ as String

strQ = CHR$(34)   ' Double quote character

If Len(Nz(Me.NameQry, 0)) > 0 Then
   Me.Filter = "EmployeeCode = " & strQ & Me.NameQry & strQ
   Me.FilterOn = True
Else
   Me.FilterOn = False
End if

End Sub

Okay, now assume you are working from a bound combo box where the EmployeeID field is tied to the Employee table...

Let say your combo box bound to EmployeeID uses the SQL statement...
[tt]
Select EmployeeID, EmployeeLN, EmployeeFN, EmpPhone from Employee
[/tt]

Okay, a cute trick is to realize that a combo box returns all values in the Select statment - ID, Last and First Name and Phone number. These values are available to you.

So assuming the EmployeeID combo box has been updated, and you want to populate the other fields (usually unbound since you just need EmployeeID and the other fields are accessible)...

For the AfterUpdate field update

Private Sub EmployeeID_After_Update()

If Nz(Me.EmployeeID, 0) > 0 Then
   Me.txtEmpLN = Me.EmployeeID.Column(1)
   Me.txtEmpFN = Me.EmployeeID.Column(2)
   Me.txtEmpPhone = Me.EmployeeID.Column(3)
End If

End Sub

Here, you can reference the column number in the select statement and assign it as appropriate.

You can NOT / should NOT assign the value back to another field that also is bound to the Employee table and EmployeeID.


Last one.
You can also use DLookUp (as well as DMAX, DCount, etc)
Bob is a master of this syntax. His aforementioned posts deal with this approach.

Back to the Employee info.
For txtEmployeeLN, Control source is set to...[tt]
=DLookUp("[EmployeeLN]", "tblEmployee", "[EmployeeID] = " & EmployeeID)[/tt]

Hope one of these approaches answered your question.
Richard

 

[CluelessRink]

I like the idea of the DLookUp, but it as I am close to the point of making a fatal mistake. So will hit it up fresh tomorrow.

Thank you very much for the help.

I'll post back tomorrow with whatever solution I come up with.