Hi,

I have so far got

Dim strSql As String 'SQL Satement



strSql = "INSERT INTO [tblCustomerContacts] " & _
"SELECT * " & _
"FROM [tblCustomerContacts] WHERE [CustomerID] = " &
Me!Cust_Alias_ID.Column(0) & ";"

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

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 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 am not having much luck.
I have looked at Allen Brownes duplicate example - to no avail.


Any help really really appreciated.

Hi Bill

Currently you are saying:

INSERT INTO [tblCustomerContacts]
SELECT * FROM [tblCustomerContacts] WHERE ...

Because you are using SELECT *, you are selecting ALL the fields from the
records you want to append, including CustomerID.

What you need to do is make a list of all the fields in
[tblCustomerContacts] EXCEPT CustomerID, and select those fields along with
the new CustomerID value. You should also omit any autonumber primary key
field, if you have one.

You can assign a list of field names to a variable and use it twice in your
SQL string - for example:

strOtherFields = ", FirstName, LastName, JobTitle, OfficePhone, Mobile"

strSQL = "Insert into tblCustomerContacts (CustomerID" _
& strOtherFields & ") SELECT " & lngNewCustomerID _
& strOtherFields & " from tblCustomerContacts WHERE CustomerID=" _
& Me!Cust_Alias_ID.Column(0) & ";"

lngNewCustomerID is the autonumber PK value of the new record that you have
just added.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Also,
"Billp" <(E-Mail Removed)> wrote in message
news:A4D4B599-EB4F-46D6-AF64-(E-Mail Removed)...
> Hi,
>
> I have so far got
>
> Dim strSql As String 'SQL Satement
>
>
>
> strSql = "INSERT INTO [tblCustomerContacts] " & _
> "SELECT * " & _
> "FROM [tblCustomerContacts] WHERE [CustomerID] = " &
> Me!Cust_Alias_ID.Column(0) & ";"
>
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> 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 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 am not having much luck.
> I have looked at Allen Brownes duplicate example - to no avail.
>
>
> Any help really really appreciated.
>
>