Copy Cell Above and Add 1

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?

This is a spreadsheet question. You need to think differently when working with a relational database. If what you want to do is to assign a unique sequential value, look up "DMax()" and "autonumber" for a gazillion posts that explain how to generate your own sequence numbers.

Tables are not spreadsheets and you never actually view or work with a table, you always view a recordset (even if you open a table from the database container, Access builds a query to obtain data from the table) and a recordset is by definition an UNORDERED set. You must specificlly order a recordset to ensure that it has a predictable order.

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

if I could just hit a button that would copy the check number above it and add 1 to it

Click to expand...

This really is the wrong way to think about a table. Your head is in spreadsheet land. What will the number be for the first check in a series? There will be no row "above". How would your code determine the value? Are you beginning to see the problem?

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

Click to expand...

If you think that you can't use DMax() then you haven't looked at the help entry for it. The third argument is the equivalent of a where clause without the where so you would specify the checkbookID so you could find the Maximum check number issued for that particular checkbook. Hopefully, you don't use different tables for different checkbooks since that would cause more problems.

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

There is no such thing as a "cell" in a relational database. I am telling you to use the DMax() because that is the correct method in a relational database. This method will always work provided you identify the correct domain. Trying to find the previous record will NOT work if the recordset is not properly sorted or there is no previous record. To convince yourself that I am correct, click in a field other than checknumber and click on the sort descending button. Now when you try to assign a new check number, you will almost certainly get the wrong number. Granted, someone has to mess up the sort sequence but the fact is, when you use inferior methods you get what you get.

Me.YourCheckNum = Nz(DMax("YourCheckNum", "Yourtableorquery", "YourCheckBookNumber = " & Me.YourCheckBookNumber), 0) + 1

You may certainly continue on your current path but it is WRONG.