Solved

Binding a SubForm Text Box to Query Result

Posted on 2013-01-22
11
578 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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