Copy one record from one table to another in a form 2

[Cero21]

I would like to know if I can copy one record (the currently displayed one) from one table (the table the form is based off of) to another table using a form button. I've been searching but all I've found is how to move all the records at once. Anyone know how to do this? Thanks.

 

[Remou]

There are a few ways to do this, perhaps an Append query would suit:
[tt]strSQL="INSERT INTO tblTable " _
& "( [FieldName1], [FieldName2] ) " _
& "SELECT '" & Me.txtTextField1 _
& "', " & Me.txtNumberField2
DoCmd.RunSQL[/tt]

I hope I typed that right

 

[Cero21]

Thanks for your quick response Remou.

Just a question on that. Would I place that code in the "event action" for a form button or create the append query and link it to a "run query" form button?

 

[Remou]

Depend on what you are doing. If you wish to give the user a choice, a button is good; if the append is to happen whenever something else happens, an event would seem more suitable.

 

[Cero21]

Oh ok. Makes sense. I'm going to go the button route.

I typed this into a click event for a button

Private Sub Command68_Click()
strSQL = "INSERT INTO Deployed Personnel Archive " _
& "( [CFTPO], [Service Number], [Unit], [Name], [Initals], [Rank], [Gender], [Operation], [Start Date], [End Date], [Projected End Date], [Comments] ) " _
& "SELECT '" & Me.CFTPO _
& "', " & Me.Service_Number _
& "', " & Me.Unit_Number _
& "', " & Me.Name _
& "', " & Me.Initals _
& "', " & Me.Rank _
& "', " & Me.Gender _
& "', " & Me.Operation _
& "', " & Me.Start_Date _
& "', " & Me.End_Date _
& "', " & Me.Projected_End_Date _
& "', " & Me.Comments
DoCmd.RunSQL
End Sub

I get a "Method or Data Member not found" error.

I reduced it down to see what I am doing wrong.

Private Sub Command68_Click()
strSQL = "INSERT INTO Deployed Personnel Archive " _
& "( [CFTPO], [Service Number]) " _
& "SELECT '" & Me.CFTPO _
& "', " & Me.Service_Number
DoCmd.RunSQL
End Sub

and I get an "Compiler Error" arugment not optional with "RunSQL" highlighted

What is wrong with my code?

 

[Remou]

Oops . DoCmd.RunSQL strSQL
Also, you need to check your delimiters. It is useful to build the query in Query design, or debug.print strSQL. Each text field must be surrounded by single quotes (') and each date field by hash (#) signs. Number fields do not need anything:
... & Me.Operation _
& " , #" & Me.Start_Date _
& "#, #" & Me.End_Date _
& "#, #" & Me.Projected_End_Date _
& "#, '" & Me.Comments & "'"

 

[PHV]

A starting point:[tt]
strSQL = "INSERT INTO Deployed Personnel Archive " _
& "([CFTPO], [Service Number], [Unit], [Name], [Initals], [Rank], [Gender], [Operation], [Start Date], [End Date], [Projected End Date], [Comments]) " _
& "VALUES ('" & Me.CFTPO & "'" _
& ", " & Me.Service_Number _
& ", " & Me.Unit_Number _
& ",'" & Replace(Me.Name, "'", "''") & "'" _
& ",'" & Me.Initals & "'" _
& ", " & Me.Rank _
& ",'" & Me.Gender & "'" _
& ",'" & Me.Operation & "'" _
& ",#" & Format(Me.Start_Date, "yyyy-mm-dd") & "#" _
& ",#" & Format(Me.End_Date, "yyyy-mm-dd") & "#" _
& ",#" & Format(Me.ProjectedEnd_Date, "yyyy-mm-dd") & "#" _
& ",'" & Replace(Me.Comments, "'", "''") & "'" _
& ")"
DoCmd.RunSQL strSQL[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cero21]

Thanks a lot guys that worked out great. I would have never figured that out myself!