Duplicate several fields in a new record

[Guest]

Hello everyone,
I am working in Access 2002 and would like to duplicate 15 of 50 fields into
a new record. I would also want the new record to open in order to edit. Is
there a way to do this?
Connie

 

[John Vinson]

Hello everyone,
I am working in Access 2002 and would like to duplicate 15 of 50 fields into
a new record. I would also want the new record to open in order to edit. Is
there a way to do this?
Connie

This sounds like you may have a design problem. If you have 15
repeating fields, and 35 unique fields, it certainly sounds like you
should consider having TWO tables in a one-to-many relationship;
you'ld enter the 15 fields once, not duplicate them at all, and use a
Subform to enter the remaining fields. Even 35 fields is a much wider
table than I usually like to see!

That said... you can do this, if you insist; use a Form to do your
data entry (good practice in any case, and only a Form has tools to
let you do this). Use VBA code in the AfterUpdate event of each
control that you want to carry forward:

Private Sub controlname_AfterUpdate()
Me!controlname.DefaultValue = Chr(34) & Me!controlname & Chr(34)
End Sub


John W. Vinson[MVP]

 

[Guest]

Hi John
Thanks for your response. Actually, I do want the 15 records to repeat but
only because it is a family and the address should be the same for each
family member. Each family member is to have their own seperate record, and
a second table is unnecessary. So ideally, what is needed is a button that
allows users to "add a family member", but only duplicates the last name,
address, and contact information. Is there anything I can do to resolve this?

Thanks in advance.....I am away now for 4 hours. Will pick up the thread
then.
Connie

 

[John Vinson]

Hi John
Thanks for your response. Actually, I do want the 15 records to repeat but
only because it is a family and the address should be the same for each
family member. Each family member is to have their own seperate record, and
a second table is unnecessary. So ideally, what is needed is a button that
allows users to "add a family member", but only duplicates the last name,
address, and contact information. Is there anything I can do to resolve this?

I still suggest that you are lumping two valid entities - Households
and People - into one table, and that for many applications it would
be inappropriate to do so.

What you could do is code like the following:

Private Sub cmdNewFamilyMember_Click()
Dim strSQL As String
Dim Q As String = """" ' define the " character
strSQL = "INSERT INTO Contacts " _
& "(LastName, Address, ThisField, ThatField, ..., LastField) " _
& "VALUES ("
strSQL = strSQL & Q & Me!txtLastName & Q & ","
strSQL = strSQL & Q & Me!txtAddress & Q & ","
strSQL = strSQL & Q & Me!txtThisField & Q & ","
<etc>
strSQL = strSQL & Q & Me!txtLastField & Q & ");"
DoCmd.RunSQL strSQL
Me.Requery
DoCmd.GoToRecord acLast
End Sub

John W. Vinson[MVP]

 

[Joseph Meehan]

Hi John
Thanks for your response. Actually, I do want the 15 records to
repeat but only because it is a family and the address should be the
same for each family member. Each family member is to have their own
seperate record, and a second table is unnecessary.

That is exactly the situation that you DO want two tables.

Let's look at what will happen. You have a family and they move. You
need to change all the records to reflect the new address. Miss one and you
have an error.

Now let's say you have two related tables. Now you change one record
and all are correct.

You are thinking like a spread sheet, not database. Please seriously
consider "normalizing" (that is what they call setting up tables properly)
your data. It will make many things easier and faster. Trust us on this,
we have all been there and most of us have learned the hard way.

Access even has a Wizard to help you out. Just go to Tools - Analyze -
Table. Work with a copy of your database and I suggest you read up on
Normalizing first.

 

[Guest]

Hi Connie,

Without commenting on the possible database design issues that John Vinson
and Joesph Meehan have mentioned, here is a link to a KB article that you may
find helpful:

How to fill record with data from previous record automatically
http://support.microsoft.com/?id=210236


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thank you all for you responses. Tom, excellent! I have gone to the site
and it is exactly what I need.