Button in Form to assign field value based on previous record

[dirtbooksun]

Sorry i don't have VBA experience but i'm working on a form in Access. Often there will be the same data from entry to entry as the previous record.
So what i want to do is have a little button beside the 4 or 5 fields that i know will likely be repeated and then if the user clicks that button then the field value is copied over from the previous record.

I can't find anything online with this specific example to copy for code. Obviously it would have to have the field name in there somewhere as i'm not looking to copy everything or to have any default values.
Thank you!

[PhilOfWalton]

Are you aware that pressing the Ctrl key & "'" (single quote) together does what you want?
On my keyboard the "'" is on the same key as the "@". Anyway, it's ascii code is 39.

Phil

[dirtbooksun]

Thanks yes i'm already aware of this. I'm looking for a way however to use the mouse to fill in the fields with a single click rather than the keyboard. This is because the mouse will be the primary input method due to the number of required dropdown boxes and if i can create a button that gets clicked to populate fields with the same value as previous this will significantly speed up the data entry which is the aim. I had the same setup on a previous database in Filemaker pro and it was great - but that program doesn't use visual basic so i can't just copy it over...

[PhilOfWalton]

So suppose your 5 fields are named "MyField1", "MyField2" ... "MyField5" and the undetlying recordsource for the form is "MyQuery", then you need a command button against each field that has code something like the following:-

 
Private Sub Command1_Click()
 
    If IsNull(MyField1) Then         ' Blank Field
        MyField1 = DLast("Myfield1", "MyQuery")
    End If
 
End Sub
 

Be aware that this will copy the last record entered. Regardless of whether MyQuery is sorted, It will be the last record in the underlying table.

The Ctrl & single Quote will produce the same result, so your user will have 2 methods of copying the previous information, but the latter method does not check that the field is empty before copying, so may be best avoided unless one is careful.

Phil

[NeoPa]

As Form Recordsets are often filtered it is unsure that such an approach will always be reliable. There are actually .Recordset & .RecordsetClone properties for a Form though, and some recordset navigation may be helpful to select and get the previous record. I mention .RecordsetClone specifically so that you can access other data without moving off the current record in the Form itself.

I hope that points you in the right direction for a fully-reliable solution. You'll see from this that the question, while seeming relatively straightforward in human terms, is anything but when dealing with databases and Forms in particular. Nevertheless you should have all the tools you need here with which to create your solution.