PC Review


Reply
Thread Tools Rate Thread

Re: Copy record with subform

 
 
Roger Carlson
Guest
Posts: n/a
 
      9th Dec 2005
Sorry, Sent by accident.

A couple of your field names have spaces in them: Type Interest should be
[Type Interest] and Depth Limitation should be [Depth Limitation]. I also
steered you wrong on the field list in the INSERT INTO statement. It should
be surrounded with parentheses. The whole statement should look like this:

strSQL = "INSERT INTO Partner_WI_Subtable (Field, Company, Interest, " & _
"[Type Interest], [Depth Limitation]) SELECT Field, Company, " & _
"Interest, [Type Interest],[Depth Limitation] from Partner_WI_Subtable
" & _
"WHERE Field = '" & Me.[Field] & "'"

However, after re-thinking this, I'm afraid there are still complications.
At best, all of your subform records will show, old and new. To counter
that, you should probably programmatically modify the Field field value
temporarily in both the mainform copy and in the subform copy.

So in your mainform copy routine, change the Field assignment to something
like:

Me.Field = rs!Field & "-temp"

Then change the Insert Into statement to:
strSQL = "INSERT INTO Partner_WI_Subtable (Field & "-temp", Company,
Interest, " & _
"[Type Interest], [Depth Limitation]) SELECT Field, Company, " & _
"Interest, [Type Interest],[Depth Limitation] from Partner_WI_Subtable
" & _
"WHERE Field = '" & Me.[Field] & "'"


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L




"emily" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I'm getting 'Syntax error in INSERT INTO statement' for the this part of

> the
> > code. Did I forget a comma somewhere? Thanks!
> >
> > Dim strSQL As String
> > strSQL = "INSERT INTO Partner_WI_Subtable [Field, Company, Interest,
> > Type Interest, Depth Limitation] " & _
> > "SELECT Field, Company, Interest, [Type Interest],[Depth
> > Limitation] from Partner_WI_Subtable " & _
> > "WHERE Field = '" & Me.[Field] & "'"
> > DoCmd.RunSQL strSQL
> > Me.Partner_WI_SUBFORM.Requery
> >
> >
> >
> > "Roger Carlson" wrote:
> >
> > > "Field" is the actual field name? Amazing! <shakes head>
> > >
> > > Well, any answer would need to be highly dependant on exactly how your
> > > database is set up, so I can't give you code. However, I can give you

> some
> > > general ideas. Here's what I'd try:
> > >
> > > Add an Append Query to the end of this code that will copy the subform
> > > records. In general, it would look like this:
> > >
> > > Dim strSQL as String
> > > strSQL = "INSERT INTO TargetTable [Field, Field1, Field2, etc] " & _
> > > "SELECT Field, Field1, Field2, etc from TargetTable "

&
> _
> > > "WHERE Field = '" & Me.[Field] & "'"
> > >
> > > Replace TargetTable with the name of the actual table that the subform

> is
> > > bound to, Field1, Field2, etc with the actual field names. This query

> also
> > > assumes that Field is a text field. If it is numeric, it would look

> like
> > > this:
> > > "WHERE Field = " & Me.[Field]
> > >
> > > To execute this, you can use:
> > >
> > > DoCmd.RunSQL strSQL
> > >
> > > Then you will have to refresh your subform.
> > >
> > > Me.SubformControl.Requery
> > >
> > > As I said, this may not work because of some misunderstanding on my

part
> of
> > > how your tables are set up, but is should give you a start.
> > >
> > > --
> > > --Roger Carlson
> > > Access Database Samples: www.rogersaccesslibrary.com
> > > Want answers to your Access questions in your Email?
> > > Free subscription:
> > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > >
> > >
> > >
> > >
> > > "emily" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Thank you for the books--I will definitely check it out. This is a
> > > database
> > > > that was created by someone else that I am now responsible. As of

now
> > > (and
> > > > until I redesign it) people are going into datasheet view, copying

the
> > > line
> > > > of data that is similar to the new record they would like to create,
> > > pasting
> > > > it at the end of the datasheet, going into form view, copying the

> contents
> > > of
> > > > the subform and pasting them all at the end of the line, renaming

the
> > > Field
> > > > name in all of the copied lines and leaving the originals and then

> renamin
> > > g
> > > > the Field name in the main form so the new entries in the subform

are
> the
> > > > only entries tied to the new record (Field is the field name that

ties
> the
> > > > main form and subform together). There are so many opportunities for

> error
> > > in
> > > > this method that I don't even want to think about it. That's why I

> would
> > > > like to create a button that copies all of the information

> automatically
> > > so
> > > > all the user has to do is change the field name in the sub form and

> main
> > > > form. In the mean time I can be reading Database Design for Mere

> Mortals
> > > and
> > > > figuring out how to avoid this song and dance entirely. Can you help

> me?
> > > > "Roger Carlson" wrote:
> > > >
> > > > > But what if one of the fields that is NOT supposed to change is

