Copy row of fields in form view

[Autoeng]

I have a form displayed in Form view that really represents a data table. I am using form view because we found that the order of items would change even though we put in several stop gap measures to prevent that (none worked). By using form view I can guarentee the order of entries stays the same. When I need to delete an entire line of entries from the form I used the command button wizard and selected Record Operations - Delete Record and placed that button beside each row on the form.

Pretty simple and works well. Now I would like to make two more buttons (one to copy the row and one to paste into a row). Using the command button wizard is not working when selecting Record Operations - Duplicate Record as it makes a copy and automatically inserts it to the next empty row. I would like to copy the information in a row, move to where I want it pasted and then paste it in. What is the On Click procedures for copy and paste command buttons?

Autoeng

 

[Dave Eyley]

It's an interesting problem AutoEng,

It sounds like you need a function like the Tab Order in the View menu.

Is there a logical order that you want the 20 records put into?

Can it be sorted using code in some way?

I can see a way to copy/cut the record into a temp location, but as yet I can't work out the paste bit. It's getting the insertion to work.

I think code is the best option. What about -

First click on a record copies the record to a temp location, then
the second click on a record starts the code which opens a recordset from the table concerned, finds the position of the second-clicked record and inserts the temp-located record in there. Then it requeries the form/datasheet.

Sounds good, but I'm not doing the code for that tonight!

HTH

Dave E

 

[Rich]

You don't need a command button on each row, 1 in the form footer will do

 

[Autoeng]

Thank you gentlemen for your input concerning this problem.

A little more information will shed some more light on the situation. The data entry form is actually a subform. I am using the relational database in a way that it was not meant to be used (as a bills of material but not quite as I don't make the system perform calcs but rather make the user enter all structures). Basically what I need is for the user to enter the BOM as intended for view (Part A is made of Parts D & E, Part B is made of Parts C & F) and the parts remain in this order no matter what the system wants to do. If I need to delete a part I use the delete button at the end of each row (so that the user won't delete the wrong row by mistake which Rich's suggestion could lead to) which leaves the table with a blank row. Users have been instructed to never leave a blank row in the table so by using an up arrow button with the following code they close the table row gap.

Private Sub cmdUp_Click()

Dim rsParts As Recordset, varReturn As Variant, strCurrentPart
If Me.Seq = 1 Then
varReturn = MsgBox("Can't move top item any further up." & vbCrLf & "Please click in the row you want to move up.", vbOKOnly, "Move")
Else
strSQL = "Select [Seq] from [ECNPartstbl] Where [ECNBCNVIP ID]= " & Forms![ECNBCNVIPfrm].[ECNBCNVIP ID]
Set rsParts = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
rsParts.MoveFirst
rsParts.FindFirst ("Seq = " & Me.Seq - 1)
rsParts.Edit
rsParts("Seq") = Me.Seq
rsParts.Update
rsParts.Close

Me.Seq = Me.Seq - 1
strCurrentPart = Me.ECNPartsID
Me.Requery
Me.ECNPartsID.Visible = True
DoCmd.GoToControl "ECNPartsID"
DoCmd.FindRecord strCurrentPart
DoCmd.GoToControl "Part #"
Me.ECNPartsID.Visible = False

End If
End Sub

I have a field in the table called Seq that I use to control the sorting (actually the non-sorting as I want to control the position of records) that I have the following BeforeInsert code attached to.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim varLookup As Variant
varLookup = DMax("Seq", "ECNPartstbl", "[ECNBCNVIP ID]= " & Forms![ECNBCNVIPfrm].[ECNBCNVIP ID])
If Not IsNull(varLookup) Then
Me.Seq = CByte(varLookup) + 1
Else
Me.Seq = 1
End If

End Sub

If a user needs to insert data they go to the bottom of the table and selects the last empty and uses the up arrow button to move it to the location needed then fills it with information. I want to add more user friendliness because a lot of our parts have information that is the same as others (same description, supplier, quantity per, ect..) with the addition of a copy button and a paste button. My attempts with the command button wizard didn't give me the results that I want per the first post. I might even need an on close event to reorder the Seq of the subform records but I'm a newbie (4 months coding experience) and am not really for sure.

Again, thanks for assisting and any more recommendations based on the detailed information will be greatly appreciated.

Autoeng