Access VBA: How To: Implement User Function Code [RESOLVED]

Esteemed Forum Participants and Lurkers:
===============================
Access 2003 VBA

I'm a gutless wonder when it comes to Access (not to mention my gross disdain for the (in)capability of documenting??? an Access design!).

I'm trying to implement a user function in an access database. For testing purposes, I have the following trivial "triple" code in module "User_Functions":

Code:

---

Option Compare Database

Option Explicit
Function triple(anInt As Integer) As Integer
    triple = anInt * 3
End Function

---

I can't seem to get this to work in a Report, or in a Query, or in a Table column (which is what I would really like to do!). My Report control looks like this:

Code:

---

TextBox:  Text4
Control Source:  =triple(MSI_10_10!Code)

---

When I try to open the Report in Print Preview, I get a popup asking "Enter Parameter Value" for "MSI_10_10" which is the name of my table. I don't want a parameter. I just want an output for each record which (for now) = (Code x 3). If I enter a value in this popup, such as "99", my Report shows "#Error" in the Text4 control for the 2 integer values for the Code in my MSI_10_10 table ... 12 & 34.

Looks like your code is ok. Possible add Public before it (if it is in a module).

As to the value being a table well uh how can I put this...

Your function is expecting a variable (value).
You gave it a table.

To show the values, you need to put a field in for the initial values, then put that field into the function.

It should work then.

eg:
Source query
Select TheValue from TheTable

On the report in a text field
=TripleThis(TheValue)

Thanks Vince ... I'm still confused ...

Quote:

---

Your function is expecting a variable (value). You gave it a table.

---

Actually, I give it a FIELD in a table ... "(MSI_10_10!Code)" ... "Code" is the main field of interest in my table. I need to generate a second value for each record in the table which is the result of the function applied to the "Code" field for that record. If I can do this for a Report, that is good ... for a Query would be better ... or for a Table would be perfect.

In my end application, the table is getting built in one shot from a data base (this part of the code is being generated by other "newbies"). I'm trying to fill in the piece for loading the MSI field because I'm the only one who knows anything about the complex math/string function that is required to do the job.

Incidentally, the function will actually build a proprietary bar code text string from the "Code" number. When the report gets printed, it will use the bar code font for this field.

Gads ... I hate Access.

I found one dumb problem ... I was messing around with putting the function in different places, and so I changed the name of the function. Well, actually, I changed the name of the 'return' variable I was setting. I neglected to change the name of the actual function itself, so they did not agree. I caught that and fixed it and decided to create a new report using the report wizard. This time, in the expression builder, I put in the function first ... it found my tripleZ function just fine ... and then I highlighted the parameter inside the function parentheses and went to the Tables menu and found the "Code" field in the MSI_10_10 table. It inserted only "Code" into the function, and the thing worked perfectly. It created a Report with the Code in the first column, and Triple the Code in the second column for all records.[code]Option Compare Database
Option Explicit
Function tripleZ(anInt As Integer) As Integer
tripleZ = anInt * 3
End Function

Expression Builder for 2nd Text Box in Report - This WORKS!:
Control Source: =tripleZ(

Code:

---

)

---

I went back in and edited the Control Source expression in my original Report to delete the "[MSI_10_10]!" from in front of the "Code' field name, and if the Report "Record Source" is "MSI_10_10" (the Table name), then the dumb thing works.

Is there any reasonable explanation for what all is going on here? If I hadn't stumbled across that situation where the wizard moved the Table Name into the Report Record Source, I may have NEVER found this.

Thanks for any and all comments, suggestions, and assistance.

This doesn't make any sense at all. Sometimes the Expression builder puts in the errant "[MSI_10_10]!" table name and sometimes it doesn't! With the table name in the expression, I get the parameter popup. Without it, I get my correct report. I haven't figured out what determines which way it goes. Who is confused, and who is having fun?