Doing a calculation on a form

[robsworld78]

I have a form which I need to do a calculation on a field and have it write the answer to the table. I can do it if everything is on the same form or subform using

Me.BarsSold = Me.BarsLeft - Me.BarsReturned.

But I can't do it when there on different subforms. I took a snapshot of the form and added some notes on it so you can see better what I'm talking about. It should be attached below.

 

[John Big Booty]

This link should help you get the correct syntax for referring to the control on the various sub-forms.

 

[stopher]

I have a form which I need to do a calculation on a field and have it write the answer to the table. I can do it if everything is on the same form or subform using

Me.BarsSold = Me.BarsLeft - Me.BarsReturned.

But I can't do it when there on different subforms. I took a snapshot of the form and added some notes on it so you can see better what I'm talking about. It should be attached below.

John's answer will give you the syntax you need.

However, the way I read you question suggests you are trying to store the result in your table. Is that correct?

If so then it's generally considered good practice not to store calculated values in tables. Instead just calculate them when needed e.g. just calculate them to display in your form.

Apologies if I've misinterpreted you question.

Chris

 

[robsworld78]

Thanks for the quick replies, I'm going over that page and will try some. I know its not good to store calculations but this is a special case. The db was originally designed so at the end of the night a person would enter barssold so all the queries, forms and reports use the field barssold from the table. Now we need to change it so people enter how many they returned instead as people couldn't track how many they sold, returns works cause they count what they have left and enter it in.

Now because everything was built on BarsSold that form has to write that calculation to the table otherwise the whole db needs to be redone. Its only this form I have problems with. The db is quite complex so I don't want to be redoing it.

I noticed on that link it talks about 2 subforms does much change with 3 subforms?

 

[robsworld78]

Do I want to refer to a control or record source?

I've tried all the control ones and nothing works.

 

[robsworld78]

I'm getting closer, if I use the code below it works but on the subform with the column "Given" it takes the first record, 33, and uses that for all the records on the outline subform where I'm enter the returns.

Me!BarsSold = Forms!NightCount!NightCountInventorySubform.Form!BarsLeft - Me!BarsReturned

Any ideas why?

 

[robsworld78]

So close I can feel it. I just discovered that on the subform with "given" column if I select the 2nd record it will use the number in that record, if I select the 3rd record it will use that number. Now I need to figure out when I select a record in the outlined subform the same record gets selected in the subform with given on it.

Can that be done?

 

[Galaxiom]

A reference to a control on a subform will be to the current record of the subform.

 

[robsworld78]

sorry that makes no sense to me, I read it 50 times.

 

[Galaxiom]

Subformcontrols have LinkMasterFields and LinkChildFields properties. These connect the subform record having the matching LinkChildFields to the main form's LinkMasterFields.

So when the main form record is selected the subform follows. If necessary you can have multiple link fields which are entered separated by semicolons.

 

[Galaxiom]

Forms!NightCount!NightCountInventorySubform.Form!BarsLeft

This expression will return the value in a control named BarsLeft from the Current Record of the subform displayed in the subformcontrol called NightCountInventorySubform

You can use the LinkFields to make the subform only show the records which match the main form's current record. If you use the Product fields then the subform will only show the matching product and hence the Current Record in the subform will be the matching one.

However I suspect you may be approaching problem from the wrong direction because of the late requirement to change the way the quantities are calculated. Sometimes a kludge like this can be more trouble than reworking the underlying data structure.

 

[robsworld78]

That's cool, I've been playing lots with that thinking it had something do with it and thought it would be nice if 2 things could go there but didn't think it was possible, I'm a newbie of course.

I tried a few different 2nd links but couldn't get any different results. What would I use as the link, the original is OrderID, I tried orderID to the given subform and I tried inventoryID to the given subform thinking it would match each item but nothing. If I only link the InventoryID to the given subform I only get 1 line in the outlined subform for the 1st record.

This is what I was doing.

MasterLink
[NightCountOrdersSubform].Form![OrderID];[NightCountInventorySubform].Form![InventoryID]
ChildLink
OrderID;OrderID

2nd link also with InventoryID

 

[robsworld78]

yeah that's what its doing from the current record, if I select another record because it becomes current it uses that number.

The subform is showing only the records I need it to, it can't show any less.

There must be a way that when I select record #3 in the outline subform it also sets focus on record #3 on the form with "given" column. Both subforms have the same info to identify them with.

Can't something be said if "InventoryID" suchandsuch is selected in "returns subform" then select record with same "InventoryID" in "given subform"?

 

[Galaxiom]

The ChildLinks must be controls or fields in the subform held by the subformcontrol.
The MasterLinks must be controls or fields on the main form that holds the subformcontrol.

