Prevent Brackets from auto appearing?

[gucci]

Hi, in this forum already I have being given an solution to a problem and it should work but [] brackets keep appearing in the expression. Here is the expression.

=Forms![VatSub].[AmountExVAT]*Forms![VatSub].[VATRate].Column(2)

But it keeps reverting to this

=[Forms]![VatSub].[AmountExVAT]*[Forms]![VatSub].[VATRate].[Column](2)

Why

 

[boblarson]

That's the actual way Access refers to it, so there's nothing you can do to change it.

 

[gucci]

But it does not work then. Am I doing some thing else wrong so.
Is this valid code for doing the calculation.
=[Forms]![VatSub].[AmountExVAT]*[Forms]![VatSub].[VATRate].[Column](2)
Multiply a textbox AmountExVAT by the value in the third column in the combo box VATRate.

Thanks

 

[boblarson]

When doing calculations on a form, you should make sure that your control names are NOT the same as the field names. So, if you have a text box named AmountExVAT, then you should rename it txtAmountExVAT and then you can use that formula to refer to the FIELD, which is what you want to refer to.

 

[gucci]

OK I tried that and no success. Just to let you know that if I set the third column in the combo box to bound then it works. So does that not mean that i have everything set ok just doing something wrong with the expression when third column is not bound.

 

[boblarson]

maybe, if you can't get it, you can post your database here.

 

[gucci]

should the control name and field name for the combo box be different also.

 

[boblarson]

If the combo box is bound to a field, then yes.

 

[gucci]

what version should I upload it in?

 

[gucci]

Here it is. Sorry about the mess of it but I'm only starting out.

OK sorry I solved that problem. You were right, the problem was with all my choping and changing I changed VATRate to VATRateID and did not realise it.

Thanks for all you help on that one. I still have the header problem, but I might give it a shot myself first. unless you see the solution quickly.

Thanks again.

 

[WayneRyan]

gucci,

Just read a bit of this and can't open your DB, but wanted to add that the
.Column subscripts start at 0. So: .Column(2) is the third column.

Wayne

 

[gucci]

Thanks Wayne, I using Access 2007. Yes I know .Column starts at 0. But if you want you can help with my next issue. Fields calculated in form are not stored in the table? I have 2 fields that are calculated in the form VAT Amount and Total. Everything else is stored but not these. Now I know when it comes to the reports I can have do the calculations again but I would like to know why. This is one of the fields VATAmount. The textbox containing the value is bound to the right field in the table. The calculation is correct as we have allready discussed. But am I meant to enter some thing else in the control source to add the calculation to the table.

Thanks

 

[boblarson]

If you absolutely feel the need to store a calculated amount, then you would leave the control source alone for the current stuff, and add a text box for each calculated field, which can be hidden, and set it's control source to the other calculated control and then in the AfterUpdate event of the control with the formula, put:

Me.YourHiddenTextBox = Me.YourControlWithTheFormula

 

[gucci]

Sorry Bob, just not sure what your saying here. So the VATAmount should stay the same with the =[AmountExVAT]*[VATRateID].[Column](2) in the control source. Then I add a new test box to the form that is hidden and call it VATAmountHidden. Set its control source to =[AmountExVAT]*[VATRateID].[Column](2) also and add =Me.VATAmountHidden=Me].VATAmount to the AfterUpdate.

Is that right

 

[boblarson]

No, VATAmount should stay the same and the new hidden text box will be bound to the field you want to bind it to (no formula in the controlsource, just the field to bind to) and then set the
Me.VATAmountHidden = Me.VATAmount
in the AfterUpdate Event, but I just realized you should have the code as thus:
DoCmd.RunCommand acCmdSaveRecord
Me.VATAmountHidden = Me.VATAmount

 

[gucci]

I'm going to keep trying to get this to work but so far I've had no success. Are you sure this works. The hidden field does not seam to capture the Value to store.

OK I just got that to work, not that I'm going to use it for calculated fields. But I'm wondering will the same system work for this. I have a Undound combo box in the form header. The form is a Continuous Forms. so I put a hidden textbox in the detail section to capture the combo box selected value. And it does but I want this value to be entered into a field in the table. Would this system work for that.

Thanks again.

 

[boblarson]

I'm currently doing something similar in one of my databases, so I know it works. It's just a matter of finding the correct event to put it on and getting the syntax correct. If you post the updated, fixed version of your db (from the other day), I'll take a look when I get a chance.

 

[gucci]

I supose this part should stay the same;
DoCmd.RunCommand acCmdSaveRecord
And because it's taking the info from a combobox Column() will come into it.

 

[boblarson]

DoCmd.RunCommand acCmdSaveRecord is just a command to tell the form to save the record.