How to copy multiple fields from one record to an existing record

[tonialbrown]

I was hoping that someone may give me some help in regards to writing some code.

I have the following code that works - basically a user selects a record from a list box & it copies the fields name & title into the new record with the key(id) already assigned.

What I want to be able to do is the same idea but copy all fields from the selected user record into the new record (except for the id). Obviously the other fields are not available in the list box. So I don't have to do each field separately is there a way using the recordset to do this. I am a novice at writing code using the record set so any help would be appreciated.

Code:

Private Sub cmdCopy_Click()
On Error GoTo Err_cmdCopy_Click
Dim wrk As Workspace
Dim dbs As Database
Dim rst As Recordset
Dim sql As String
Dim stDocName As String
Dim StLinkCriteria As String

Set wrk = DBEngine.Workspaces(0)

Set newId = [Forms]![frmdelnewproperties]![id]


Set dbs = CurrentDb()

'The following 4 lines copies the name & title from the list box into the current record (works!!!)

sql = "update delegation " & _
"Set [delegation].[name] = '" & lstDelFrom.Column(1) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Column(2) & "'" & _
"Where [delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "'"



MsgBox "Copying details from Delegation - " & lstDelFrom.Column(0) & " " & lstDelFrom.Column(1) & "-" & lstDelFrom.Column(2)

wrk.BeginTrans
dbs.Execute sql
wrk.CommitTrans

lstDelFrom.Value = 0
'close out of the form
DoCmd.Close


Exit_cmdCopy_Click:

Exit Sub

Err_cmdCopy_Click:
wrk.Rollback
MsgBox Err.Description
Resume Exit_cmdCopy_Click

End Sub

[zaidlig]

First I don't know how many fields each record has. You can have many fields in a list box but make their column widths 0 so they don't show. That way you select the record based on the two columns that show but you can read the 14 that are hidden using the ".column(#)" property of the list box.

However the easy way to do it would be set your list box so that the bound column is the ID and set its column width to 0. Now with the ID you can create a simple append query: appending all the fields from table A to table B where the ID you selected is equal the ID in table A.

This query can easily be created as a query and then steal the SQL text and use it in you code to add the variable ID you selected. Also Docmd.RunSQL would be easier than the recordset method in this instance.

[tonialbrown]

First I don't know how many fields each record has. You can have many fields in a list box but make their column widths 0 so they don't show. That way you select the record based on the two columns that show but you can read the 14 that are hidden using the ".column(#)" property of the list box.

However the easy way to do it would be set your list box so that the bound column is the ID and set its column width to 0. Now with the ID you can create a simple append query: appending all the fields from table A to table B where the ID you selected is equal the ID in table A.

This query can easily be created as a query and then steal the SQL text and use it in you code to add the variable ID you selected. Also Docmd.RunSQL would be easier than the recordset method in this instance.

Many thanks for your reply.

The number of fields each record has is 15. But the way I was doing it I only had 3 of the fields making up the list box - ie) the id, title & name where the user picked the record to copy from.

When you say steal the SQL text - do you mean from the update in my code above???

Do I then list each of the fields that I am adding from the text box ie) similar to the following:

sql = "update delegation " & _
"Set [delegation].[name] = '" & lstDelFrom.Column(1) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Column(2) & "'" & _
"Where [delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "'"

but continue on with the other 12 fields after the [delegation][title] = lstDelFrom(2) ????

Thanks

Tonia

[zaidlig]

Many thanks for your reply.

The number of fields each record has is 15. But the way I was doing it I only had 3 of the fields making up the list box - ie) the id, title & name where the user picked the record to copy from.

When you say steal the SQL text - do you mean from the update in my code above???

Do I then list each of the fields that I am adding from the text box ie) similar to the following:

sql = "update delegation " & _
"Set [delegation].[name] = '" & lstDelFrom.Column(1) & "'," & _
"[delegation].[title] = '" & lstDelFrom.Column(2) & "'" & _
"Where [delegation].[id] = '" & [Forms]![frmdelnewproperties]![id] & "'"

