Update table from linked forms

[rpadams]

I have a budget application with two tables:

tblTitles: TitleID (key long int)
PastYrBudgetAmt
CurrentYrAmt
NextYrAmt

tblDetail: DetLinkID (matches TitleID)
DetailID
DetailDescription
DetailPastYr
DetailCurrentYr
DetailNextYr

There is a one to many relationship between these two.

I have two forms visible on my screen. The Title form is a multi-line form that I use to select the title wanted. The tblTitle is bound to this form. The Detail form is also a multiline . I have written code so that when I select one record on the title form, all of the Details are shown for editing or adding new ones.

The problem I have is that many of the Titles do NOT have Details, but DO HAVE a single dollar value for the title category itself. I could create a "dummy" detail for each one, but that seems like a lot of extra work inputing. What I would like to do is input these Title values directly (and keep them in the tblTitles) AND, where there are DETAILS, sum the details and then place the sum in the Titles table.

I KNOW it is not good practice to put calculated values into a table, but there are a number of reasons I want to do so in this application.
My real question is how do you Sum one form (based on a key field) and place the result in a table for another form.

I've run into not updateable query messages. Help suggests a DSUM function, but I can't get it to work. Any help would be appreciated.

 

[neileg]

I don't fully understand what you are wanting. Here's a couple of observations.

It would be much easier to use a form and subform with your tblTitles data in the form and the tblDetail stuff in the subform. You can rely on the parent child links in this situation without needing to code it out. You can include a Text box in your subform to sum the details. You could put this on the main form if you want. Where there is no detail data, you just get an empty subform. You won't need to store the total since it will be calculated for you.

You should bind your forms to queries not tables. There are things you can do in queries that you can't do with tables. Anything you can do with a table, you can do with a query based on the table.

 

[aleb]

I am sorry for pointing it out but ... one thing is to display the total of details on the main form and another thing is to actually place this value in a field.
I guess you are moving in a wrong direction.
The reason why I am saying this is: Let’s say you placed some value in the main table current year amount field. in details you have blank line ... well if you pull this record the field will be overwritten, Besides you have some value in the past year field someone in details adds the line for the current year value oops you have past year overwritten now. I would really suggest as President Putin said: "Keep the flies and beefsteaks separately!"

 

[rpadams]

There's more to it unfortunately

To Neileg:

I agree that the form-subform would be a lot easier, but here is the problem. The "Titles" field are budget categories for a very large school district and there are hundreds and each differs by only one digit in a string eg. 2110-490-02-0012., 2110-491-02-0012. Even the word descriptions are sometimes very similar and it is difficult to be sure you have exactly the one you need among a host of similar one. Operators are continually trying to decide how to code a certain proposed expenditure into very similar categories. It is much easier for the operator to be able to filter a list and see a short list to select the exact one needed. By showing similar titles, the operator can exclude some and get help in the selection.

I use cascading combo boxes to narrow the search, but operators still like to scroll up and down. This is why I need a multi-line form for the Titles. If I could find a way to have a multi-line form with a subform, it would be perfect but unless I'm missing something, the main form must be a single record. This is why I used the two linked forms on the screen at the same time and this was the genesis of perhaps my start down the wrong path.

Maybe I should have asked:

Does anyone know how to make a form, sub-form where both components support a multi-line list?

To Aleb:

You are right, but for brevity I did not note that I actually have TWO fields for the budget amount: The undistributed "Total" (with no detail) and another for the Sum of the Detail (which this question was about). The actual budget amount is the sum of these two which I can get via a query. This avoids the overwrite problem you spotted (and I did as well). I still would like to know how to sum a control on a form and update another table. This could have other uses, even if just illuminating how Access does things.

Thanks to both of you.

 

[neileg]

I use cascading combo boxes to narrow the search, but operators still like to scroll up and down. This is why I need a multi-line form for the Titles. If I could find a way to have a multi-line form with a subform, it would be perfect but unless I'm missing something, the main form must be a single record. This is why I used the two linked forms on the screen at the same time and this was the genesis of perhaps my start down the wrong path.

I understand, but why not use a form that just contains the combos, and then open a new main/subform with the relevant data as I suggested? I have a number of applications where I have used this approach. I find that it's easier to control what's going on with a series of forms that follow on from each other than to have two (or more) synchronised forms in view at the same time.

 

[aleb]

Try this approach