3 time lucky - Copy Sub records to new sub form - Kiwi in trouble

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

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 said:
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.
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top