but continue on with the other 12 fields after the [delegation][title] = lstDelFrom(2) ????

Thanks

Tonia

No I was suggesting create a simple append query that appends all the fields you want based on a example ID. Once that works copy the SQL to your code and replace the "Where [delegation].[id] = '5'" with Where [delegation].[id] ='' & me![ID] & "'". Now when you run the SQL it will append all the fields based on the current value of the ID.

[tonialbrown]

No I was suggesting create a simple append query that appends all the fields you want based on a example ID. Once that works copy the SQL to your code and replace the "Where [delegation].[id] = '5'" with Where [delegation].[id] ='' & me![ID] & "'". Now when you run the SQL it will append all the fields based on the current value of the ID.

Sorry not quite clear on what you mean. Do I list each field separately in the append query as indicated in the update query or do I do something else.

Thanks.

[tonialbrown]

No I was suggesting create a simple append query that appends all the fields you want based on a example ID. Once that works copy the SQL to your code and replace the "Where [delegation].[id] = '5'" with Where [delegation].[id] ='' & me![ID] & "'". Now when you run the SQL it will append all the fields based on the current value of the ID.

Sorry not quite clear on what you mean. Do I list each field separately in the append query as similar to how I am doing it the update query or do I do something else. If I am allowing the user to choose the ID/Name/Title from the list box do I copy these in from the list box??

Thanks.

[sierra7]

Sorry not quite clear on what you mean. Do I list each field separately in the append query as similar to how I am doing it the update query or do I do something else. If I am allowing the user to choose the ID/Name/Title from the list box do I copy these in from the list box??

Thanks.

Hi Tonia
I have been looking over your previous posts and you seem to have been stuck for a month on this problem. You must be really fed-up!!!

I think that you were on the right track with this thread but left 'dangling'

You have been right all along, when you have one or two fields to copy then it is quite simple to use the combo-box /List-box in the way you started.

However, if you have 15 fields it gets a bit tedious writing the code, plus as you have found, if any of you 'donor' field are blank or Null, then complications arise when writing the SQL to accommodate these situations.

You were given the right advice (in my opinion) a few posts ago with Zaidlig. You need an Append query.

The way you create this is as follows. We will assume you source table name is tblDonorTable and you want to append the fields to tblTargetTable.

1. Open a new query - choose Design View
2. Add tblDonorTable, then double-click on all of the fields that you want to copy, so they appear in the grid.
3. Now in the Main Menu at the top click Query and choose Append Query to change this query from a SELECT to an APPEND
4. You will then be prompted for the table name to append to - In this example I have used tblTargetTable but yours may be Delegation, I think.
5. If the field names are the same in both tables then the wizard will automatically complete the destinations, otherwise set them manually in the Append to line. The cells become combo coxes when you enter them
6. You only want one record to be copied from tblDonorTable, so arbitarily enter '5' in the Criteria line under ID.
7. Now click on View in the top menu and select SQL View. Copy and past this code to you SQL string in the After_Update procedure for your listbox.

It should be something like;-

strSQL = "INSERT INTO tblTargetTable ( id, seg, other, name, title, [section], keywords ) " & _
"SELECT tblDonorTable.id, tblDonorTable.seg, tblDonorTable.other, tblDonorTable.name, tblDonorTable.title, tblDonorTable.section, tblDonorTable.keywords " & _
"FROM tblDonorTable " & _
" WHERE (((tblDonorTable.id)=5)); "
 

NB that [Section] is in square brackets becaue I have used a reserved word for a field name!

You need to customize it so that instead of appending record ID=5, it picks the ID from your list box

 
"WHERE (((tblDonorTable.id)=" & lstDelFrom.Column(1) & "));"
 

That's about it. I believe that this method does not get involved with Nulls and the like because it is all handled within the SQL and is not trying to interprete what is written in text- or list-boxes.

I'll go and comment on your other string now - about Null Dates, in-case you are too far down that road to turn back !

S7