using a function an control source 3

[jamaarneen]

Hi to all of you,

I wan to use a function in a control source of a textbox, and it doesn't works.

the thing is, that it's a own developed function. the function should convert a numeric value to a certain string. (the values and the strings are stored in a table. )

the function works ok when it is triggered in an event, but now i would like tio use it as a control source?

is it possible? any suggestion?

Thanks
ja

 

[formerTexan]

Setting the textbox's controlsource to a function should work unless:

1. You are calling a non-existant function
2. or a function out of scope
3. or a function that isn't visibly doing anything
4. or you are improperly referring to the function

=fFunction() will work if there is a function by this name within scope.

Cheers, Bill

 

[jamaarneen]

Thanks Bill

option 1-3, are excluded, because my function is working properly in the click event of another control (what i made only to test) on the same form.

so the problem might be something like option 4 - some syntax problem. the thing is, that the exact syntax that works in the event of my test-control, doesn't work in the 'control source'.

my function takes tow arguments, an integer & a string:

 ListValueToText(intListValue As Integer, strListHeaderName As String)

.
(it's actually a 'dlookup' function, that searches for the value "ListHeaderName", and then gets the string-value for the numeric-value(intListValue) and it returns a string)

in the control source, the intListValue I'm passing, is a field-name what's bound to the form. here is the code:

 =ListValueToText (vol_Frequency,"VolunteerFrequency")

???
Ja

 

[dhookom]

Can you share the function?
Have you tried setting a break point in the funtion?

Duane
Hook'D on Access
MS Access MVP

 

[formerTexan]

When you say that the function doesn't work - are you raising an error, or not getting the expected results?

When you set a breakpoint as Dwight suggested and step through your function, look and see if you are actually passing a value for intListValue

Cheers,
Bill

 

[jamaarneen]

Hi, Thank you all, really appreciating your answers.

when i use the function in the click event of a textbox on the same form - it works perfect.

but when put the function (in design view) as a row source for a text box, then all i get (in form view)in the text box is: #name?.
(and yes, i had set a breakpoint in the function, but it only get there when i click on my testing-textbox).

Ja

 

[PHV]

Is vol_Frequency a field in the form's underlaying query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[formerTexan]

I've got to guess that you are mistyping something and this is
validated by the #name? error. Access is usually clever enough to recognize control names and will bracket [] them turning

=ListValueToText(vol_Frequency,"VolunteerFrequency")

into

=ListValueToText([vol_Frequency],"VolunteerFrequency")

The lack of control bracketing in your control source example possibly indicates where something is amiss. Unfortunately we can't see your code or control names, so check your spelling again.

Cheers,
Bill

 

[dhookom]

Still no reply with the actual function code :-(

I assume the "row source" in "when put the function (in design view) as a row source for a text box" should be "control source".

You must also be careful the name of the text box is not the name of a field in the Record Source of the form.

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Thanks a lot!!!

PHV:
Yes, vol_Frequency is in the forms recordset.
BTW, I have also, added a control (visible=false) on the form which is bound to 'vol_Frequency' and is named 'vol_Frequency' because otherwise the click event on my testing-control wouldn't work either (it would stop on 'Me.vol_Frequency', and give an error: "Method or data member not found")

Bill:
That's what I'm thinking, that it is a syntax matter, and I have tried with brackets too (the bracketing issue is driving me crazy).

Duane:
1) here is my function (sorry for the delay...)

Public Function ListValueToText(intListValue As Integer, strListHeaderName As String) As String
    Dim strResult As String
    strResult = ""
    strResult = DLookup("lst_ListDetailName", "tblListDetails", "lst_LisDetailValue = " & intListValue)
    ListValueToText = strResult
End Function

2) "control source" indeed

3) don't worry, Access reminded me to be careful of that... So, I already added "txt_" to the controls name

Thanks again,
Ja

 

[formerTexan]

Ja

The function is OK although going off topic: DLookup may return a null if a matching record isn't found and string variables don't accept nulls. You might want to change strResult to a variant data type.

Are you sure that you have either a form control or field in the form's recordset that is named vol_Frequency?

Cheers, Bill

 

[dhookom]

I'm not sure why you pass in "strListHeaderName As String" as an argument and then don't use it.

What happens if you change the control source to:

= DLookup("lst_ListDetailName", "tblListDetails", "lst_LisDetailValue = " & [vol_Frequency])

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Bill:
- thank you for the comment.
- yes, I'm sure i have a control AND a field in the form's recordset named vol_Frequency! in fact, my is working in the click event of a testing-textbox. this is the code in the click-event:

Text20 = ListValueToText(Nz(Me.vol_Frequency, 0), "VolunteerFrequency")

Duane:
- I tried it already, but access won't let me: "the expression you entered contains invalid text"

- you are right about the "strListHeaderName As String"-argument. to be honest: I was wondering myself why this argument is in ... (well, I started this project about a year ago, and now I'm back... so sometimes I cannot get myself... and I'm not experienced at all...)

 

[dhookom]

There is a difference between

=ListValueToText (vol_Frequency,"VolunteerFrequency")

and

= ListValueToText(Nz(Me.vol_Frequency, 0), "VolunteerFrequency")

Does vol_frequency always contain a numeric value or is it possibly null?

Is your field name in the table actually "lst_LisDetailValue" or should it be "lst_Lis[red]t[/red]DetailValue"?

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Hi to all of you: GOOD NEWS

I got it!!! unbelievable. this code does work as a charm:

=ListValueToText(Nz([vol_Frequency][b][red];[/red][/b]0)[b][red];[/red][/b]"VolunteerFrequency")

while this code fails:

=ListValueToText(Nz([vol_Frequency][b][red],[/red][/b]0)[b][red],[/red][/b]"VolunteerFrequency")

see the difference??? irritating, but that's access-life...

our lesson: to put in a control source a [red];[/red] instead of a [red],[/red]

thanks to all of you
Ja

 

[dhookom]

Our lesson is to determine the language of the poster so we know if commas or semi-colons are expected in expressions.

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

By "language of the poster" did you meant programming language?
because it is an 'access' forum here... or id it my version that's missing... so it's access 2003

thanks,
Ja

 

[PHV]

In fact you have to use the list separator character defined in the regional settings.

 

[jamaarneen]

Thanks PHV for your comment, and now I do understand Duane's previous reply.

But now is my question (what I'm frightened of):
What will happen if I'll use my database on another PC, where the list separator is comma - will it make problems???

One more question (what I'm just curious):
If my list separator is a semi-colon, so why then in VBA code he will accept only comma's and NOT semi-colons? (it's only while entering a control source in the properties-box where he required semi-colons)

Ja

 

[PHV]

Seems that design view is localised and VBA isn't.