I thought I knew something about Access and functions but obviously I don't know enough!
Very simple report, printing all the addresses in a database
Rather than concatenating the first name and surname and all the lines of the address in the control source box I thought I'd have a function or two to make it simpler.
So I have:
CallMe = Trim$([Surname] & " " & IIf(IsNull(Fornames$), "", Trim$(Forname$)))
Then I try to set a text box source to =CallMe() and
When I try print preview I get the oh so helpful #Error
I've tried Me.[Surname] etc with the same result
I thought all fields in the current record were available in functions.
What has me even more puzzled is that whilst trying this out I rewrote it passing Surname as a parameter and SOMETIMES I could access Fornames without passing it and sometimes I could not.
Please someone tell me what I'm overlooking.
Thanks Sinndho, for your swift and helpful reply.
I know your code will work fine, I was tired and frustrated when I posted the question, can I refine it a little and ask a more general question?
I have programmed in Access from Access 2 onward and worked as a professional programmer in other languages. Fundamentally I'm asking what is the Scope of the current record in an Access 2010 report.
I thought, obviously wrongly, that the current record was global within the report and so fields in that record could be accessed anywhere. But it seems that they are not.
If I were writing this from the ground up I could pass the current record to the function but because I'm using Access to do the grunt work I don't know the name of the current record to pass it to the function.
Your method, although good practice and clear, means I will have to pass all the individual fields I want to concatenate as parameters (address1, address2 ect).
Is there no way to pass the current record or to access the fields in the current record within the function?
I may have found my own solution, although I'm still unsure about the scope of fields in an Access report.
This is what I have done, in case anyone else has the same problem:
I created a group on Surname
Then used the "Add existing fields" button to copy all the fields into the Group Header, Access conveniently names each txtbox with the name of the field.
I set the header visible to "NO"
Now I can refer to the contents of any field by it's name in my function,
Myfunc= Field1 & Field2
1. The contents of the current record is accessible through the Fields collection of the Recordset object. Each bound form or report in access has an embedded DAO.Recordset object. In Forms, it's rather easy to "forget" that because you can use the shortcut:
Due to the specific treatment of the data set in a report, things are not so easy. In a report, the whole data set (= all the records in the Recordset object) are processed when the report opens.
2. You could call the concatenation function in the query that is used as a data source for the report instead of formating the data at the report level.
Thanks Sinddho, that makes it much clearer.
As I said in my last post, which doesn't seem to have made it through moderation yet, I have solved the problem by placing all the fields in a Group Header which I then render invisible and that works well for this quick!!! and dirty project.
If I were starting afresh I think I would take your advice and do the concatenation in the underlying query.