Fill in value for next field

[dpw]

I would like to copy the contents of a field to the same field in the next record regardless of which record has been edited. E.g. If I change the ‘Customer Number’ field of Record 3 to 78 I want the ‘Customer Number’ of Record 4 to change to 78 and the focus set back onto Record 3. I’ve attempted to use code incorporating Bookmarks but I’ve failed miserably. Any ideas would be appreciated.

Damian

 

[Everton]

I'm assuming you have a form based on a table or query, which you are using to input the data. If so try something like this in the afterupdate event of the Customer number text box:

Private Sub Customer_number_AfterUpdate()

Dim dbs As Database
Dim rst As Recordset
Dim curnum As Integer

curnum = Me![Customer number]

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YOUR TABLE/QUERY", dbOpenDynaset)

With rst
.FindFirst "[PRIMARY KEY FIELD NAME] =" & Me![PRIMARY KEY CONTROL]
.MoveNext
.Edit
![Customer number] = curnum
.Update
End With

End Sub

HTH,
Paul.

 

[Pat Hartman]

There is something very wrong with this process. How are the two records related? What constitutes the next record? Unless you have a unique id so that you can find the record with the next highest id, there is nothing to guarentee proximity of any two records even if they are sorted.

 

[Everton]

I agree that the records need to have a unique ID, which is why the FindFirst command looks in the recorset for the record with the same value as the Primary Key field. For this to work you need to have textbox bound to the Primary Key field on your form.

Providing the order of the records in the table never changes, and the form data is not filtered, then you should be OK. However, Pat has a good point about the fact that there seems to be no firm relationship between one record and the 'next'.

Perhaps a better way would be to have a number field that determines the order of the records, eg [ORDER], and use:

With rst
.FindFirst "[ORDER] =" & Me![ORDER] + 1
.Edit
![Customer number] = curnum
.Update
End With

Any more thoughts?
Paul.