How to call Function in Access Query

[Apple08]

Hi All

I need to paste in the year blank fields and I have the Function code below. However I have problem to call this Function in an query. Please could anyone help?


Function CopyFieldRecords(Test_File As String, HRYear As String, SerialID 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_File & "]")
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

 

[ranman256]

this is not a function, this is a routine.
This could be done in a query. No code needed.

A function will return a SINGLE value.
like

FUNCTION CIRCUMFERENCE(byval pRadius)
    CIRCUMFERENCE = 3.14 * 2 * pRadius        'assign the 1 value to the function name
end function

then in the query usage:
MyCirc: Circumference( 255)

 

[Apple08]

I am new to Access, sorry I don't get what you mean. Does it mean that I can input the codes in the Expression Builder?

 

[alansidman]

I believe that Ranman is suggesting an update query to do this. Look at this link for a tutorial on how to update records.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

and this will also demo update queries

MS Access 2007: Update Query that updates values in one table with values from another table

 

[Micron]

this is not a function, this is a routine.
This could be done in a query. No code needed.

I disagree. If it's declared a function, it is a function. Not disagreeing with the rest of the statement, nor would I disagree with anyone who says it should be a procedure/routine because it's not a proper function and doesn't need to be a function. Further to my point, this will return false by default:

Function test() As Boolean
MsgBox "value is " & test
End Function

This will return Null:
Function test()
MsgBox "value is " & test
End Function

Apple08's function returns true because its value is set on the fourth line. However, it provides no real advantage written as a function.

 

[james_lankford]

but he's trying to call it in a query

I highly doubt he's setting a field to true or false

there is just so much wrong with what he's trying to do

 

[Micron]

there is just so much wrong with what he's trying to do

Agree 100% not only because I wrote

However, it provides no real advantage written as a function

but because it contains pointless code:

If Nz(rec(HRYear), "") <> "" Then

If the value returned is Null, the line will translate to If "" <> "", which will never be true.

I'm only saying in my opinion the code is a function, albeit it is fairly worthless as such. I also disagree with the statement that ranman256 is suggesting the use of an update query since the OP is appending records. I would say the intent was to explain how to call a function from a query and that this

MyCirc: Circumference( 255)

was to explain how to set up a query field to call the function, probably in design view.

 

[Apple08]

Hi all, thanks for all of your comments. I only want to copy and paste the last year record into the next record as below:

HRYear
2013
(Blank)
(Blank)
2014
(Blank)
(Blank)
2015
(Blank)
(Blank)

I found the codes online but couldn't call it via sub. I would appreciate it if you could suggest other ways to paste them in automatically. Many thanks.

 

[Micron]

I for one have read your posts several times and still cannot grasp what you are trying to do. You want to copy from where? Paste to where? Paste what? Your example makes no sense to me. There's no way I can figure out which of the "records" are the "last year" nor where you want that value to go or even what it should be. All I see are a bunch of blanks. Maybe if you posted some pictures of what you have and what you want we could help. Make it up in Excel if you have to, just make sure you provide the needed names (of queries, or fields, forms, controls, or tables - whatever you're dealing with).

 

[Apple08]

Sorry for the confusion, the result I want is:

HRYear
2013
2013
2013
2014
2014
2014
2015
2015
2015

The blank cells are automatically copied from the last cell.