Using function to specify a control value for criteria gives an error

[inso18]

Hi All.

I've encountered a problem I do not understand, and seeking for advice from people who do.

In a frmChooseBooks which is bound to qryChooseBooks, I have an unbounded cmbCountrycontrol, an anbounded cmbAuthor control, and bounded [BookName] and [ChooseBook] control.

(Logically, each country has several authors, and each author has several books)

qryBooks is based on tblBooks which has [CountryId],[AuthorId],[BookName],[ChooseBook] fields.

I also created global functions in public module to simplify the task of specifying the criteria for different queries used for the form:

Function [b]fCountry()[/b]
    fCountry = Forms!frmChooseBooks!cmbCountry
End Function

same for fAuthor()

But when I use fCountry() or fAuthor() to specify the criteria in qryChooseBooks, or when I use fCountry() to specify the criteria for cmbAuthor (to filter only the authors who are in the selected country) I get the following error:

Run-time error '2186'
This property isn't available in Design view.

with the debugger going straight to that public function.

When I use fCountry() to specify the default value of cmbAuthor(), the value doesn't get set.

Only when I use the full control name - [Forms]![frmChooseBooks]![cmbCountry] in queries and default value fields do I get the results.

Is it possible to use functions (as shortcuts) for the control values and make it work?

Need advice,
inso18.

 

[gol4]

depends on how you intend to use the function and what you want it to return

Function fCountry()as control
fCountry = Forms!frmChooseBooks!cmbCountry
End Function as control

or
Function fCountry()as string
fCountry = "Forms!frmChooseBooks!cmbCountry"
End Function as control

 

[inso18]

gol4,
What does it mean:

End Function as control

I just use

Function fCountry()
...
End Function

It returns an integer, which is what I expect it to.

 

[Golom]

I think that "End Function as control" is just a typo. I don't recall that being valid syntax.

If you do things like

Function fCountry() As Control

or 

Function fCountry() As String

Then VB can perform type checking on your assignment.

Function fCountry()

however defaults to a Variant as the return argument and you can assign anything to the return value.

 

[MajP]

What you are doing should work fine. Your problem is that the form you are referencing is in design view, it has to be in form view.

A control in design view does not have a value property. Thus the error message.

 

[inso18]

MajP,

I'm not running the form in design view. (it isn't possible)

The criteria (both in query and in default value) works fine when I use the full control value reference (Forms!form!control) but doesn't work when I use function for reference (fControl())

 

[inso18]

MajP, you are right.
I didn't think there's was a difference, but when I run the form from design mode view, referencing control value with function doesn't work, but when I run it by double-clicking on it from Forms section it does.

Any idea why's the difference?

 

[MajP]

Not sure if this is your question but:

Your function
fCountry = Forms!frmChooseBooks!cmbCountry
is really short hand for
fCountry = Forms!frmChooseBooks!cmbCountry.value
since "value" is the default property of control.

If frmChooseBooks is in design view you can not reference the value within cmbCountry because it is not a design view property of a control(have you ever seen a value displayed in a control in design view?).

You can reference other properties such as "name","height","width" etc since these properties are available in design and form view.

 

[inso18]

MajP, the form isn't in design view, it is in form view - run from design view (by toolbar button form view)

When it is in form view run from design view the function doesn't work. When it is in form view run from form list it does.

Why is it so?

Did you understand my explanation/question?