Multiply Text Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charles G - 13 Sep 2005 15:09 GMT

I've created a Form, " Account Analysis Input Form," that will populate a
single table, "Account Analysis Input Table." In my Form I have a Text Box
with a Control Source[Boxed Coin].

Can I, for example, enter the number "5" into the text box [Boxed Coin] in
form view, and multiply that by 50 to show 250 in my table?

If so, would this be a calculation that goes into the Default Value field of
my text box? I've tried:
=[Boxed Coin]*50
However, this was unsuccessful.

Name: Text23
Control Source: Boxed Coin
Format:
Default Value: =[Boxed Coin]*50

-Charles-

-Charles-

Reply to this Message

Sprinks - 13 Sep 2005 15:20 GMT

Hi, Charles.

A form textbox can either be Bound to a field, or display the result of an
expression, it cannot do both. Moreover, it doesn't need to.

Store your base number in the table, and calculate the desired amount in a
calculated control on your form, or a calculated field in a query.

Sprinks

> I've created a Form, " Account Analysis Input Form," that will populate a
> single table, "Account Analysis Input Table." In my Form I have a Text Box
[quoted text clipped - 16 lines]
>
> -Charles-

Reply to this Message

Graham R Seach - 13 Sep 2005 15:27 GMT

Charles,

No, but you can enter 50 into an unbound textbox, and use code in its
AfterUpdate or LostFocus event to recalculate the value that will be stored.

For example, create a textbox called "txtBoxedCoin", and bind it to the
[Boxed Coin] field. Set its Visible property = False.

Next, create an unbound textbox called "txtBoxedCoinUB". Its Visible
property should remain = True.

Add the following code to the unbound textbox's AfterUpdate event:
Private Sub txtBoxedCoinUB_AfterUpdate()
If IsNumeric(Me!txtBoxedCoinUB) Then
Me!txtBoxedCoin = Me!txtBoxedCoinUB * 50
Else
Me!txtBoxedCoin = 0
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

> I've created a Form, " Account Analysis Input Form," that will populate a
> single table, "Account Analysis Input Table." In my Form I have a Text Box
[quoted text clipped - 17 lines]
>
> -Charles-

Reply to this Message

PC Datasheet - 13 Sep 2005 17:26 GMT

You're whole concept here seems to be in error. You should not store
calculated values in a table or store a piece of data in more than one
table. Generally you store all data in appropriate tables then you create a
query to gather the data you need for the purpose at hand then do the
calculations in the query or a form or report based on the query.

Signature

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> I've created a Form, " Account Analysis Input Form," that will populate a
> single table, "Account Analysis Input Table." In my Form I have a Text Box
[quoted text clipped - 17 lines]
>
> -Charles-

Reply to this Message

Charles G - 13 Sep 2005 20:19 GMT

>You're whole concept here seems to be in error. You should not store
>calculated values in a table or store a piece of data in more than one
>table. Generally you store all data in appropriate tables then you create a
>query to gather the data you need for the purpose at hand then do the
>calculations in the query or a form or report based on the query.

1 Table
12 fields
1. Primary Key = autonumber
10. Boxed Coin
11. Total Amount of Currency

1 Query:
12 fields
10. Boxed Coin
11. Total Amount of Currency

Should I delete these two fields because they are already in my table?

PARAMETERS [Month of Activity] Text;
SELECT [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], [Account
Analysis Input Table].Volume, [Account Analysis Input Table].[Month of
Activity], [Account Analysis Input Table].Pennies, [Account Analysis Input
Table].Nickels, [Account Analysis Input Table].Dimes, [Account Analysis Input
Table].Quarters, [Account Analysis Input Table].Halves, [Account Analysis
Input Table].[Boxed Coin], [Account Analysis Input Table].[Total Currency
Amount]
FROM [Account Analysis Input Table] INNER JOIN [AA Client List Table] ON
[Account Analysis Input Table].[Client Name] = [AA Client List Table].[Client
Name]
WHERE ((([Account Analysis Input Table].[Month of Activity])=[Month of
Activity]));

Form:
Account Analysis Input Form
Has many fields including a field to enter [Boxed Coin] and [Total Currency
Amount], but does not have a [Volume] field. My report needs to show Volume,
which is [Total Currency Amount] and [Boxed Coin*50]. When employees are
entering data they will be entering either Boxed Coin or Total Currency
Amount, not both.

Reports:
Will be pulled from the Account Analysis Query.

I need a way to calculate the Boxed Coin by 50 in my query and show the
results in my report under [Volume]. Also, as I mentioned earlier, [Total
Currency Amount] will be under "Volume" in my report.

-Charles-

Reply to this Message

PC Datasheet - 14 Sep 2005 00:46 GMT

Having "Total Amount of Currency" in a table is a definite error and having
"Boxed Coin" as a field may be an error. List what fields 2 to 9 and 12 are
and provide any explanation needed.

Signature

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> >You're whole concept here seems to be in error. You should not store
>>calculated values in a table or store a piece of data in more than one
[quoted text clipped - 53 lines]
>
> -Charles-

Reply to this Message

Charles G - 14 Sep 2005 13:36 GMT

>Having "Total Amount of Currency" in a table is a definite error and having
>"Boxed Coin" as a field may be an error. List what fields 2 to 9 and 12 are
>and provide any explanation needed.

