Binding a SubForm Text Box to Query Result

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
Gocsteve1Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Gocsteve1Author Commented:
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
 
mbizupCommented:
<<
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
 
Gocsteve1Author Commented:
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
 
Gocsteve1Author Commented:
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
 
Gocsteve1Author Commented:
mbizup did a great job working with me on my problem.
0
 
mbizupCommented:
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
 
mbizupCommented:
-->> 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
 
Gocsteve1Author Commented:
Great, thanx again!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.