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
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
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?
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
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?
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