> somehow
> > > > > changed (or deleted) inadvertantly? Now you have a data anomoly.

> Which
> > > > > record is correct? The database doesn't know.
> > > > >
> > > > > The idea is that you store this data ONLY ONCE. Then you relate

> this
> > > record
> > > > > to records in another table that only stores what differs. This

> process
> > > is
> > > > > called Normalization and is very important to understand before

you
> go
> > > much
> > > > > further. I suggest a couple of books: "Database Design for Mere
> > > Mortals" by
> > > > > Michael Hernandez and "Access Database Design and Programming" by

> Steve
> > > > > Roman.
> > > > >
> > > > > Also, on my website there are some tutorials in database design

> based on
> > > the
> > > > > Hernandez process. You can find them here:
> > > > > http://www.rogersaccesslibrary.com/TutorialsDesign.html
> > > > >
> > > > > Look, I realize I'm talking database design when all you want to

do
> is
> > > get
> > > > > your application running. But believe me, you will save more time

> and
> > > have
> > > > > fewer headaches if you design your database properly. If you

don't
> you
> > > will
> > > > > always have to create complicated workarounds (like what you're

> asking
> > > for)
> > > > > and you will NEVER have assurance that your data is accurate.
> > > > >
> > > > > I can tell you this from experience. The first databases I

created
> > > (long
> > > > > ago) used a process much like yours and it gave me huge problems.
> > > > >
> > > > > --
> > > > > --Roger Carlson
> > > > > Access Database Samples: www.rogersaccesslibrary.com
> > > > > Want answers to your Access questions in your Email?
> > > > > Free subscription:
> > > > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > > > >
> > > > >
> > > > >
> > > > > "emily" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > Once the record is copied, I make a couple of changes. Therefore
> > > having
> > > > > the
> > > > > > copy feature cuts down on data entry tremendously.
> > > > > >
> > > > > > "Roger Carlson" wrote:
> > > > > >
> > > > > > > I'm confused as to why you would want to do that.
> > > > > > >
> > > > > > > In a properly designed database, you wouldn't duplicate a

> record,
> > > you
> > > > > would
> > > > > > > store it once and relate it to records in a separate table.

The
> > > whole
> > > > > > > purpose for data normalization is to reduce redundant

> (duplicate)
> > > data
> > > > > as
> > > > > > > much as possible. Can you explain more fully?
> > > > > > >
> > > > > > > --
> > > > > > > --Roger Carlson
> > > > > > > Access Database Samples: www.rogersaccesslibrary.com
> > > > > > > Want answers to your Access questions in your Email?
> > > > > > > Free subscription:
> > > > > > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > > > > > >
> > > > > > >
> > > > > > > "emily" <(E-Mail Removed)> wrote in message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > Hello,
> > > > > > > >
> > > > > > > > I have a form with a subform and I would like to create a

> button
> > > that
> > > > > > > copies
> > > > > > > > the entire contents of the record (including subform) into a

> new
> > > > > record. I
> > > > > > > > have fantistic code for copying the main form, but how could

I
> > > tweak
> > > > > this
> > > > > > > > code to include my subform (Partner_WI_Subform)? Thanks so

> much
> > > for
> > > > > your
> > > > > > > time
> > > > > > > > and dedication to this discussion group!
> > > > > > > >
> > > > > > > > Private Sub CopyRecord_Click()
> > > > > > > > Set rs = RecordsetClone
> > > > > > > > If Me.Dirty Then Me.Dirty = False 'Save first.
> > > > > > > > If Me.NewRecord Then
> > > > > > > > MsgBox "Pick a record to duplicate."
> > > > > > > > Else
> > > > > > > > 'Select the current record in the clone set.
> > > > > > > > Set rs = Me.RecordsetClone
> > > > > > > > rs.Bookmark = Me.Bookmark
> > > > > > > > 'Move the form to a new record.
> > > > > > > > RunCommand acCmdRecordsGoToNew
> > > > > > > > 'Copy the old fields into the form.
> > > > > > > > Me.Company = rs!Company
> > > > > > > > Me.State = rs!State
> > > > > > > > Me.[Status I] = rs![Status I]
> > > > > > > > 'etc for your other fields.
> > > > > > > > End If
> > > > > > > > Set rs = Nothing
> > > > > > > > End Sub
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding a new record at subform should update an existing record (not create new record) Mark Kubicki Microsoft Access Form Coding 1 16th Jan 2009 09:34 AM
Double click record in subform to go to new record in Main (and subform) Jpipher@gmail.com Microsoft Access 14 3rd Feb 2007 08:06 AM
Wierd subform behaviour, subform not cycling through after updating a subform record Mikal Microsoft Access Forms 2 25th May 2006 07:15 PM
code to delete a record in one subform when a record in a related subform is deleted Helen Microsoft Access Form Coding 3 3rd Apr 2005 10:01 PM
Urgent !!! - Values from Subform - #Error if no records in Subform and Only grabs first subform record Greg Microsoft Access Forms 0 17th Feb 2005 02:12 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:30 PM.