unbound textbox that updates a bound textbox

[MSUKevin]

I'm sure this is a simple question for most of you but this has been driving me bananas.... Please Help.

My problem is this:

I have an unbound text box that preforms a calculation on a form. What I want to do is store this value in a table for later use. The problem is I don't know how to make this value go to a bound text box.

Any help would be appreciated

 

[KevinM]

Can't you use the calculation in a query expression rather than as an unbound text box on a form?

 

[MSUKevin]

No I can't... I'm already too far down the line. I tried to use a set value macro but I keep running into problems with Null values. If you have any suggestions PLEASE HELP!!!

I've gotten myself into deep doo-doo here!!!

 

[charityg]

on the before update of the form

boundfieldname=me!unboundtxtbox

 

[KevinM]

'Too far down the line' to make a simple expression in a query and then basing your form on this query ??

This should take only a matter of minutes and will not affect the structure of your db.

This is your best option.

 

[MSUKevin]

charityg,

I tried the expression:

boundfieldname=me!unboundtxtbox

in the before update field for the form but I get the error message:

Access can't find the Marcro: boundfieldname=me!unboundtxtbox


If you have any other suggestions please let me know I'm on a short deadline.

 

[KevinM]

Have a button on the form that adds the value to your table.

On the OnClick event of this button put....

Dim db As Database
Dim rst As Recordset

Set db =CurrentDB
Set rst=db.OpenRecordset("TableNameToUpdateToHERE")

rst.AddNew
rst![Dest Field Name]=[UnboundTextBoxName]

rst.Update

rst.Close
Set rst=Nothing
msgbox "Record Added"

End Sub

Don't forget to substitute your table and field names with the ones above.

HTH

Kevin M

 

[MSUKevin]

Kevin,
That didn't seem to do it. I'm not sure if I did something wrong or if there was an error in the db. When I tried the button I didn't get any kind of a response... IF anyone has an idea of how to transfer this value please make a suggestion...

Thanks in advance....

 

[charityg]

I gave you the solution, but from your error, I probably wasn't clear.

