By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,423 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,423 IT Pros & Developers. It's quick & easy.

Button in Form to assign field value based on previous record

P: 2
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!
Jan 4 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
4 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jan 5 '17 #2

P: 2
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...
Jan 8 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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:-

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command1_Click()
  3.  
  4.     If IsNull(MyField1) Then         ' Blank Field
  5.         MyField1 = DLast("Myfield1", "MyQuery")
  6.     End If
  7.  
  8. End Sub
  9.  
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
Jan 8 '17 #4

NeoPa
Expert Mod 15k+
P: 31,438
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.
Jan 8 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.