Append query or VBA

[Gavx]

I have a table that has the fields CustomerID, ProductID, Qty, SalesPrice.

One CustomerID will have several records listing different ProductID's and prices etc.

I would like to have a button on my form that can copy these details from this table of a particular CustomerID (specified by some code sitting behind the button), and append the same table with these details except with a different CustomerID.

Can this be accomplished with an append query and some code to set the correct CustomerID or is this (preferable/only possible) with code that steps through each record of the relevant CustomerID, copying all the fields except CustomerID and appending the table with these details plus the correct CustomerID using something such as SELECT and INSERT type statements?

Thanks for any help.

 

[Galaxiom]

Can be done with a query but it needs to get the new CustomerID from somewhere such as the form.

Assuming the controls on that form are called OldCustomerID and NewCustomerID and they are numeric and on the same form as the button:

Private Sub YourButton_Click()

Dim strSQL as String

   strSQL = "INSERT INTO tablename (CustomerID, ProductID, SalesPrice)"  _
          & " SELECT " & Me.NewCustomerID & "," _
          & " ProductID," _
          & " SalesPrice," _
          & " FROM tablename" _
          & " WHERE CustomerID = " & Me.OldCustomerID

   CurrentDb.Execute stSQL, dbFailOnError

End Sub

 

[Gavx]

I think my question has been answered

http://www.access-programmers.co.uk/forums/showthread.php?t=199548

 

[Gavx]

Thanks Galaxiom,

I figured I would pass the CustomerID and new CustomerID to it.
Say
CustomerID to copy from = OldCustomerID
CustomerID to copy to = NewCustomerID

and then run your code. Once I figure that out I will run your code.

thanks