INSERT INTO problem

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
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
 
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
 
Back
Top