To Copy data from table1 to table2

  • Thread starter Thread starter Matthew Mandalek
  • Start date Start date
M

Matthew Mandalek

I have two tables. both of which hold data for a number of work orders and
estimates.

I need to copy records from table1 (estimates - items) to table2
(workorders - items). There can be any number of records in table1 that
need to be copied to table2.

How do get started on making this work?

Thanks

Matt
 
Hi Matt:

In one of my projects, I placed two listboxes side by side and make a double
click event, each with the following type code-

' first insert the record into the other table
DoCmd.RunSQL "INSERT INTO Patients SELECT PatientsDump.* FROM PatientsDump
WHERE ((([PatientsDump]![ACCT])=Forms![RestoreArchive]!List257.value));"
' then delete the record in the FROM table
DoCmd.RunSQL "DELETE PatientsDump.* FROM PatientsDump WHERE
((([PatientsDump]![ACCT])=Forms![RestoreArchive]!List257.value));"
' no update the 2 listboxes
Me!List260.Requery
Me!List257.Requery

Regards,
Al
 
Ooops... the prior post was done using Recordsetclone (and yes, it does work
well), but I find the following method more precise. You can see what works
best for you (SOAP is a table in one of my databases in use at my office):

Private Sub Command63_Click()
On Error GoTo command63err
Dim dbs As Database
Dim rst As Recordset
Dim ii As Long
ii = Forms![*Medical Information]![EmbMedicalInfo].Form![SOAP].Form![ACCT]
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT SOAP.* FROM SOAP WHERE ([ACCT]= " & ii &
") ORDER BY SOAP.DDate;", dbOpenDynaset)
With rst
.MoveFirst
.MoveLast
If [S:] <> " " Then
MsgBox "Please change the date so that your new record is at" &
Chr(13) & "the end of recordset! (order is by descending dates)" & Chr(13) &
"Also: your HPI field must be blank.", vbExclamation
Exit Sub
End If
.MovePrevious
oo = IIf(IsNull(![S:]), " ", ![S:])
rr = IIf(IsNull(![A5].value), " ", ![A5])
ss = IIf(IsNull(![OtherA]), " ", ![OtherA])
tt = IIf(IsNull(![ROS]), " ", ![ROS])
uu = IIf(IsNull(![O]), " ", ![O])
vv = IIf(IsNull(![A1]), " ", ![A1])
ww = IIf(IsNull(![A2]), " ", ![A2])
XX = IIf(IsNull(![A3]), " ", ![A3])
yy = IIf(IsNull(![A4]), " ", ![A4])
zz = IIf(IsNull(![P]), " ", ![P])
.MoveNext
.Edit
![S:] = oo
![A5] = rr
![OtherA] = ss
![ROS] = tt
![O] = uu
![A1] = vv
![A2] = ww
![A3] = XX
![A4] = yy
![P] = zz
.UPDATE
.Close
End With
Me.Refresh
Exit Sub

command63err:
MsgBox "An error has occurred! Make sure that the" & Chr(13) & "((copy to
record)) is the last record on the list." & Chr(13) & "Then try again.",
vbCritical
Exit Sub
End Sub
 
Back
Top