Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2014
    Posts
    4

    Red face Unanswered: User function in access 2010 report

    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:

    Function CallMe()
    CallMe = Trim$([Surname] & " " & IIf(IsNull(Fornames$), "", Trim$(Forname$)))
    End Function

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You don't pass any parameter to the function. Try:
    Code:
    Function CallMe(Byval Surname As Variant, Byval Fornames As Variant) As Variant
        CallMe = Trim(Nz(Surname, "")) & " " & Trim(Nz(Fornames, "")) 
    End Function
    Have a nice day!

  3. #3
    Join Date
    Feb 2014
    Posts
    4
    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?

    Thanks again

  4. #4
    Join Date
    Feb 2014
    Posts
    4

    Talking

    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,
    So
    Function MYFunc()
    Myfunc= Field1 & Field2
    end fuction
    works fine.

    Thanks anyway
    D

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    Me.[FieldName]
    or even
    Code:
    [FieldName]
    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.
    Have a nice day!

  6. #6
    Join Date
    Feb 2014
    Posts
    4
    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.

    Thanks again

    David

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •