Copy and Paste subform records

[Guest]

I have an Access 2000 form with a subform that I want to be able to select
some or all of the records in the subform datasheet. Click a Copy button on
the mainform to copy the selected subform records, then move to a new record
using the mainform and paste the copied records to the new subform datasheet
(The IDNum will need to be cleared so that the Autonumber will work for the
primary key when the data is pasted as new records).
I’m hoping there is an easy way to do this but will probably need to use a
query that selects the “selected†subform records using each record’s IDNum
from an array variable… and so on.
But then how to I identify which records are actually selected on the
subform?
I need some help on this one, I’m not sure where to start.

Thanks in advance,
Al

 

[tina]

well, i would probably cheat and do this the "easy" way, by adding a Yes/No
field to the table underlying the subform, so you'll be able to identify
those records while in the form/subform.

create a Select query to pull the records from the subform's underlying
table, where the Yes/No field = True. then convert the query into an Append
query to append the records back into the same table. don't include the
primary key field in the append, of course. *also*, don't append the foreign
key field from the checkmarked records. instead, set the foreign key field
to the value of the *current* record in the open main form, as

Forms!MainForm!PrimaryKeyFieldName

make a copy of the Append query, and convert it to an Update query. in the
Update query, update the Yes/No field from True to False.

add a command button on the main form to run the following sequence of
actions:

If Me.Dirty Then Me.Dirty = False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendSubformRecords"
DoCmd.OpenQuery "qryUpdateDeleteCheckMarks"
DoCmd.SetWarnings True

now when you open the main form in form view: in the subform, checkmark the
records you want to "copy". back in the main form, move to the "new" record.
note: if this really IS a new record (not another existing record in the
main form), then enter data in at least one field so that a primary key will
be assigned to the record. click the command button to save the record (if
necessary) and run the Append and Update queries.

hth

 

[Guest]

Hi, Al.

But then how to I identify which records are actually selected on the
subform?

Use the datasheet's CurrentRecord and SelHeight Properties to determine
which record is the first record selected in the group and how many records
are selected. Then walk through each of these records and save the primary
key so that you can later copy these records.

I need some help on this one, I’m not sure where to start.

The first thing to do is to copy and paste the following code into your
subform's code module:

' * * * * Start code * * * *

Private m_nHt As Long

Public Property Let DSSelHeight(nHt As Long)
m_nHt = nHt
End Property

Public Property Get DSSelHeight() As Long
DSSelHeight = m_nHt
End Property

Private Sub Form_Click()

On Error GoTo ErrHandler

m_nHt = Me.SelHeight

Exit Sub

ErrHandler:

MsgBox "Error in Form_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End code * * * *

This code will make the subform's SelHeight Property available to external
objects, such as the main form.

Next, you'll need to copy the selected records, but without the original
IDNums, and with the original foreign key replaced with the main form's
primary key for the new record. To do that, set a reference to the DAO
library and try:

' * * * * Start code * * * *

Private Sub CopyBtn_Click()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim sList As String
Dim numRecs As Long
Dim nPKey As Long
Dim fOpenedRecSet As Boolean
Dim idx As Long

numRecs = Me!subFrmCtrl.Form.DSSelHeight
Set recSet = Me!subFrmCtrl.Form.RecordsetClone
fOpenedRecSet = True

'--------------------------------------------------------------------
' Walk through each selected record to retrieve the
' primary key.
'--------------------------------------------------------------------

For idx = 1 To numRecs
sList = sList & Me!subFrmCtrl.Form.txtIDNums.Value & ", "
recSet.Bookmark = Me!subFrmCtrl.Form.Bookmark
recSet.MoveNext

If (Not (recSet.EOF)) Then
Me!subFrmCtrl.Form.Bookmark = recSet.Bookmark
End If
Next idx

sList = Mid$(sList, 1, Len(sList) - 2) ' Remove last comma & space.

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

'--------------------------------------------------------------------
' Must write to any fld to create newest AutoNum.
'--------------------------------------------------------------------

Me!txtOrderDate.Value = Date
nPKey = Me!txtOrderID.Value

'--------------------------------------------------------------------
' Copy selected subform records for new main
' form record.
'--------------------------------------------------------------------

CurrentDb().Execute "INSERT INTO OrderItems " & _
"(ItemDesc, Discount, OrderID) " & _
"SELECT ItemDesc, Discount, " & nPKey & _
" FROM OrderItems " & _
"WHERE (IDNums IN (" & sList & "));", dbFailOnError

Me.Requery ' Display subform's new
recs.
Set recSet = Me.RecordsetClone ' Use RecordsetClone of main form.
recSet.FindFirst "OrderID = " & nPKey ' Find newest rec.

If (Not (recSet.NoMatch)) Then
Me.Bookmark = recSet.Bookmark ' Jump to newest rec.
End If

CleanUp:

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

Exit Sub

ErrHandler:

If (Err.Number = 3021) Then
MsgBox "Please select a record.", vbInformation + vbOKOnly, _
"No Record Selected"
Else
MsgBox "Error in CopyBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp

End Sub ' CopyBtn_Click( )

' * * * * End code * * * *

.. . . where subFrmCtrl is the name of the subform control, IDNums is the
primary key of the table that the subform is bound to, txtIDNums is the name
of the text box that displays this value, txtOrderDate is the name of any
bound control that must be written to in order to create a new
record-generated AutoNumber primary key for this record on the main form,
txtOrderID is the name of the text box that displays this new AutoNumber on
the main form, OrderID is the primary key of the table that the main form is
bound to and the foreign key for OrderItems, the underlying table in the
subform's RecordSource Property.

Please note that because the Recordset is walked through with "MoveNext" and
not "MovePrevious," if the user selects the records from the bottom to the
top, then the first "current record" will be the bottom row selected, not the
top row selected, and the next record after the current record will be the
very first record _after_ the actual group of records selected -- which will
result in an erroroneous list of records selected.

The bottom line is that the user _must_ select from the top to the bottom of
the records, not from the bottom to the top. If you want to accommodate this
quirk, then I leave it to you to modify the algorithm above to determine when
the user has selected from the bottom to the top of the records, and
accommodate this with recSet.MovePrevious.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

Hi, Al.

It appears that you've marked my post above as an answer to your question.
Unfortunately, the Web portal is still rather buggy and it didn't recognize
you as the original poster of the question. Would you please do me a favor
and sign in again to the Microsoft Online Community with your .Net Passport
and try to mark the "Did this post answer your question?" question on my
previous post until a green check mark shows up? (Refresh the page about a
minute later and the green check mark should appear.)

Thanks! It's greatly appreciated.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.