[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Binding a SubForm Text Box to Query Result

Posted on 2013-01-22
11
Medium Priority
?
594 Views
Last Modified: 2013-01-23
Im wanting to tie a subform texbox to a query result,  I don't care if its tied to a button to start the query, but I'm not having much luck sorting this one out.

Ive attached a sample of my database.  "Client" is my main form, "Invoice" is the subform that I want to populate the field "SumOfPremiumDue" on the "Invoice" table from the Query "Employee Query"

I have a button that runs the query, then I can cut and past the output result to my field, but I know I'm missing some simple code to get this working.

thanx in advance
Invoice-Database.accdb
0
Comment
Question by:Gocsteve1
[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
  • 6
  • 5
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38807394
Try using DSum for the control source of a textbox for your total.

You dont need an intermediate query.  

Place this in the control source property of a textbox, inluding the = sign:

=DSum("PremiumDue","Employee","Client_ID =" & [Client_ID])

Open in new window


Jim Dettman has a good article explaining the Domain Functions, including DSum:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38807403
Also, as a general rule, you should perform calculations like this on an as-needed bases through functions or queries in your forms and simply display the results on forms or reports without actually saving the results in your tables.
0
 

Author Comment

by:Gocsteve1
ID: 38807501
Ok, I had tried this and indeed it fills the field on the form, but does no save the sum as an "Add Record" to the "Invoice" table "SumOfPremiumDue" field.  

My goal is to have the populated field then "Save" to the Invoice table as a new record (invoice)

sc
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 61

Expert Comment

by:mbizup
ID: 38808253
<<
My goal is to have the populated field then "Save" to the Invoice table as a new record (invoice)
>>

This can indeed be done, but curious why you want to do it?

Generally recommended design practice is to NOT store calculated/derived values.  The reason for this is that if any of the 'parts' of your derived value change, you may wind up with bad data in the tables where you have stored your calculation.

For that reason, standard design practice 99% of the time is to calculate data like this on the fly, when it is needed for forms and reports.

That said, there is that 1% of the time where you really do need to store derived data... so just curious what your reasons are for storing it?  (I don't want to give you bad advice that may cause problems later).
0
 

Author Comment

by:Gocsteve1
ID: 38810171
As a "New Record" tracking in the Invoice table, each invoice ammount.   Its a snapshot, I know, but it will tie to a Crystal Report version of an auto spooled/emailed invoice.  

The premium will change from one invoice cycle to  another, so pulling up the client, editing the individual premiums, (employe datasheet) then saving those changes as a "New Record" (creating a unique Invoice ID Num) tied to the client ID.

I don't care that the employee amount is flexing, I just need the Invoice table to house a line per invoice to the client that will Match the Crystal that I'm auto emailing.....   Again, I can do it with a cut and paste from the Employee Query, I am just looking to take that step out and make it easier on the admin that is going to do this.

sc
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38810211
Ok -

Remove the control source property, and bind the control source of your textbox to the field in the underlying table.

Then write this VBA in your command button's click event:

Me.txtBoxName =DSum("PremiumDue","Employee","Client_ID =" & Me.Client_ID)

You'll have to adjust the textbox name as needed (I can't open a database in .accdb format at the moment)
0
 

Author Comment

by:Gocsteve1
ID: 38810291
Sweet, thanx.   Now, can you explain how you got knew to choose this code?    I am ok with Domain, but really weak with VBA.   Is there a good book available on helping me with learning that you would reccomend?   Im a novice, but would like to get better!

sc
0
 

Author Closing Comment

by:Gocsteve1
ID: 38810292
mbizup did a great job working with me on my problem.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38810336
Jim's article on the Domain functions is great, but for general VBA Beginner stuff, check out ALL of the EE articles by TechMommy, starting here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_9490-Introduction-to-VBA-Part-1.html

(I believe each article contains links to the next few in the series, down at the bottom of each article)

Also, regarding that command button code, you might want to play around with placing that code in different events such as your Form's Current event and After Update events of various controls and/or your subform to see how different things can trigger that code, and populate the textbox without actually using a command button.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38810348
-->> Now, can you explain how you got knew to choose this code

Those domain functions are a great, easy way to lookup any single value from any table or query - regardless of what table/query your form happens to be bound to.
0
 

Author Comment

by:Gocsteve1
ID: 38810361
Great, thanx again!!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 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