Sometimes it will work if the MasterLink is in another subform but it is not reliable. This is due to the loading order of the subforms.

To MasterLink to a control on a subform, create a textbox on the main form with the ControlSource refering to the control on the subform. Then make that textbox the MasterLinkField of the other subformcontrol.

 

[robsworld78]

A lot of my MasterLinks are in another subform and I haven't had trouble yet, they all link the way I expect them to. Maybe its because I'm linking in the order I'm putting a new subform on the mainform.

Anyways if I put a textbox on the main form and refer to the controlsource "InventoryID" which I think you're talking about I'll only get the 1st record because the main form isn't continous and only the 1st ID would should up or be used?

 

[Galaxiom]

There must be a way that when I select record #3 in the outline subform it also sets focus on record #3 on the form with "given" column.

This requires VBA in the OnCurrent procedure of the subform object.

Use the FindRecord Method to move the CurrentRecord to the matching record.

I still think you should step back and rethink your approach to this problem. Unfortuantely I don't yet have enough of a picture to suggest how you should be doing that.

 

[robsworld78]

I'll see what I can do with that, I'm just googling it as well.

I know this might not be proper but if it works what makes it not proper?

So far I think its great, someone can enter how many returned and it figures out how many were sold, writes that number to the table and all my queries, forms and reports still work. I have about 140 different objects and I wouldn't know where to start editing.

I know the correct way would be not having barssold in the table and use the queries but its too late for that. People don't want to pay so bandaid it is.

I can't see any problems because once someone enters there numbers on that form and posts them its gone and can't be retreived again so there will never be a need to edit what was put it. If there was a need then problems would exist, big problems.

Well I think I'm on the home stretch this was tuff for me to figure out and seeing what I've done it wasn't all that hard. Thanks for all your help I'll let you know if I get it.

 

[Galaxiom]

A lot of my MasterLinks are in another subform and I haven't had trouble yet, they all link the way I expect them to. Maybe its because I'm linking in the order I'm putting a new subform on the mainform.

That has been my experience too. If the subform is added later it seems to cause trouble.

Anyways if I put a textbox on the main form and refer to the controlsource "InventoryID" which I think you're talking about I'll only get the 1st record because the main form isn't continous and only the 1st ID would should up or be used?

If I understand you correctly I think you are saying the right thing but I am not clear why you think the form type matters. Regardless of the form type the recordset will contain all the matching records.

However only the CurrentRecord can be referenced so you need the focus to move to the one you want if there is more than one.

 

[Galaxiom]

I know this might not be proper but if it works what makes it not proper?

As Linq says : There is always more than one way to skin a cat.

So far I think its great, someone can enter how many returned and it figures out how many were sold, writes that number to the table and all my queries, forms and reports still work. I have about 140 different objects and I wouldn't know where to start editing.

I know the correct way would be not having barssold in the table and use the queries but its too late for that.

It doesn't really matter what you store and it is perfectly normalized to store the calculated field since you don't store the fields they are derived from. It only becomes denormalized when you store both the entry and the calculation.

I think all you really need to change is the data entry form.

I suspect the BarsReturned should simply be an unbound control on the bound subform. Use VBA to write the result of the calculation to the BarsSold that you are storing.

I don't understand why you have the subform for the calculation.

People don't want to pay so bandaid it is.

Don't you hate that?

 

[robsworld78]

That has been my experience too. If the subform is added later it seems to cause trouble.

Good to know!

If I understand you correctly I think you are saying the right thing but I am not clear why you think the form type matters. Regardless of the form type the recordset will contain all the matching records.

However only the CurrentRecord can be referenced so you need the focus to move to the one you want if there is more than one.

I'll try this if I can't get record find to work, which of course at this point is giving me an error.

It doesn't really matter what you store and it is perfectly normalized to store the calculated field since you don't store the fields they are derived from. It only becomes denormalized when you store both the entry and the calculation.

Thank-you! Someone understands. I am actually storing the barsreturned though, that was just so I could have a spot for each record. To me not a big deal, its never recalled in the db again. I'm not great with VB so this way was simple and works.

I think all you really need to change is the data entry form.

Yup that's it. It was looking scary for a while though, but always knew it could be done.

I don't understand why you have the subform for the calculation.

The subform for the calculation is because that subform can't be updated or at least the query used on the subform. The field "BarsLeft" or column "Given" is sumofbarsgiven - sumofbarssold = barsleft.

I know the whole db couldn't have been done better but I'm pretty happy with it, its quite complex and only my 2nd db. My first was simple. This one is inventory control, it tracks inventory, it does trailer level, as many freezers as you want, vans and vendors and then an overall level. At at time you can see who has what and where it all is. Took me about 6 weeks to build.

Can't wait to apply what I've learned to my next one, one day I'll be as good as you.