Data copy from one field to another

[djRickAtlanta]

I'm stumped. Hopefully, someone can point me in the right direction.

I've used the music database template as a starting point for a database
that will catalog my vinyl music. On the main data entry form (called
RECORDINGS) the first field that comes up when you add a new record is
"RecordingTitle". Since I'm cataloging vinyl 12 inch singles the data
entered will be the song title for the main track on the 12 inch.

At the bottom of the data entry form is a window to another form (called
TRACKS). The columns that I've set up include Title, Version and Length.
All of this is working just fine, however, 95% of the time whatever I've
entered as the data for "RecordingTitle" is the same as the data that I enter
in the Title column in the TRACKS area.

So, the question is how to take the data that I enter in RecordingTitle and
have it automatically show up at the Title in the TRACKS section? Please
note that if there are five cuts on a piece of vinyl, three use the same
title, and two others might require different song titles. In short,
whatever the 'title' is that automatically shows up must be able to be
changed.

Thanks in advance for any help.

 

[Ken Sheridan]

In the AfterUpdate event procedure of the RecordingTitle text box control on
the main form set the default value of the Title control in the subform
(that's what the 'window' at the bottom of the main form is). To do this put
code along the following lines in the AfterUpdate event procedure:

Dim frm As Form, ctrl As Control

Set frm = Me.sfrTracks.Form
Set ctrl = frm.Title

If Me.NewRecord Then
ctrl.DefaultValue = """" & Me.RecordingTitle & """"
End If

where sfrTracks is the name of the subform *control* on the main form (i.e.
the control which houses the subform). This sets the DefaultValue property
of the Title control in the subform, so the subform record won't be saved
until you've added data to other field(s) in the same row of the subform or
changed the Title to something else. When a record in the subform is saved
you'll find that the same title will show as the default for the new row
which appears below it. This won't be saved as a separate 'tracks' record,
however unless you add or change data in the new row, so don't delete it from
the new row; that would count as a 'change' and simply save a 'tracks' record
with a blank title if the Tracks table allows a Null title, or raise an error
if it doesn't.

As the DefaultValue will continue to apply to the subform's Title control
when you move to another record in the main form, you want to clear it, which
you can do with similar code in the main form's Current event procedure which
sets it to a zero length string:

Dim frm As Form, ctrl As Control

Set frm = Me.sfrTracks.Form
Set ctrl = frm.Title

ctrl.DefaultValue = """"

This time there is no need to test for the main form being at a new record
as you want the code to execute whenever you move to another record, new or
otherwise.

You might be wondering why there are sets of four adjacent quotes characters
in the above code. Its because the DefaultValue property is always a string
expression regardless of the data type of the underlying field, so needs to
be wrapped in literal quotes characters. A pair of quotes characters
delimited by another pair of quotes characters is interpreted as a literal
quotes characters.

Ken Sheridan
Stafford, England

 

[djRickAtlanta]

Ken:

Thank you so much for the information ... very easy to follow your
instructions and understand 'why' things need to go where etc. I truly
appreciate your taking the time to answer AND explain everything. RICK