Copy old subform records to new Form's subform

[Billp]

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.

 

[Graham Mandeno]

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,