?
Solved

How to get log totals in the tree view , another log database question

Posted on 2013-11-18
4
Medium Priority
?
421 Views
Last Modified: 2013-11-20
Thank you all for your continued help on this.  I am learning a ton and get stuck at about every step of the way but I am getting closer to having a code base that I can work from by copying and pasting.  

The project tree footage is working.  I was able to get a nested loop of the calculation working.

Project Total
I know how to do the SQL now for what I need to do, but I can't see how to do it in one of these fields.  I need to show the individual log footage for each tree.

Log Footage
For example after selecting that first log and clicking on "View Logs" you get this screen and the total for that tree.

View Logs
If I go to the design view of the Tree Form and go to actions for the text field "txtCurrentLogFootage" it brings up the expression editor.  How do I do an SQL VBA query thing in there?
Database1683--2-.accdb
0
Comment
Question by:getwidth28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39658266
You're question is not very clear -- I think that is why it has gone unanswered for so long.

IF I understand correctly...

1.  You shouldn't store the CurrentLogFootage in your table.  Calculations like this should be made and displayed on the fly when needed in forms, reports and queries rather than stored in tables (there are a few exceptions to this rule of course, but this does not seem to be one of them).

2.  To accomplish #1, set the control source property of your CurrentLogFootage to the following (include the = sign at the beginning):

=DSum("(Round(((([Diameter] - 4) / 4) ^ 2) * [Length], 0))","tblLogs","treeID = " & [treeID] & " AND ProjectID = " & [ProjectID])

Open in new window

0
 

Author Comment

by:getwidth28
ID: 39659482
Shucks, I tried to explain as well as I can.  I will try what you put probably this afternoon when I can.  If I need to I will explain better.   Thank you

I wasn't hinting at needing to store the calculations in the table, I changed my thoughts on that from this board (may have been you telling me).  I just know it would be easy to put the field in the txt box vs. a SQL Query.

Man that line is a mouth full.  How do you guys keep that stuff straight.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39660125
--->> How do you guys keep that stuff straight.

It is actually 95% what you had already written.  I just wrapped it in a function and added criteria.

The domain functions (DSum, DAvg, DLookup, etc)  are a very common way to 'query' single values to display in textboxes.

Jim Dettman has a great article detailing their usage here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
0
 

Author Comment

by:getwidth28
ID: 39663869
I plugged that in exactly how you wrote it and it worked right away.  Brilliant.  Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question