Before entering any code you need find the names of the fields you are using. Using the properties window find the name of the field bound to the table where you want to store the calculated value (I will refer to this in the code as "txtbound", remember to change it. Next find the name of the unbound textbox that performs the calculation. (I will refer to this in the code as "txtcalculated"), remember to change it.

then:
Open the form properties window. On the Event tab, click the ellipsis (...) to the right of the BeforeUpdate row then select CodeBuilder from the list.
type:
txtbound=Me!txtcalculated

(Remember to change the field names!)

If this doesn't work, I'd have to take a look at the database to see what's going on.

Good luck,
Charity

 

[KevinM]

Are you using A97?

Is the form based on the table you want to update?
If so then charityg's reply will work.

If not, then it won't. Mine will, however it uses DAO which is used by A97 and not A2K (by default).

 

[MSUKevin]

Charity,

I tried to set up the before update again... but it still didn't take. I can't for the life of me figure out what the problem is here. I'm sure it has something to do with null values, but my expertise is limited so I am not sure. If you think you could figure it out by taking a look at what I have, please let me know asap and I'll e-mail at copy to you. I'm sure we can beat this problem, I'm just not sure how!!!

Kevin,
In response to your post. I am using A97 and form is based on the table to be updated.

Please don't give up....we are almost there!!!

 

[russi]

Hi. I think I can help you out.

I have a calculated unbound field on a form that calculates someone's age based on the DateOfBirth field. It is called AgeCalc.

My table has a field called AgeAtApp. On the form, I have the AgeAtApp field with 'visible property' set to 'no'. It comes after the AgeCalc filed, in the Tab list.

Finally, in the 'AfterUpdate property' of DateOfBirth, I have the following code:
Me.AgeAtApp = Me.AgeCalc

This works great in storing the value.

Hope this example is specific enough to help you.

Russ

 

[MSUKevin]

Russ,

I have basically the same set up as you do but with one little difference:

I have a main form with a subform. On the main form we have one bound textbox named "Gasoline Sale". This box is on the main form, and is the one that will be used most often. On the subform we have the additional products. The subform contains the product/unit price/Quantity/Line Total Fields. There is also an unbound txtbox on the subform that calculates the sum line totals.

To get the total sale I had (readast tense) the "sum line total" from the subform and the "Gasoline Sale" box on the main form add into each other. Sounds simple right!?!?!...... WRONG!!!

The problem I have run into is Null Values. Even when I had set the default values to 0, I still got errors when one item was purchased and not the other i.e. (only a gas sale and no products). To fix this problem, I had to write Iff Null statements for both the gasoline sale txtbox and the subform "sum total sale" box.

NOW, the calculation is preformed using these hidden boxes to compensate for any null values that occur. THIS HOWEVER, is (I think) the reason that the Me.Total_Sale = Me.Text101 data transfer will not work. If you, or anyone else thinks they can help further on this issue please let me know!!!!

I have to get this one little hiccup...

Ps. Sorry this is so long but I wanted to explain my problem!!!!

 

[KevinM]

MSUKevin

I think you are trying to do something that is really unnecassary.

You DO NOT need to store this Grand Total in your table. This is a complete NO-NO in database design.

You have this value on your form.
You can also get this value on a report and in a Totals Query.

Why on earth do you need to store it in the table?

What if you have to remove or add a product from a previous record. How can you guarantee that this 'stored total' will always be up to date.

If you didn't store it in your table, then it will ALWAYS be up to date.

 

[MSUKevin]

KevinM,

I hear what your saying and that is a very valid point. The reason for wanting to store this info in the table is so I can do a totals query: (I need to produce queries to calculate employee sales for week, month, year, ect..) I have tried to produce these queries by reproducing the equations in a qeury but I have to use the two unboundtxtboxes that preform the Iff Null statements on my form because the Null values fudge up the totals calculation. I do not know how to get these values into the queries because they are unbound and are not represented in the field lists for query design. I have tried setting up an employee's sales query (based of the design in the Orders Entry sample database) but because of the null values, Access can't calculate the total sales...

If you have an Idea of a way around this, or if you know how I could represent these Null Value statements in the query, I would love to know....

If you think you can help and need more info please let me know...

anxiously awaiting....
Kevin

 

[KevinM]

What is the calculation being performed?

There is no reason why you can't have this calculation in a query.

Have you ever used expressions (calculations) in a query before?

You can perform Total Queries on select queries.

Sometimes it may take more than one query/totals query to 'drill down' to your final result.

If the calculation is too complex then I suggest you put it in a module first and then reference it in the query.

(I still believe this is your best option)

HTH

 

[MSUKevin]

KevinM,

The equation is this:

Total: Sum(([Quantity]*[Unit Price])+[Gasoline Sale])

It doesn't work because if I have ONLY gasoline sales... and no product sales... the null values do not let the calculation preform. I get a total value sum of (blank). If you think you can solve this problem let me know and I'll attach file you a copy of my database and you can take a look at it.

Thanks Again,
Kevin

 

[KevinM]

OK, mail me the db (if it's not too big), i.e. no more than a few meg.

Make sure you do a Comapct first and then zip it up.

I only use A97 and A2000

Also, please mail me more details on what you want, an overview of what the db is storing and reference your problem forms, tables queries etc.

 

[charityg]

if you are transfering an unbound textbox from the subform to a bound field on the main form then the code I gave you is wrong. Should be:
Parent!boundtxtfield=me!unboundtextfield

Don't know if you've ever used it, but the Nz function should compensate for null values

Nz(value,0) :this would assign 0 to any value that is null.

[This message has been edited by charityg (edited 05-31-2001).]

 

[MSUKevin]

Charityg,

You had it right the first time. It's an unbound txtbox to a bound txtbox on the main form. Both are on the main form. BUT, I still couldn't get your code to work.

Even more interesting is that I have used code just like the one in your previous post before on a different db, and it worked fine.

This leads me to believe that I have bigger problems that are not so visible, if you have an suggestions I'd love to hear them, till then....

I'm hopelessly frustrated,
Kevin