Dear All,

I want to copy multiple records from one table to another with
specific field. I have written code. It is copying only first record.
I use the loop command also. My code are as follows. Please anybode
solve my problem.

Thanks in advance.

Private Sub CboMemoref_AfterUpdate()
Dim db As Database
Dim rs As Recordset, rs1 As Recordset, rs2 As Recordset
Dim vReqNo As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from tblMechRequestDetails where
val(RequestNo) = '" & Val(Me.RequestNo) & "'")
Set rs2 = db.OpenRecordset("tblQuotationDetailsMech", dbOpenDynaset)

Do While Not rs.EOF
vReqNo = rs.RecordCount
rs.MoveLast
If rs!RequestNo = Me.RequestNo And vReqNo > 0 Then
rs2.AddNew
rs2!QuotationNo = Me.QuotationNo
rs2!RequestNo = rs!RequestNo
rs2!MemoRef = Me.MemoRef
rs2!ItemName = rs!ItemName
rs2!CSINo = rs!CSINo
rs2!Type = rs!Type
rs2!SubType = rs!SubType
rs2!JointFaceType = rs!JointFaceType
rs2!Material = rs!Material
rs2!MaterialCode = rs!MaterialCode
rs2!Coating = rs!Coating
rs2!Lining = rs!Lining
rs2!SizeCapacity1 = rs!SizeCapacity1
rs2!Unit1 = rs!Unit1
rs2!SizeCapacity2 = rs!SizeCapacity2
rs2!Unit2 = rs!Unit2
rs2!Class = rs!Class
rs2!Schedule = rs!Schedule
rs2!Thickness = rs!Thickness
rs2!Unit3 = rs!Unit3
rs2!Quantity = rs!Quantity
rs2.Update
Exit Do
End If
rs.MoveNext
Loop

Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

Naushad wrote:

dim sSQL as String
sSQL="INSERT INTO tblQuotationDetailsMech (" & _
"QuotationNo, RequestNo, ..., Quantity) " & _
"SELECT " & _
Me.QuotationNo & ",RequestNo, ..., Quantity " & _
"FROM tblMechRequestDetails " & _
"where val(RequestNo) = '" & Val(Me.RequestNo) & "'")

db.execute sSQL,dbFailOnError

If QuotationNo is text rather than number, then change the select clause to:
"SELECT '" & _
Me.QuotationNo & "',RequestNo, ..., Quantity " & _

If this still fails to give you the results you need, then add

debug.print sSQL

to the code so you can see the sql statement being generated in the
Immediate window, and you can copy the SELECT portion to a query builder's
SQL View and test it to see what it returns.