Hi,

The main form/table has an Autonumber ID called "Customer_Record_ID" and is
the main key.
A feild called "CustomerID" is a unique 6 letter identifier for the form.

The subtable is linked to the main form/table via "CustomerID" in a many to
one.

Upon selecting the "CustomerID" via a combo box (Column 0) I am trying to
copy all
records in a subtable relating to that 'CustomerID" and paste them into the
new subform as part of the new record under the new "CustomerID".

The new records are a duplicate of the old reference. Say the old refernce
had 9 records - I need those 9 reords to copy to the new subform and to be
linked to it by the new "CustomerID".

I have the following,
'now to copy the subform CustomerContacts
'CustomerID is a unique identifier which must be copied to link the tables
'possibly an sql insert into statement


Dim strSql As String 'SQL Satement




strSql = "INSERT INTO [tblCustomerContacts](
CustomerID,LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI," _
&
"WorkExtension,MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure," _
&
"Calendar_Sent,Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
&
"ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus," _
&
"SpousesName,SpousesInterests,ChildrensNames ) " & _
"SELECT " & Me![CustomerID] & "As NewCustomerID,
LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,WorkExtension,"
_
&
"MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,Calendar_Sent," _
&
"Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
&
"ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus," _
&
"SpousesName,SpousesInterests,ChildrensNames" & _
"FROM [tblCustomerContacts] WHERE [CustomerID] =
Me!Cust_Alias_ID.Column(0)"

DBEngine(0)(0).Execute strSql, dbFailOnError

Me!frmCustomerContacts.Requery

Alas and alac I now get
Error 3075 - Syntax error missing operator.

Kiwi not flying anymore.

Any help appreciated.

Hi Bill (fellow Kiwi!)

[You should really have posted this back to the same thread as a reply to my
previous answer. That way people viewing your post can see the history of
what has happened so far.]

The problem is that CustomerID is a a text field and not a number. Because
of this, it needs to be enclosed in quotes in your SQL statement. I assumed
yesterday that your Customer_Record_ID/CustomerID fields were numeric.

Also, I encourage you to use my suggestion of a "strOtherFields" variable to
reduce the amount of typing and the liklihood of errors:

strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
& ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
& ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
& ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
& ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
& ",HomePhone,MaritalStatusSpousesName,SpousesInterests" _
& ",ChildrensNames"

strSQL = "Insert into tblCustomerContacts " _
& "(CustomerID" & strOtherFields & ") " _
& "SELECT '" & Me![CustomerID] & "' As NewCustomerID _
& strOtherFields & " FROM tblCustomerContacts " _
& "WHERE CustomerID='" & Me!Cust_Alias_ID.Column(0) & "';"

DBEngine(0)(0).Execute strSql, dbFailOnError

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Billp" <(E-Mail Removed)> wrote in message
news:BAF61C70-74FB-4620-A6D2-(E-Mail Removed)...
> Hi,
>
> The main form/table has an Autonumber ID called "Customer_Record_ID" and
> is
> the main key.
> A feild called "CustomerID" is a unique 6 letter identifier for the form.
>
> The subtable is linked to the main form/table via "CustomerID" in a many
> to
> one.
>
> Upon selecting the "CustomerID" via a combo box (Column 0) I am trying to
> copy all
> records in a subtable relating to that 'CustomerID" and paste them into
> the
> new subform as part of the new record under the new "CustomerID".
>
> The new records are a duplicate of the old reference. Say the old refernce
> had 9 records - I need those 9 reords to copy to the new subform and to be
> linked to it by the new "CustomerID".
>
> I have the following,
> 'now to copy the subform CustomerContacts
> 'CustomerID is a unique identifier which must be copied to link the
> tables
> 'possibly an sql insert into statement
>
>
> Dim strSql As String 'SQL Satement
>
>
>
>
> strSql = "INSERT INTO [tblCustomerContacts](
> CustomerID,LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,"
> _
> &
> "WorkExtension,MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure," _
> &
> "Calendar_Sent,Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up,"
> _
> &
> "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> _
> &
> "SpousesName,SpousesInterests,ChildrensNames ) " & _
> "SELECT " & Me![CustomerID] & "As NewCustomerID,
> LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,WorkExtension,"
> _
> &
> "MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,Calendar_Sent," _
> &
> "Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
> &
> "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> _
> &
> "SpousesName,SpousesInterests,ChildrensNames" & _
> "FROM [tblCustomerContacts] WHERE [CustomerID] =
> Me!Cust_Alias_ID.Column(0)"
>
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> Me!frmCustomerContacts.Requery
>
> Alas and alac I now get
> Error 3075 - Syntax error missing operator.
>
> Kiwi not flying anymore.
>
> Any help appreciated.