I changed some fields around, how does this look?

Tables:
Account Analysis Table
Fields:
1. ID Autonumber
2. Client Name (32 Clients with some sharing account numbers)
3. Service Code (rolled coin = 385 and Total Currency Amount = 390)
4. Volume (Boxed Coin * 50 and Total Currency Amount)
5. Pennies
6. Nickels
7. Dimes
8. Quarters
9. Halves
10. Month of Activity ( January - December) (Parameter in my Query)

Boxed Coin and Currency Table
Fields
1. ID Autonumber
2. Boxed Coin
3. Total Currency Amount

Queries:
Account Analysis Query
Show Tables: Account Analysis Input Table, AA Client List Table, Boxed Coin
and Currency

Fields in Query: From Table
1. Client Name Account Analysis Input Table
2. Client Account # AA Client List Table
3. Service Code Account Analysis Input Table
4. Volume Account Analysis Input
Table
5. Pennies Account Analysis Input
Table
6. Nickels Account Analysis Input
Table
7. Dimes Account Analysis Input
Table
8. Quarters Account Analysis Input
Table
9. Halves Account Analysis Input
Table
10. Month of Activity Account Analysis Input
Table
11. Boxed Coin Boxed Coin and Currency
12. Total Currency Amount Boxed Coin and Currency

PARAMETERS [Month of Activity] Text;
SELECT [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], [Account
Analysis Input Table].Volume, [Account Analysis Input Table].Pennies,
[Account Analysis Input Table].Nickels, [Account Analysis Input Table].Dimes,
[Account Analysis Input Table].Quarters, [Account Analysis Input Table].
Halves, [Account Analysis Input Table].[Month of Activity], [Boxed Coin and
Currency].[Boxed Coin], [Boxed Coin and Currency].[Total Currency Amount]
FROM ([Account Analysis Input Table] LEFT JOIN [AA Client List Table] ON
[Account Analysis Input Table].[Client Name] = [AA Client List Table].[Client
Name]) LEFT JOIN [Boxed Coin and Currency] ON [Account Analysis Input Table].
ID = [Boxed Coin and Currency].ID
WHERE ((([Account Analysis Input Table].[Month of Activity])=[Month of
Activity]));

-Charles-

Reply to this Message

Charles G - 14 Sep 2005 17:33 GMT

I've deleted [Boxed Coin] and [Total Currency Amount] in my query. In my Form,
I've binded my Boxed Coin textbox and Total Currency Amount textbox to [the
Volume] field in the query.

-Charles-

Reply to this Message

PC Datasheet - 15 Sep 2005 02:42 GMT

Your Account Analysis Table is a definite error. You need:

TblClient
ClientID
ClientName
AccountNumber

TblCoin
CoinID
CoinDenomination 'Penny, Nickel, Dime, Quarter, Half

TblCoinPackage
CoinPackAgeID
CoinPackage 'Roll, Box

From here I'm still not understanding service code and volume. Please
explain these and what happens in an activity each month.

Steve
PC Datasheet

> >Having "Total Amount of Currency" in a table is a definite error and
> >having
[quoted text clipped - 81 lines]
>
> -Charles-

Reply to this Message

Charles G - 15 Sep 2005 04:32 GMT

Service Code is either 385 or 390. I have these in a combo box in my form.
385 = rolled coin
390 = currency

After you select 385 for rolled coin, you then proceed to enter the figures
into the text boxes: [Pennies], [Nickels], etc in my Form. If you select 390,
you proceed to enter the [Total Currency Amount] and that's it.

[Volume] is rolled coin x 50. And separately, currency is also volume. In my
form you will either have a currency transaction or coin transaction, not
both. With that being said, binding it to [Volume] shouldn't be an issue.

[Month of Activity]: I have this field in my table and query, but I think I'm
going to delete it from the Account Analysis Table and just have it in my
query. This field states the month in which the transaction was made: January,
February, etc.

I will eventually have to make calculations to the Pennies, Nickels, Dimes,
etc. I’m really interested in learning how to make calculations in forms to
input into a query and run a report from that query. However, my “Access 97
Bible” isn’t very good at explaining the process.

-Charles-

Reply to this Message

Charles G - 15 Sep 2005 21:23 GMT

On my Form based query I have textboxes for these coin denominations:
[Pennies] [Nickels] [Dimes] [Quarters] [Halves] [Dollars] [Boxed Coin]

I have other controls but these are the ones that need to be calculated. I've
tried:
=[Pennies]/.50 in my Control Source for my [Pennies] textbox along with
Before_Update and After_Update events with no luck. I've tried creating and
unbound [txtPennies] with a value of /.50 and putting =[Pennies]*[txtPennies]
in my bound [Pennies] textbox with no luck.

I would love to calculate these in my form somehow so that when I run my
query I can see the results. I will need to actually view the result for
[Boxed Coin]*50 under the Page Header "Volume"

I will not need Pennies, Nickels, Dimes, etc in my report.

I've been reading these forums non-stop for the past week, but cannot figure
this out. My database performs as I would like it, even with the possibility
of it not being the most solidly designed. What gives?

-Charles-

Reply to this Message

Charles G - 16 Sep 2005 18:38 GMT

Thanks to all those who helped. I figured it out.

-Charles-

Reply to this Message