Gocsteve1
asked on
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
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
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.
ASKER
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
My goal is to have the populated field then "Save" to the Invoice table as a new record (invoice)
sc
<<
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).
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).
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
sc
ASKER
mbizup did a great job working with me on my problem.
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:
https://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.
https://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.
-->> 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.
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.
ASKER
Great, thanx again!!!
You dont need an intermediate query.
Place this in the control source property of a textbox, inluding the = sign:
Open in new window
Jim Dettman has a good article explaining the Domain Functions, including DSum:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html