copy one field to another on same form

[John S]

Probably trivial - but this is getting annoying. I've searched this forum & Access help files, but can't find a solution.

I have a form where all the fields refer to a single table Members. One field has control source LastName, and another is ChildLastName.
I am trying to do the following whenever I enter a new record-
every record has a value entered for LastName. I simply want this value to be copied to ChildLastName automatically once I leave the LastName textbox, but for ChildLastName to remain editable in case they need to be different.
I've tied various default value and on exit settings, but just get blank field or error messages.

Using Access 2000.
Grateful for any help

John
Bristol UK

 

[D-Fresh]

In the AfterUpdate event of the LastName field, put the following code...

me!ChildLastName = me!LastName

That should do it for you. Hope that helps.

Doug

 

[John S]

Thanks Doug - very nearly there - this puts the value of LastName into ChildLastName, as required, but it only does so when I start a new record.
I'd like to be able to get this to happen as soon as LastName has been entered. I've also tried putting the code into 'on lost focus' and 'on exit', and also on other fields, but still can't get it to work until I start a new record.
Any ideas?

John

 

[MSUKevin]

John S,

Try putting the code in the 'OnChange' event, that should take care of your problem...

HTH,
Kevin

 

[John S]

Thanks Kevin - I'm afraid that doesn't work either - it does copy the text from LastName into ChildLastName, but only after I move to a different record & then back again.

Still hoping for a way of putting a value into LastName, tabbing to the next field (Address1), and immediately seeing the ChildLastName field further down the form filled with the value from LastName.

I'm sure it must be simple, but can't see it yet...

John

 

[D-Fresh]

Is the last name already in the underlying table? And are you just trying to get that name to move on to the other field? If so, you should use an update query, to update the ChildLastName to LastName.. Hope this is what you were looking for...

 

[jstutz]

Maybe I've misunderstood what you are trying to do, but I think you need to use the AfterUpdate event of the LastName field. It should read:

Me.ChildLastName = Me.LastName

Is this it?

jamie

 

[John S]

Thanks - still not quite there yet

D-Fresh - no, there is nothing in the table for a new record until I enter a value for LastName - which I then want immediately to appear as well in the ChildLastName box on the same form as soon as I tab away from the LastName box. Both values should then get written to the table when I move to a different record.

jamie - thanks - when I used the code exactly as written I got a compile error (member not found). Changing the Me. to Me! fixes the compile error - but still doesn't fill the ChildLastName field immediately. I've got:

Private Sub LastName_AfterUpdate()
Me!ChildLastName = Me!LastName
End Sub

in the AfterUpdate properties of LastName. The correct value does get copied to ChildLastName - but only after the form is redrawn (minimise / maximise, or move back / forward to a different record).

Any further ideas, anyone?
TIA - John

 

[Fiona]

I have something similar in a form of mine but I put the code in On Exit instead of the After Update bit of the field and it works fine.

Try Me!ChildLastName = Me!LastName there and see if it works.

Hope that helps,

Fiona

 

[wh00t]

You could try setting up a macro to refresh records and run the macro in the on exit event, this works when I use DLookup on forms, may work here.

Gareth

 

[jstutz]

John, I wonder if you have some sort of problem with your Access installation. The code snippet I gave you yesterday when called from the AfterUpdate event caused the LastName value to be copied into ChildLastName as soon as I tabbed off the field, which is what it's supposed to do. Here's what Access help says about event orders. Based on what they say, the AfterUpdate event SHOULD be firing (ie. populating ChildLastName) when you tab off the control:

-------------
As you move the focus among the controls on a form, events occur for each control. For example, the following sequences of events occur when you:

Open a form and change data in a control:
-Current (form) ->Enter (control) ->GotFocus (control) ->BeforeUpdate (control) ->AfterUpdate (control)

Move the focus to another control:
-Exit (control1) ->LostFocus (control1) ->Enter (control2) ->GotFocus (control2)

Move the focus to another record:
-BeforeUpdate (form) ->AfterUpdate (form) ->Exit (control2) ->LostFocus (control2) ->Current (form)
--------

In your HELP -> ABOUT ACCESS screen, what version of Access are you using? I'm using MS Access 2000 9.0.3821 SR-1.

Maybe a re-install of Access is in order????

 

[John S]

Finally sorted it, by adding the line:
Me.Refresh
to the OnExit code suggested above - and it works!

many thanks to everyone who responded.

John