Sean Howard wrote:
Dear All,
As is my want I need to do something in Access that seems simple but
cannot fathom out.
I have main form with two subforms, both datasheets with an almost
identical table structure.
I want to add the functionality to copy records from subformA to
subformB WITHOUT USING COPY/PASTE. The reasonfor this is that I must
make certain changes to the data from subformA before appending it into
subformB
1) Ctrl-C + Ctrl+V. Cannot work because additional changes are needed
2) Adding an extra field to the source table in subformA to indicate
those I need copying. Would work but I DO NOT want to add any more
"unnecessary" fields to my database.
3) Using a "COPYRECORDS" button on main form. Does not work even though
though I can select the required records, as soon as I press another
button those records become unselected
3) Using right-hand-mouse button. Probably would work but I have no idea
how to identify which records have been selected via the record
selectors
Do anyone have any ideas?
Sean
Look at SelHeight and SelTop to determine rows selected if using record
selectors.
You might be better off writing a routine to copy the records. The
routine I use gets field names from the rows selected. This could be
used to capture the keys in Table1. Then write an append query to copy
the fields in Table1 to Table2. Or use a recordset to write to Table2.
Or write a routine to go to a new record in Table2's form and write
the data.
Here's an example, without the adding of records.
Option Compare Database
Option Explicit
Dim intTopRow As Integer
Dim intRowCnt As Integer
Private Sub CommandSelected_Click()
'I created a button to display the values contained in the
'field Item from the records selected. To test this out, change
'the name of ListItems to the name of your form. Change Me.Item
'to the name of a field you want to display.
'The problem, if you select records and then click on a command button
'the selection is removed. Thus I use the MouseDown event to capture
'the top row and number of rows. This would not occur if you use a menu
item to do the copy and then you would not need the mousedown event.
Dim intFor As Integer
Dim strMsg As String
DoCmd.GoToRecord acDataForm, "ListItems", acGoTo, intTopRow
For intFor = 1 To intRowCnt
strMsg = strMsg & Me.Item & Space(5)
DoCmd.GoToRecord acDataForm, "ListItems", acNext, 1
Next
MsgBox strMsg
End Sub
Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
'I used the mouse down event to capture the top/botton row of the
'selected records. These values will be used
intTopRow = Me.SelTop
intRowCnt = Me.SelHeight
End Sub