Copy Cell Above and Add 1

[AlanW]

I have a subform in Datasheet view. One field contains numbers. When entering data into the datasheet I wish to copy the number from the cell above and add 1. Can anybody help me with the code to do this?

 

[AlanW]

Thanks Pat

What I am trying to do is create a cheque writing programme. In the datasheet I referred to I will enter the details of the cheques I want to print. The field I referred to is the cheque number field. It would be handy, when entering the next cheque, if I could just hit a button that would copy the cheque number above it and add 1 to it.

I can not use the Dmax function because in my company use different cheque books with the same account number at the same time.

Anyway, The only way I can manage to automate this process is as follows using the F4 key: Maybe you can think of a better way of doing it.

Private Sub Cheque_Number_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode
Case vbKeyF4
SendKeys "^{'}"
If IsNumeric(Me.Cheque_Number) = False Then
Me.Dirty = True
Exit Sub
End If
Case Else
End Select

End Sub

Private Sub Cheque_Number_KeyUp(KeyCode As Integer, Shift As Integer)

Select Case KeyCode
Case vbKeyF4
If IsNumeric(Me.Cheque_Number) = False Then
Exit Sub
End If
Me.Cheque_Number = Me.Cheque_Number + 1
Case Else

End Select

End Sub

 

[AlanW]

Thanks for you help Pat. I understand what you are saying but clearly there is a need to copy the cell above within Access otherwise Microsoft wouldn’t have created the Ctrl+’ function. Anyway. My code works for me . . .So I’ll use it