Solved

Binding a SubForm Text Box to Query Result

Posted on 2013-01-22
11
540 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
  • 6
  • 5
11 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<<
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
mbizup did a great job working with me on my problem.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
-->> 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
Comment Utility
Great, thanx again!!!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now