Button to copy text of control in previous record in continuous form

[oxicottin]

Hello, I have a continuous form with a text box named [txtLotNumber] and it consists of entering 12 numbers each time BUT sometimes it is the same number as the last record. So I wanted to make it easier on the user and have a button they could press that would copy down the number from [txtLotNumber] from the last record into [txtLotNumber] of the new record and not make the button visible on the first NewRecord because there isnt anything to copy down. How can I accomplish this?

the ID text box is [txtProductHoldID] and its source is [ProductHoldID]

Update: I did get the button not to show on the first record by putting the VBA below in the subforms current event.

'Set the copy lot number to show if it isnt a new record on open
If Me.NewRecord Then
Me.cmdCopyLotDown.Visible = False
Else
Me.cmdCopyLotDown.Visible = True
End If


Thanks,

[jonh]

Add a button to the form footer to add the new record.


Private Sub Command1_Click()
If Me.RecordsetClone.BOF And Me.RecordsetClone.EOF Then
MsgBox "enter first value"
Exit Sub
End If
Dim v As Variant
DoCmd.GoToRecord , , acLast
v = [txtLotNumber]
DoCmd.GoToRecord , , acNewRec
[txtLotNumber] = v
End Sub

[oxicottin]

@Jonh, thank you for the example... The way I have it set up is without a footer and with a copy button on each record just under each [txtLotNumber]. I tried the example and it sorta worked but by reading your post you say add a new record button in the footer and I would rather not do that. what its doing is if I click the button it copy's down the numeric value but if I click again It copy's it down again and creates a new record and pastes it in that [txtLotNumber]. For example if I had 5 records and I clicked on the first ones "Copy Lot #" button it would copy that number into a new record which would be the 6th record not the next record down. Thoughts?
14637

[jonh]

I gave you the easiest method - add the the last value to a new record.

If you want to copy the value from the last record to a new current record you'll have to use sql.
Google 'sql select top'.

Top being your record id.
edit


Private Sub Command1_Click()
On Error Resume Next
With Me.RecordsetClone
.MoveLast
v = .[machine]
Select Case Err.Number
Case 0: [machine] = v
Case 3021: MsgBox "enter first number"
End Select
End With
End Sub

[oxicottin]

@john, your last post/example confused me? you posted for me to start looking for sql select top which im learning it has something to do with it picks the top however many out of a query you select then I see you edited your post which is is awesome but I don't understand it? You wrote "Top being your record id." not sure what that means my record ID is [txtProductHoldID] and its source is [ProductHoldID]. You have [machine], what should I substitute that for because I have no machine? Is your last post supposed to correspond with the first bit of VBA?

Thank You,

[jonh]

machine should be your field, txtLotNumber ? I thought I had changed that.

Then try the code.

[oxicottin]

john, I tried the example you had given and I'm either still not understanding or I did something incorrect. I made a example of the form im using with the last example in the button, I cant get it to copy down any value?

14639

Thanks!

[jonh]

Actually I think I completely missed post #3 which says something different to post #1.


So you want to create and insert a new record within a set of records, not just add a new record at the end?


In that case you'll need to add a new field to tblsub_ProductHoldData to manage the row order.


Here I've called the new field 'occno' (occurrence number) and it's a long datatype.



You'll need to change the recordsource of frmsub_ProductHoldData so that the recordset is ordered by that new field.



select * from tblsub_ProductHoldData order by occno


This should copy a new record into the next 'slot'.



Private Sub cmdCopyLotDown_Click()
DBEngine(0)(0).Execute "update tblsub_ProductHoldData set occno=occno+1 where holdid=" & _
HoldID & " and occno > " & occno
DBEngine(0)(0).Execute "insert into tblsub_ProductHoldData (holdid,occno,LotNumber,cartonsheld) " & _
"values (" & HoldID & "," & occno + 1 & "," & LotNumber & ",0)"
Me.Requery
End Sub


Now you need to make sure that manually added records are assigned the next available occno.



Private Sub Form_BeforeInsert(Cancel As Integer)
occno = nextoccno(Parent!HoldID)
End Sub


Private Function nextoccno(id As Long) As Long
On Error Resume Next
nextoccno = DBEngine(0)(0).OpenRecordset( _
"select top 1 occno from tblsub_ProductHoldData where holdid=" & id & " order by occno desc")(0)
Select Case Err.Number
Case 0: nextoccno = nextoccno + 1
End Select
End Function


You'll probably find forms a lot easier to work with if you only make them pop up dialogs once you've finished designing and testing them.