Hi Graham - Greetings in real time (K 1 W 1)

The microssoft server is on a go slow (much like the AB's) as I couldn't
repost to the 2nd attempt - you had kindly responded but the post wasn't
showing, hence the 3rd attempt.
I have updated the code:
Dim strSql As String 'SQL Satement
Dim strOtherFields As String

strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
& ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
& ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
& ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
& ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
& ",HomePhone,MaritalStatusSpousesName,SpousesInterests" _
& ",ChildrensNames"


strSql = "Insert into tblCustomerContacts " _
& "(CustomerID" & strOtherFields & ") " _
& "SELECT '" & Me![CustomerID] & "' As NewCustomerID" _
& strOtherFields & " FROM tblCustomerContacts " _
& "WHERE CustomerID='" & Me!Cust_Alias_ID.Column(0) & "';"

DBEngine(0)(0).Execute strSql, dbFailOnError

Me!frmCustomerContacts.Requery

getting now an error 3061 - too few parameters expecting 2.

ummmmmmmmmmmmmmmmmm

"Graham Mandeno" wrote:

> Hi Bill (fellow Kiwi!)
>
> [You should really have posted this back to the same thread as a reply to my
> previous answer. That way people viewing your post can see the history of
> what has happened so far.]
>
> The problem is that CustomerID is a a text field and not a number. Because
> of this, it needs to be enclosed in quotes in your SQL statement. I assumed
> yesterday that your Customer_Record_ID/CustomerID fields were numeric.
>
> Also, I encourage you to use my suggestion of a "strOtherFields" variable to
> reduce the amount of typing and the liklihood of errors:
>
> strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
> & ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
> & ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
> & ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
> & ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
> & ",HomePhone,MaritalStatusSpousesName,SpousesInterests" _
> & ",ChildrensNames"
>
> strSQL = "Insert into tblCustomerContacts " _
> & "(CustomerID" & strOtherFields & ") " _
> & "SELECT '" & Me![CustomerID] & "' As NewCustomerID _
> & strOtherFields & " FROM tblCustomerContacts " _
> & "WHERE CustomerID='" & Me!Cust_Alias_ID.Column(0) & "';"
>
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Billp" <(E-Mail Removed)> wrote in message
> news:BAF61C70-74FB-4620-A6D2-(E-Mail Removed)...
> > Hi,
> >
> > The main form/table has an Autonumber ID called "Customer_Record_ID" and
> > is
> > the main key.
> > A feild called "CustomerID" is a unique 6 letter identifier for the form.
> >
> > The subtable is linked to the main form/table via "CustomerID" in a many
> > to
> > one.
> >
> > Upon selecting the "CustomerID" via a combo box (Column 0) I am trying to
> > copy all
> > records in a subtable relating to that 'CustomerID" and paste them into
> > the
> > new subform as part of the new record under the new "CustomerID".
> >
> > The new records are a duplicate of the old reference. Say the old refernce
> > had 9 records - I need those 9 reords to copy to the new subform and to be
> > linked to it by the new "CustomerID".
> >
> > I have the following,
> > 'now to copy the subform CustomerContacts
> > 'CustomerID is a unique identifier which must be copied to link the
> > tables
> > 'possibly an sql insert into statement
> >
> >
> > Dim strSql As String 'SQL Satement
> >
> >
> >
> >
> > strSql = "INSERT INTO [tblCustomerContacts](
> > CustomerID,LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,"
> > _
> > &
> > "WorkExtension,MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure," _
> > &
> > "Calendar_Sent,Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up,"
> > _
> > &
> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> > _
> > &
> > "SpousesName,SpousesInterests,ChildrensNames ) " & _
> > "SELECT " & Me![CustomerID] & "As NewCustomerID,
> > LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,WorkExtension,"
> > _
> > &
> > "MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,Calendar_Sent," _
> > &
> > "Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
> > &
> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> > _
> > &
> > "SpousesName,SpousesInterests,ChildrensNames" & _
> > "FROM [tblCustomerContacts] WHERE [CustomerID] =
> > Me!Cust_Alias_ID.Column(0)"
> >
> > DBEngine(0)(0).Execute strSql, dbFailOnError
> >
> > Me!frmCustomerContacts.Requery
> >
> > Alas and alac I now get
> > Error 3075 - Syntax error missing operator.
> >
> > Kiwi not flying anymore.
> >
> > Any help appreciated.
>
>
>

Cheers Graham,

It works by golly it works.
My bad with the spelling mistakes and typo's of the field names which caused
the error 3061.
Once corrected she rocks, boy does it rock.
You the man Graham.

Best Regards
Bill

"Graham Mandeno" wrote:

> Hi Bill (fellow Kiwi!)
>
> [You should really have posted this back to the same thread as a reply to my
> previous answer. That way people viewing your post can see the history of
> what has happened so far.]
>
> The problem is that CustomerID is a a text field and not a number. Because
> of this, it needs to be enclosed in quotes in your SQL statement. I assumed
> yesterday that your Customer_Record_ID/CustomerID fields were numeric.
>
> Also, I encourage you to use my suggestion of a "strOtherFields" variable to
> reduce the amount of typing and the liklihood of errors:
>
> strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
> & ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
> & ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
> & ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
> & ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
> & ",HomePhone,MaritalStatusSpousesName,SpousesInterests" _
> & ",ChildrensNames"
>
> strSQL = "Insert into tblCustomerContacts " _
> & "(CustomerID" & strOtherFields & ") " _
> & "SELECT '" & Me![CustomerID] & "' As NewCustomerID _
> & strOtherFields & " FROM tblCustomerContacts " _
> & "WHERE CustomerID='" & Me!Cust_Alias_ID.Column(0) & "';"
>
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Billp" <(E-Mail Removed)> wrote in message
> news:BAF61C70-74FB-4620-A6D2-(E-Mail Removed)...
> > Hi,
> >
> > The main form/table has an Autonumber ID called "Customer_Record_ID" and
> > is
> > the main key.
> > A feild called "CustomerID" is a unique 6 letter identifier for the form.
> >
> > The subtable is linked to the main form/table via "CustomerID" in a many
> > to
> > one.
> >
> > Upon selecting the "CustomerID" via a combo box (Column 0) I am trying to
> > copy all
> > records in a subtable relating to that 'CustomerID" and paste them into
> > the
> > new subform as part of the new record under the new "CustomerID".
> >
> > The new records are a duplicate of the old reference. Say the old refernce
> > had 9 records - I need those 9 reords to copy to the new subform and to be
> > linked to it by the new "CustomerID".
> >
> > I have the following,
> > 'now to copy the subform CustomerContacts
> > 'CustomerID is a unique identifier which must be copied to link the
> > tables
> > 'possibly an sql insert into statement
> >
> >
> > Dim strSql As String 'SQL Satement
> >
> >
> >
> >
> > strSql = "INSERT INTO [tblCustomerContacts](
> > CustomerID,LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,"
> > _
> > &
> > "WorkExtension,MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure," _
> > &
> > "Calendar_Sent,Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up,"
> > _
> > &
> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> > _
> > &
> > "SpousesName,SpousesInterests,ChildrensNames ) " & _
> > "SELECT " & Me![CustomerID] & "As NewCustomerID,
> > LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,WorkExtension,"
> > _
> > &
> > "MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,Calendar_Sent," _
> > &
> > "Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
> > &
> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
> > _
> > &
> > "SpousesName,SpousesInterests,ChildrensNames" & _
> > "FROM [tblCustomerContacts] WHERE [CustomerID] =
> > Me!Cust_Alias_ID.Column(0)"
> >
> > DBEngine(0)(0).Execute strSql, dbFailOnError
> >
> > Me!frmCustomerContacts.Requery
> >
> > Alas and alac I now get
> > Error 3075 - Syntax error missing operator.
> >
> > Kiwi not flying anymore.
> >
> > Any help appreciated.
>
>
>

Hi Bill

Glad you got it working!

Error 3061 is usually cause by either (a) a typo in a field name or (b) a
value not enclosed in quoted when it should be.
--
Cheers :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Billp" <(E-Mail Removed)> wrote in message
news:C4D17DF9-4CF5-46C0-BF17-(E-Mail Removed)...
> Cheers Graham,
>
> It works by golly it works.
> My bad with the spelling mistakes and typo's of the field names which
> caused
> the error 3061.
> Once corrected she rocks, boy does it rock.
> You the man Graham.
>
> Best Regards
> Bill
>
> "Graham Mandeno" wrote:
>
>> Hi Bill (fellow Kiwi!)
>>
>> [You should really have posted this back to the same thread as a reply to
>> my
>> previous answer. That way people viewing your post can see the history
>> of
>> what has happened so far.]
>>
>> The problem is that CustomerID is a a text field and not a number.
>> Because
>> of this, it needs to be enclosed in quotes in your SQL statement. I
>> assumed
>> yesterday that your Customer_Record_ID/CustomerID fields were numeric.
>>
>> Also, I encourage you to use my suggestion of a "strOtherFields" variable
>> to
>> reduce the amount of typing and the liklihood of errors:
>>
>> strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
>> & ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
>> & ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
>> & ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
>> & ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
>> & ",HomePhone,MaritalStatusSpousesName,SpousesInterests" _
>> & ",ChildrensNames"
>>
>> strSQL = "Insert into tblCustomerContacts " _
>> & "(CustomerID" & strOtherFields & ") " _
>> & "SELECT '" & Me![CustomerID] & "' As NewCustomerID _
>> & strOtherFields & " FROM tblCustomerContacts " _
>> & "WHERE CustomerID='" & Me!Cust_Alias_ID.Column(0) & "';"
>>
>> DBEngine(0)(0).Execute strSql, dbFailOnError
>>
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "Billp" <(E-Mail Removed)> wrote in message
>> news:BAF61C70-74FB-4620-A6D2-(E-Mail Removed)...
>> > Hi,
>> >
>> > The main form/table has an Autonumber ID called "Customer_Record_ID"
>> > and
>> > is
>> > the main key.
>> > A feild called "CustomerID" is a unique 6 letter identifier for the
>> > form.
>> >
>> > The subtable is linked to the main form/table via "CustomerID" in a
>> > many
>> > to
>> > one.
>> >
>> > Upon selecting the "CustomerID" via a combo box (Column 0) I am trying
>> > to
>> > copy all
>> > records in a subtable relating to that 'CustomerID" and paste them into
>> > the
>> > new subform as part of the new record under the new "CustomerID".
>> >
>> > The new records are a duplicate of the old reference. Say the old
>> > refernce
>> > had 9 records - I need those 9 reords to copy to the new subform and to
>> > be
>> > linked to it by the new "CustomerID".
>> >
>> > I have the following,
>> > 'now to copy the subform CustomerContacts
>> > 'CustomerID is a unique identifier which must be copied to link the
>> > tables
>> > 'possibly an sql insert into statement
>> >
>> >
>> > Dim strSql As String 'SQL Satement
>> >
>> >
>> >
>> >
>> > strSql = "INSERT INTO [tblCustomerContacts](
>> > CustomerID,LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,"
>> > _
>> > &
>> > "WorkExtension,MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,"
>> > _
>> > &
>> > "Calendar_Sent,Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up,"
>> > _
>> > &
>> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
>> > _
>> > &
>> > "SpousesName,SpousesInterests,ChildrensNames ) " & _
>> > "SELECT " & Me![CustomerID] & "As NewCustomerID,
>> > LastName,FirstName,ContactName,Title,Region,WorkPhone,WorkDDI,WorkExtension,"
>> > _
>> > &
>> > "MobilePhone,FaxNumber,EmailName,Has_Left,Corp_Brochure,Calendar_Sent,"
>> > _
>> > &
>> > "Diary,Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up," _
>> > &
>> > "ContactNotes,ContactsInterests,Birthdate,Hometown,HomePhone,MaritalStatus,"
>> > _
>> > &
>> > "SpousesName,SpousesInterests,ChildrensNames" & _
>> > "FROM [tblCustomerContacts] WHERE [CustomerID] =
>> > Me!Cust_Alias_ID.Column(0)"
>> >
>> > DBEngine(0)(0).Execute strSql, dbFailOnError
>> >
>> > Me!frmCustomerContacts.Requery
>> >
>> > Alas and alac I now get
>> > Error 3075 - Syntax error missing operator.
>> >
>> > Kiwi not flying anymore.
>> >
>> > Any help appreciated.
>>
>>
>>