Get data from previous records

[crtopher]

once i have started entering data into a new record via a form (ie the record
is already created) i would like to go to another record, pinch some data
from some of the fields in that record and go back to the record i was just
on, & put that data in the equivalent fields. The data i would need to pinch
would be the same each each time (ie i don't need to select each time what
fields to import).

i can already go to the record i need to by means of a search combo box on
my form, but i don't know the way to copy the data and take it back to the
other record.

I am very basic with access....i can make forms and can edit the VBA behind
it a bit!!

Please be kind

Cheers
Chris

 

[Allen Browne]

Sounds like you want to set the Default Value for several of the controls on
your form, based on the data in a previous record.

You could add a command button to the form, and use it to set the
DefaultValue property of the control that had focus before you clicked the
button, i.e. Screen.PreviousControl. Then choose another control, and click
the button again to set that one, and so on.

DefaultValue is a string type, so you may need to add quotes around numeric
or date values to get the result you expect.

Also, add error handling in case PreviousControl does not resolve to
something with a value (e.g. another command button, or there was none.)

Here's an example of setting DefaultValue:
http://www.mvps.org/access/forms/frm0012.htm

Here's another alternative, that automatically carries data forward from the
last record, though that's not what you asked for
http://allenbrowne.com/ser-24.html

Note that these do not survive closing the form, unless you store the value
in a table in Form_Unload, and assign them again in Form_Load.

 

[John W. Vinson]

once i have started entering data into a new record via a form (ie the record
is already created) i would like to go to another record, pinch some data
from some of the fields in that record and go back to the record i was just
on, & put that data in the equivalent fields. The data i would need to pinch
would be the same each each time (ie i don't need to select each time what
fields to import).

It's not quite clear if this is what you mean, but you can set the default
value of a field in the AfterUpdate event of the form, so that the field will
be "sticky" - each time you enter a new record it will offer you the value
from the previous record as a default (which can be changed and become the new
default).

If you're trying to pinch PART of a field, that's harder, and it really
suggests that your table design may not be optimum. What is the nature of the
data and what would be carried over?

John W. Vinson [MVP]

 

[crtopher]

Thankyou John and Allen.

My problem is that i am editing an existing record and need to go to another
record to get the data (and i need the whole field not just part) then go
back to the record i was just editing and paste the data....the problem, as i
understand it, is that by setting the default value, that only works when you
go to a new record...the default value won't come across to an existing
record, the feilds of which may, or may not, already have data in them.

 

[J_Goddard via AccessMonster.com]

Hi -

Sounds to me like Edit - Copy (Ctrl-C) and Edit - Paste (Ctrl-V) would work;
that seems too easy - am I missing something?

John

Thankyou John and Allen.

My problem is that i am editing an existing record and need to go to another
record to get the data (and i need the whole field not just part) then go
back to the record i was just editing and paste the data....the problem, as i
understand it, is that by setting the default value, that only works when you
go to a new record...the default value won't come across to an existing
record, the feilds of which may, or may not, already have data in them.
[quoted text clipped - 14 lines]

John W. Vinson [MVP]

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Allen Browne]

Hmm. So you need to open another instance of the same form to a different
record, and duplicate the particular fields onto the original instance while
it is still being edited.

It is possible to programmatically open another instance using the New
keyword. But I think it would be easier to create another form for this
purpose - perhaps one in Continuous Form view, so you can easily spin
through to the desired record, and then click the button on that form to
copy those values into the other form. One advantage of this approach is
that you can open this form in dialog mode. The code for the button's Click
event procedure would be really quite simple, e.g.:
With Forms("NameOfYourOriginalFormHere")
!SomeField = Me.SomeField
!AnotherField = Me.AnotherField
'etc
End With

If you really want to pursue opening another instance of the same form,
here's some info on that:
Managing Multiple Instances of a Form
at:
http://allenbrowne.com/ser-35.html

 

[crtopher]

Allen thank you...that sounds like a plan. I'll make another form to search
for the record, part of which i want to copy, then use your suggested code to
drop that back into the main form, which will obviously still be on the
record i was editing.

I'll try and get back to you...

 

[crtopher]

Hi John

I have to copy data from several fields so i don't want to be flipping back
and forth several times to collect all the data i need

Cheers
Chris

Hi -

Sounds to me like Edit - Copy (Ctrl-C) and Edit - Paste (Ctrl-V) would work;
that seems too easy - am I missing something?

John

Thankyou John and Allen.

My problem is that i am editing an existing record and need to go to another
record to get the data (and i need the whole field not just part) then go
back to the record i was just editing and paste the data....the problem, as i
understand it, is that by setting the default value, that only works when you
go to a new record...the default value won't come across to an existing
record, the feilds of which may, or may not, already have data in them.

once i have started entering data into a new record via a form (ie the record
is already created) i would like to go to another record, pinch some data

[quoted text clipped - 14 lines]

John W. Vinson [MVP]

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[crtopher]

Hi Allen

Just wanted to say your suggestion was perfect...just what i needed was the
idea to open another form. I put a search combo on it to find the record i
needed then used your little bit of code to take the values over to the first
form. Worked beautifully. Thank you

Cheers
Chris