Copy 3 separate records from form into 1 row of table as new entry

[Tony]

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.

 

[Douglas J. Steele]

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.

 

[Tony Kuiper]

I tried this and another version of doing this also.
The process worked but only after I selected the record itself with the mouse.

I ended up after much trial and error with the following. I think the issue
was around my data that I needed to paste was in a listbox and is populated
by a query and subsequent refresh macro.

Private Sub Append_btn_Click()
Dim strSQL As String
strSQL = "INSERT INTO Robbery ([Date], [Child Part], [Units]) VALUES ('" &
Date_Value.Value & "','" & Child_Part_List.Column(0, 0) & "','" &
Qty_Value.Value & "');"
DoCmd.RunSQL strSQL
Form.Refresh
End Sub

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

(no e-mails, please!)

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.

 

[Douglas J. Steele]

That strikes me as somewhat odd.

Child_Part_List.Column(0, 0) will always return the first column of the
first row of the list box. Why, then, bother storing it in the table if it's
always the same?

You really should rename the field Date: Date's a reserved word, and should
never be used for your own purposes. For a comprehensive list of names to
avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html And unless Date is a text
field (and storing a date in a text field is never a good idea), you should
be using Format(Date_Value.Value , "\#yyyy\-mm\-dd\#") instead of putting
the single quotes around the value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried this and another version of doing this also.
The process worked but only after I selected the record itself with the
mouse.

I ended up after much trial and error with the following. I think the
issue
was around my data that I needed to paste was in a listbox and is
populated
by a query and subsequent refresh macro.

Private Sub Append_btn_Click()
Dim strSQL As String
strSQL = "INSERT INTO Robbery ([Date], [Child Part], [Units]) VALUES ('" &
Date_Value.Value & "','" & Child_Part_List.Column(0, 0) & "','" &
Qty_Value.Value & "');"
DoCmd.RunSQL strSQL
Form.Refresh
End Sub

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

(no e-mails, please!)

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.