Memo field update

D

douginf

I am having some real problems updating memo field as follows.

25000 records have columns address1, address2, address3, post code ,country
now i want this information to be put in a single invoice field so that users
could copy paste this if they needed too so ithought i would use a memo field
and then use update query to run through database and complete this but i
have tried all sorts and cannot get it to work.

please help
 
S

Stewart Tanner

You don't need to put this in a memo field.

If your users copy from a form, put an edit box on the form and populate the
edit with a calculated expression as they move through the records, or use a
pop up form. Putting this data in a memo field is asking for trouble, in
terms of duplicating data and data sizes, problems with keeping it up to
date, and most data corruption occurs in memo fields etc

Stewart
 
D

douginf

that sounds like a much better idea i have created the form but i have no
idea what the exspression would look like to put place all the data into the
edit box in an address like format.

any help would be much appreciated since access is not my speciality
 
S

Stewart Tanner

edit1 = address1 & vbcrlf & address2 & vbcrlf & address3 & vbcrlf & post
code & vbcrlf & country

where edit1 is the name of your target field
 
D

douginf

ok call me stupid but this is confusing do i add this code into the on click
event to take to the form or do i add it to the text box in some way i am
sorry that you have to deal with me on this level but i just don't get it!
 
S

Stewart Tanner

sorry Doug, I forget that what seems obvious to me is not necessarily
obvious to others. You are not stupid, you are learning and everyone in any
of these groups was once starting out.

you have two option to implement this

1. you make the control source of the edit
= address1 & vbcrlf & address2 & vbcrlf & address3 & vbcrlf & postcode
& vbcrlf & country
and then in the on current event of the form you put
edit1.requery

in this case the expression that you need is the control source of the edit
box that you are going to use for the display.

or

2. in the on current event of the form you put
edit1 = address1 & vbcrlf & address2 & vbcrlf & address3 & vbcrlf &
postcode & vbcrlf & country
in this case the edit box has no control source and we are just using code
to place some text into the box.

does this help at all?
 
S

Stewart Tanner

Hi again Doug,
I am trying to create a running total on the form from three numeric
Click to expand...
values
does this mean you are summing three values from the same record or that you
are calculating from multiple records.

If one record the approach is the same.

if from multiple records you can do as you suggested make a query or you
could have a look at the DSUM function in help.

Without more information it is hard to suggest anything else.

Stewart
 
D

douginf

i forget that being vague does not help you.

i have four numeric fields within the same record and i am just trying to
create a runnning total of them and display at the bottom of the form. i
will try with the same approach as yesterday but i feel that i will have to
add a sum command in there somewhere.

so i hope this will work.
 
S

Stewart Tanner

Doug,

the thing that keeps scaring me is the word sum.

If you want to do the same thing as yesterday use edit = field1 + field2
+field3 + field4
notice here we are using the plus symbol for addition rather than the &
symbol for concatenation of a string.

you would need to put this in the oncurrent and the after update events for
all four fields.

if you are feeling very brave you could put it in a function or sub and call
that from all 5 places, this makes it easier to maintain your code.

eg
Private sub CalculateInvoiceTotal()
edit = nz(field1,0) + nz(field1,0)+nz(field1,0)+ nz(field1,0)
end sub
note the NZ function replaces null values with zero. Nulls propagate nulls
so any calculation that has a null value referenced in it results in a null
output.
then in each fields after update event you call the procedure
eg
Sub Field1_afterUpdate()
call CalculateInvoiceTotal
end sub

did that answer you?

Stewart
 
D

douginf

thanks again fantastic run though code i seem to be more at ease using the
code rather than the expression thing which i just don't get.

maybe in the future i will understand it more.

anyway thanks again
 
Top