append query question

[mohobrien]

I'm very slowly making headway with a db that is tracking property titles among other things. The problem I'm having is with a method to "ditto" certain fields as I enter new records.

The main table holds a number of fields with DispositionNumber as the key. A second table holds property holder info and it is presented in a subform. Each property can have many partial holders so the tables are linked by DispositionHolder in a one to many relationship. An autonumber ID is the key in the holders table.
I've tried the .Tag way and it works well but I could only get it to work for one holder. I'm looking for a way to ditto multiple holders. I have a global variable that stores the previous record DispositionNumber (LastDispNum) and can query those holders.
How do I append those records to the holder table and change the DispositionNumber to the current value in the new record?
If I try what's below I'll just have double the holders for the previous record. Is there a way to parse the fields?

INSERT INTO tblHolders ( DispositionNumber, Holder, Percentage, Address, Address1, Address2, PCode )
SELECT tblHolders.DispositionNumber, tblHolders.Holder, tblHolders.Percentage, tblHolders.Address, tblHolders.Address1, tblHolders.Address2, tblHolders.PCode
FROM tblHolders
WHERE (("DispositionNumber"="LastDispNum"));

 

[mohobrien]

almost done - it works

Finally getting results. I had to do three queries to get what I wanted but it works almost perfectly.

Private Sub Holder_DblClick(Cancel As Integer)
Dim SQLMakeTblStr As String
Dim SQLUpdateStr As String
Dim SQLAppendStr As String
SQLMakeTblStr = "SELECT tblHolders.DispositionNumber, tblHolders.Holder, tblHolders.Percentage, tblHolders.Address, " & _
         "tblHolders.Address1, tblHolders.Address2, tblHolders.PCode " & _
         "INTO tbltmpHolders " & _
         "FROM tblHolders " & _
         "WHERE (((tblHolders.DispositionNumber)= """ & LastDispNum & """ ));"
SQLUpdateStr = "UPDATE tbltmpholders SET tbltmpholders.[DispositionNumber] = """ & ThisDispNum & """;"
SQLAppendStr = "INSERT INTO tblHolders " & _
               "SELECT * " & _
               "FROM tbltmpHolders ;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLMakeTblStr
DoCmd.RunSQL SQLUpdateStr
DoCmd.RunSQL SQLAppendStr
DoCmd.SetWarnings True
Me.Requery
'HolderSubformTableView.SetFocus
'Requery
'HolderSubForm.SetFocus
End Sub

The only problem left is to get the table view of the updated holder query to update. The form view is updating fine with the Me.Requery I presume I must change the focus to the table view subform and requery. It bombs. Am I going in the right direction?