I have a form that has 3 fields. 1 is the date (default = Date()), the second 
is a value which is populated by a query into the form field, and the 3rd is 
a number (1, 2 or 3 etc)

I would like to copy all those 3 records to a single new record in a table 
of 4 columns (first being the "autonumber ID Key")
Preferably by double clicking the "second value" field in the form or by 
button press on the form.

Private Sub Textfield_DblClick(Cancel As Integer)  [or Private Sub 
Button_Click()]
Dim strSQL As String

  strSQL = "INSERT INTO MyTable(DateField, TextField, NumericField) " & _
    "VALUES(" & Format(Me.DateField, "\#yyyy\-mm\-dd\#") & ", " & _
    Chr$(34) & Me.TextField & Chr$(34) & ", " & Me.NumericField & ")"
  CurrentDb.Execute strSQL, dbFailOnError

End Sub

Hopefully it's obvious what's what, so that you can change the names to 
match your actual names.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tony" <Tony@discussions.microsoft.com> wrote in message 
news:74D1857D-1B83-437B-AA67-1EFB26895CE1@microsoft.com...
>I have a form that has 3 fields. 1 is the date (default = Date()), the 
>second
> is a value which is populated by a query into the form field, and the 3rd 
> is
> a number (1, 2 or 3 etc)
>
> I would like to copy all those 3 records to a single new record in a table
> of 4 columns (first being the "autonumber ID Key")
> Preferably by double clicking the "second value" field in the form or by
> button press on the form.
>