INSERT INTO problem

I

Ivan Debono

Hi,

I'm using Access 2003. I have a table 'customers' with many fields. The
primarykey is the ID field which is Autonumber. I want to insert the details
of one customer to a new customer. The criteria is the ID of the original
customer, and the customer_name field will be passed as parameter. Only
other fields will remain the same.

Can I do this using a simple SQL statement.

Furthermore, is it possible to immediately return the ID in the same
statement, or would I have to use a separate SQL statement for it?

Thanks,
Ivan
 
D

Dale Fye

Ivan,

It's possible to write the single SQL to accomplish this, but you will have
to get the ID field separately.

Your SQL will look something like:

Private sub cmd_CopyRecord_Click()

bCopySucceeded = CopyRecord(Me.CustID, Me.NewCustomerName)
lngNewID = DMAX("ID", "yourTableNameGoesHere")

End Sub
******************
Private Function CopyRecord(CustID as Long, NewCustomer as string) as
boolean

Dim strSQL as string
On Error goto CopyRecord_Error

strSQL = "INSERT INTO yourTableNameGoesHere (CustName, field2, field3,
field4, ...) " _
& "SELECT " & chr$(34) & NewCustomer & chr$(34) & ", " _
& "field2, field3, field4 " _
& "FROM yourTableNameGoesHere T " _
& "WHERE T.ID = " & CustID
Currentdb.execute strSQL, dbFailOnError
CopyRecord = True
Exit Function

CopyRecord_Error:
msgbox "Unable to successfully copy the record!" & vbcrlf _
& Err.number & ": " & Err.Description
End Function
 
Top