98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Copy previous record to the next Access Function | Access forum
Copy previous record to the next Access Function | Access forum
Resolved · Low Priority · Version 2013
Fri 21 Aug 2015, 09:35Delegate Elsa said...
Elsa has attended:
Access Intermediate course
Power BI Modelling, Visualisation and Publishing course
Copy previous record to the next Access Function
Hi, I need to copy previous years to the next blank fields in Access:
2013
(Blank)
(Blank)
2014
(Blank)
(Blank)
2015
(Blank)
(Blank)
I have found the Function code below, but I have no idea how to recall this function in a query. Please could anyone help?
Function CopyFieldRecords(Test_Table As String, HRYear As String) As Boolean
Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant
CopyFieldRecords = True
On Error GoTo err_copyrecords
vCopyDown = Null
Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * FROM [" & Test_Table & "]")
While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(HRYear), "") <> "" Then
vCopyDown = rec(HRYear)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(HRYear) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend
exit_copyrecords:
Exit Function
err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords
End Function
Fri 21 Aug 2015, 09:55Trainer Jens said...
RE: Copy previous record to the next Access Function
Hi Elsa,
Thank you for the forum question.
This function cannot be called from a query but must be called from a VBA sub procedure. IT can be done using DAO or ADO.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 21 Aug 2015, 11:14Delegate Elsa said...
RE: Copy previous record to the next Access Function
Thanks Jens for your reply. I have tried to call CopyFieldRecords from a form load sub VBA, however I got an error message 'Compile error: Argument not optional'. Have I missed anything please?
Fri 21 Aug 2015, 11:17Trainer Jens said...
RE: Copy previous record to the next Access Function
Hi Elsa,
Do you pass the arguments Test_Table and HRYear to the function from the sub?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 21 Aug 2015, 11:56Delegate Elsa said...
RE: Copy previous record to the next Access Function
Hi Jens, please could you give me an idea how to pass the arguments to the function from the sub?
Fri 21 Aug 2015, 12:17Trainer Jens said...
RE: Copy previous record to the next Access Function
HI Elsa,
Test_Table HRYear is the name of the table where you want the function to do some thing HRyear is the year.
the example below is a very simple function which calculate age. The function is called from the sub and the result will be shown in a message box.
Sub callFunction()
MsgBox age(#12/12/2000#)
End Sub
Function age(DateOfBirth)
age = Int((Date - DateOfBirth) / 365.25)
End Function
Create a sub procedure in the module with the function. Inside the sub type CopyFieldRecords("the name of your table", "the year you want").
Run the sub in a COPY of your database. I do not know how your tables are build and VBA can be very powerful and do a lot of damage if it is not correct.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Remove spaces in a tableIf you have a table that has too many space marks littered around, you can create a update query and use the trim function to get rid of any excess space marks |
