Populating a field in a table from an unbound field in a form.

[Seano]

I have an Orders table which I have created an orders form from.

The form has an unbound field, which I have made calculate a total cost for
the product selected. Once the total cost is calculated, I would like this
value to populate the Total field in my order form and order table.

Can this be done?

Thank you in advance.

 

[Wayne-I-M]

Hi

Yes it can be done very simply but don't do this.
You can get the result you want at anytime you want. If you store the
result and then add another product to it then the field will need to be
updated. So basically its a waste of time doing it.

You can use the result on your form any time you want and you can add it
into reports so there is no reason to do this.

There are a few cases when you may want to save the result of a calculation
(but not many)

 

[Seano]

I would like to test it and see if it works for my application.

Could you possibly tell me how it's done?

Thanks very much.

 

[Wayne-I-M]

There are many ways of putting data into a field - either by pressing a key
or by calculations or by code or by etc etc etc

The most basic example I can think of would be
Say you have a field in a table called OrderTotal
Create a form with this field on then
Add 2 unbound txt boxes (txt1 and txt2)

On the Afterupdate event of txt2 put this
Me.OrderTotal = Me.txt1 * Me.txt2

The result of the calculation would be stored into your table

BUT whats the point you can always see the results anyway - you are using
Products which you can see - the cost which you can see and the order number
which you can see etc
Just add an unbound text box to your form and whenever its opened just have
the results of the order shown on the screen.

Or just do the input on the form and then refresh the form (which should be
based on a query) - you have some calculated columns in your query - you
could then the results of the calculation anywhere in your database.

It may seem simpler at first to store the results but you will have more
problems later on when you application grows in size. Best bet is not to
store results of calculations.

Up to you though

 

[Seano]

Thanks for the response Wayne.

I tried the 2 unbound Text Boxes but the field didn't update in my table.

I'm not sure if I'm asking the right questions for what I need. I'll try
again and see if I make more sense. Pardon my lack of experience ;P

1. I have an orders table which contains Acc.Num, Order.Num, the product
details, Quantity and Price. The 'Total' field is a manually entered field at
the moment.

2. I have created an order form from the above table.

3. I can't seem to make Orders.Total calculate from the entered fields, so I
have created an unbound field text7 to do the calculations for me.

4. Because I need that total to appear in another form, I need the value
from text7 to be written to Orders.Total.

Is this different to what I originally asked?

What am I doing wrong?

Thanks again

 

[Wayne-I-M]

Create a query based on your table - add all the field that are used on your
form
Add a calcuated column - right click a blank column and select build
In the box add this
OrderTotal: [Quantity] * [Price]
Click save
Right click again and select Currency from the format row.
Save the query

Open your form in design view
Right click in the blank area (grey bit) select properties
In the Proerties box select Source
Select the new quuery your have just made
Save the form

In the view drop downdown (top of the page)
select View Field List
Drag OrderTotal onto the form somewhere you want
Save the form


Note you are still in design view
Select the box you manualy enter stuff into 2nd
Say you have
[Quantity]
[Price]
You add the Quantity then you add the price (sorry can't see you application)
So in this case it would be the Price - you can work that bit out
Right click the control
In the properties box - event column select the AfterUpdate row
Right click - select Build
Select Code
You will see something like this

Private Sub NameOfControl_AfterUpdate()

End Sub

Add this between the lines
Me.OrderTotal.Requery

So that it looks like this

Private Sub NameOfControl_AfterUpdate()
Me.OrderTotal.Requery
End Sub

- - - of course change the names to what you have on your form - - -

Save and close this window
Save the form

Open in normal view

Give it a go and see if it's what you need

NEXT
Use this query to add the OrdetTotal to your other form
Link the querys on your Order.Num
TOP TIP - don't use a full stop in a field name
Change it now before you start doing anything else or you'll have to go
through and change lots of stuff - best to do it now.
Something like Order_Num or just OrderNum

Hope this helps