Autofill cell based on previous record's value (auto increment)

[Apricot]

Hi everyone.

I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form)
i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new\next record

I have written some pseudo-code to show what I am trying to accomplish, but as my knowledge of VBA is minimal I am having trouble writing the code

If anyone has the time to convert the mess below into something access will understand I would appreciate it, or failing that If you can point me in the right direction.

Thank you.

if keydown = down-arrow and current cell contents isnumeric then
Cval = current cell contents
if current record = last record then
create new record
move down 1 record
set cell value of new record to cval+1
else
move down 1 record
if cell value = null then set cell value of record to cval+1
end if
end if

 

[JHB]

I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form)
i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new\next record

Is it only for the "down-arrow key"?
What about all the other possibilities for moving to next record, like by mouse, "Enter key" etc.?

 

[Apricot]

The form has been created in such as way as to limit the ways a new record can be created, I am not the original author, I am just trying to add a small modification. I have found out i can use the keydown event to start the event, for for the rest I need help.

 

[vbaInet]

You're thinking too hard about this. What I think you want is the Default Value of the field to be the last record's value + 1.

The default behaviour of a Continuous form or a form in Datasheet view is when you're in the last record and when you key down it will move to a new record.

 

[Apricot]

You are correct that is basically what I am trying to accomplish, but I am not sure how to accomplish it. I know how to set a default value, but not how to create a default value based on the previous record. Any help would be appreciated.

 

[vbaInet]

Try searching the forum for the DMax() function (or Google it), you'll find lots of examples that you can adapt.

 

[Apricot]

Correct me if I am wrong, but as far as I know Dmax returns the maximum for the entire field, not only the previous record.

 

[vbaInet]

Ok, good thinking. Is this value supposed to be a unique identifier or just a normal Number field?

 

[Apricot]

The field is a standard number field, I would have used a autonumber field , but the field has duplicate values.

 

[vbaInet]

Do you have any sort applied?

 

[Apricot]

Apologies for the delay in replying. The table has 16 fields in total. It is sorted by two number fields: Dock & Point.

 

[vbaInet]

What fields do you use to determine the previous record?
Also, I hope that the form's Record Source is a query with the sorts applied.

 

[Apricot]

It certainly would be easier if the record source was a sorted query, but in my case the data source is a table which contains randomly entered data. The form sorts the data by two number fields, first by Dock then by Point, both acceding.

 

[vbaInet]

You can do that yourself. Create a query based on the table, set the sort in the query, save and close then select the query name in the Record Source property of your form.

Get that sorted and we'll work on the code.

 

[Apricot]

Done, now what?

 

[vbaInet]

Done, now what?

Now run around the office shouting profanities at your colleagues

For testing purposes and so that you can understand what's going on:
1. Place a button in the header section of your form
2. In the Click event of the button add this code:

    Dim rsForm As DAO.Recordset
    
    Set rsForm = Me.RecordsetClone
    
    With rsForm
        .MoveLast
        MsgBox Nz(![[COLOR="Blue"]FieldName[/COLOR]], 0) + 1
    End With

3. Go back to your form and test the code by clicking the button.

 

[Apricot]

Doing this in VBA is much longer than I originally envisaged. As I promised to get this done by lunch on Monday I have decided to separate the code from the data by moving the database to SQL Server and writing a custom front end in C#. Thank you for your assistance.

 

[vbaInet]

It's only a few lines of code.

At least you're happy with your approach and got it working.

 

[FreshPlantain]

Not OP, but I was following along and now I'm stuck.

I have the FieldName appearing in a Message Box, but how do I get that value into the new record that hasn't been written yet?

 

[FreshPlantain]

Ok, I took a different approach

This code fires on the AfterInsert event. So when I hit TAB and go to a new record, voila.

FieldName.DefaultValue = """" & [FieldName] & """"

I'm not sure it's best practices, but whatever. Google was surprisingly silent in answering this question.