My form text box field was recently 3

[spoon1]

My form text box field was recently changed from data entry type to a function that displays a result. The table name and report that relates to this form was not altered.
****
My problem is that the form displays the data correctly, however this data is not displayed in my table, or report (the report uses a query, based on the table). My table fields relating to this form field is blank, I can't figure it out- I checked all my properties, and they seem to be in order- HELP PLEASE!!

 

[ZmrAbdulla]

Hi,
Calculated fields that displays data properly on form not on table
You might have done the calculation using the
"Expression builder".
You must use the calculation using code.
I'm using a code to calculate as below. It shows the result both in the form and table. Try this.

Private Sub Rate_Change()
If Rate <> &quot;&quot; And Quantity <> &quot;&quot; Then
Amount = Format(Val(Rate * Quantity), &quot;###,##0.00&quot
Else
Amount = &quot;0.00&quot;
End If
End Sub


I'm using this to calculate amount of the item purchased.
There is some more in addition with this.
but the main part is the Format calculation

try to write code for your purpose.

ZmrAbdulla

 

[spoon1]

Thank you Abdulla, you're correct I did use expression builder to create the desired result on the form. The expression was a very simple one: = Date() + d,30 which is to add 30 days.
So.. why do you think it would work with VB code and not an expression from the builder?

Do I create the code in the expression builder?

can you help me with the code? (Not a programmer) Thank you.

 

[ZmrAbdulla]

Hi,

Sorry I'm not familiar with date calculations.
I'm sure some one else can help you.

There is an FAQ under
Microsoft: Access Modules (VBA Coding)

Topic 8 a
Calculationg a future workday date.

Check this it may help you.

Regards.

ZmrAbdulla

 

[JoyInOK]

You created the expression to populate the field, but it sounds like you didn't bind the control to a field in the table. You can tell a control is bound by looking at its control source - it will be the name of a field.
One workaround: Add your table field to the query. In the one of the events of the calculated control, update the table field:

[NameOfFieldInTable]=Me!NameOfUnboundTextBox.text

Depends on how your users navigate the form as to which event you would put that in.

 

[spoon1]

I did bind it.
If I add a new field to a form, why cant I get it to relate to a new column in a table?? Access is a weird program. I want to do what seems like a simple thing: I created a function on a form, and I want to pull that data to a report-- but I can't get a table to buy it, so it seems like it doesnt exist, since the only data allowed for report creation is a query or a table. I need the users input to create the data I need, so I have to use the form. ???????

 

[JoyInOK]

If the Control Source of the text box is an expression, it is not bound to a field in the table, and the text displayed in it will not write to the table. ZmrAbdulla wanted you to put your calculation in VB so that you could set the control source for the text box to the desired field. If your expression works to display the calculated total in the field, you really only need one line of code (to update the field in the table).
First, if your form is based on a query, make sure the table field you're wanted to update is in the query.
In the After Update event of any textboxes displaying data used in the calculation, put this line of code:

[NameOfFieldInTable]=Me!NameTextBoxThatContainsExpression.value

that way, if a user changes the data, the expression will be recalculated, and the field in the table will be updated.
More detail, since you have not programmed before:
Select the field used in the expression, then from the Properties form, choose the After Update event. Choose Code. Type the line of code in the subroutine that opens. Repeat for any other fields used in the expression.

 

[Hap007]

Dear Spoon,

You are trying way to hard to solve your problem.

Do this:
1) Set the Control Source back to the date 'Field' from your query.
2) In the properties of the control, place your calculation:
default value = Date() + 30

Now, on new record, the default wil automatically fill.
When the record is saved, your new value will be stored.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[JoyInOK]

Hap, that's a good solution.
Only thing I was worrying about was if the user revisits the record on a different day, and we need to change the field to be thirty days from the current date, it won't get automatically updated with the default value because there's already a value in there. That's why I would want the field to be recalculated each time. But looking again at Spoon's statement of the problem, it probably won't be an issue here.
So, I think your solution will do it, and I worry too much!
Thanks

 

[Hap007]

Spoon,

Just place a command button next to the date field and
enter in the code for 'On Click'
me.DateField = Date() + 30

Then, the user can refresh if they so desire.

Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[TJones76]

Hi!
I'm having trouble with this same issue of having a calculation text box (or actual field calculation) display the result in the table data. I've read all of the above suggestions but cannot seem to get this to work.

Here's what I have:

Form based on Query
Field 1 = Table data
Field 2 = Text box with calculation as control source
Fields 3, 4, 5 = fields involved in the calculation (all three multiplied together and divided by 60)

Can you tell me step by step how to get my calculated text box value (Field 2) into the corresponding table data (Field1)?

Thank you so much!
Tiffany

 

[psemianonymous]

Assuming you're doing this in a form, this will work. If you are able to enter data into this table ANYWHERE ELSE, this abstraction will break, and your program is buggy.

1. Include a hidden textbox on your form that is bound to Field#1. Let's call it txtFieldOne.

2. Let's call your Field#2 textbox &quot;txtCalculated&quot;.

3. On the Form_BeforeUpdate() event, do:

txtFieldOne.Value = txtCalculated.Value

See the built-in help on the BeforeUpdate() event to see exactly how it works. Also, note that any pre-existing data in the table won't have the special calculation stored in Field#1. So just be careful.