Access2000: UDF in combo's query

A

Arvi Laanemets

Hi

On form fmMain is subform sfDeviceGroups with form fmDeviceGroups as source.
On form fmDeviceGroups is subform sfDevices with form fmDevices as source.

On form fmDeviceGroups are 2 unbound combobox controls: cbbDevGroup and
cbbUserLevel.
On form fmDevices is a navigation combobox cbbSelectDevice. At moment
devices list to select from depends on cbbDevGroup value from parent form:
SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " &
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((b.TabN)=a.CurrUser) And _
((Left(a.DeviceID,1))=[Forms]![fmMain]![sfDeviceGroups]!txtDevGroup)) _
ORDER BY a.CurrUL, 2;

So long it works.

Now I need to apply a additional filter: cbbUserLevel can have 11 different
text values. Depending on value of cbbUserLevel, I want in cbbSelectDevice
displayed devices with field CurrUL equal to one of values in integer range
0 - 9, or all devices except CurrUL=9

I created an UDF, which uses cbbUserLevel value as parameter and returns
values in range 0-9 or 99.
Public Function GetUL(parSelection As String) As Integer
Select Case parSelection
Case "All except user level 9"
GetUL = 99
Case "User level 0"
GetUL = 0
Case "User level 1"
GetUL = 1
Case "User level 2"
GetUL = 2
Case "User level 3"
GetUL = 3
Case "User level 4"
GetUL = 4
Case "User level 5"
GetUL = 5
Case "User level 6"
GetUL = 6
Case "User level 7"
GetUL = 7
Case "User level 8"
GetUL = 8
Case "User level 9"
GetUL = 9
End Select
End Function

Now when I use this function as source for some unbound textbox control, it
works perfectly.
It also works, when I use it in some saved query, p.e.
SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " & _
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((a.CurrUL)=GetUL("User level 1")) _
And ((b.TabN)=a.CurrUser) _
And ((Left(a.DeviceID,1))="A")) _
ORDER BY a.CurrUL, 2;

But when I use this function in combo's row source query, nothing is
returned! Here is an testing example, which by default must return all
devices from group, because default value of cbbUserLevel is "All except
level 9", so GetUL() must return 99:

SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " &
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((a.CurrUL)<GetUL([Forms]![fmMain]![sfDeviceGroups]!cbbUserLevel)) _
And ((b.TabN)=a.CurrUser) _
And
((Left(a.DeviceID,1))=[Forms]![fmMain]![sfDeviceGroups]!txtDevGroup)) _
ORDER BY a.CurrUL, 2;

, which returns nothing. When I replace UDF with value 99, all is OK.

I also tried to use a calculated control with UDF as source, but it didn't
work also. Probably it isn't possible to refer to calculated control from
query - I remember I read something about it.



I have lost almost 2 days on this problem, and any help is welcome! Thanks
in advance!
 
D

Douglas J. Steele

It would appear that your reference to the control on the subform is
incorrect.

First, make sure that the name of the control on the parent form that's
holding the subform is, in fact, sfDeviceGroups (depending on how the
subform was added to the parent form, they may not be the same name)

Assuming it is, you need to use
Forms]![fmMain]![sfDeviceGroups].Form!cbbUserLevel

(If the name of the subform control is something else, replace
sfDeviceGroups with the name of the control)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arvi Laanemets said:
Hi

On form fmMain is subform sfDeviceGroups with form fmDeviceGroups as
source.
On form fmDeviceGroups is subform sfDevices with form fmDevices as source.

On form fmDeviceGroups are 2 unbound combobox controls: cbbDevGroup and
cbbUserLevel.
On form fmDevices is a navigation combobox cbbSelectDevice. At moment
devices list to select from depends on cbbDevGroup value from parent form:
SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " &
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((b.TabN)=a.CurrUser) And _
((Left(a.DeviceID,1))=[Forms]![fmMain]![sfDeviceGroups]!txtDevGroup)) _
ORDER BY a.CurrUL, 2;

So long it works.

Now I need to apply a additional filter: cbbUserLevel can have 11
different text values. Depending on value of cbbUserLevel, I want in
cbbSelectDevice displayed devices with field CurrUL equal to one of values
in integer range 0 - 9, or all devices except CurrUL=9

I created an UDF, which uses cbbUserLevel value as parameter and returns
values in range 0-9 or 99.
Public Function GetUL(parSelection As String) As Integer
Select Case parSelection
Case "All except user level 9"
GetUL = 99
Case "User level 0"
GetUL = 0
Case "User level 1"
GetUL = 1
Case "User level 2"
GetUL = 2
Case "User level 3"
GetUL = 3
Case "User level 4"
GetUL = 4
Case "User level 5"
GetUL = 5
Case "User level 6"
GetUL = 6
Case "User level 7"
GetUL = 7
Case "User level 8"
GetUL = 8
Case "User level 9"
GetUL = 9
End Select
End Function

Now when I use this function as source for some unbound textbox control,
it works perfectly.
It also works, when I use it in some saved query, p.e.
SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " & _
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((a.CurrUL)=GetUL("User level 1")) _
And ((b.TabN)=a.CurrUser) _
And ((Left(a.DeviceID,1))="A")) _
ORDER BY a.CurrUL, 2;

But when I use this function in combo's row source query, nothing is
returned! Here is an testing example, which by default must return all
devices from group, because default value of cbbUserLevel is "All except
level 9", so GetUL() must return 99:

SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " &
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
a.DeviceID AS DeviceInfo _
FROM tblITDevices AS a, tblUsers AS b _
WHERE (((a.CurrUL)<GetUL([Forms]![fmMain]![sfDeviceGroups]!cbbUserLevel))
_
And ((b.TabN)=a.CurrUser) _
And
((Left(a.DeviceID,1))=[Forms]![fmMain]![sfDeviceGroups]!txtDevGroup)) _
ORDER BY a.CurrUL, 2;

, which returns nothing. When I replace UDF with value 99, all is OK.

I also tried to use a calculated control with UDF as source, but it didn't
work also. Probably it isn't possible to refer to calculated control from
query - I remember I read something about it.



I have lost almost 2 days on this problem, and any help is welcome! Thanks
in advance!
Click to expand...
 
A

Arvi Laanemets

Hi

Thanks for response, but it didn't work.


Douglas J. Steele said:
It would appear that your reference to the control on the subform is
incorrect.

First, make sure that the name of the control on the parent form that's
holding the subform is, in fact, sfDeviceGroups (depending on how the
subform was added to the parent form, they may not be the same name)
Click to expand...

It's all OK. Really in same SQL there is another reference to another
control on same parent form, and it works fine. It looks like probles arise,
when I pass the control as parameter to UDF.

For testing I added UDF return value to calculated field in query, without
using UDF in WHERE clause. When I used text string as parameter passed to
UDF, it worked.
SELECT a.DeviceID, _
Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & _
Trim(Nz(a.Producer,"") & " " &
IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) & "; " & _
CStr(GetUL("All except user level 9")) & "; " & _
a.DeviceID AS DeviceInfo
FROM tblITDevices AS a, tblUsers AS b
WHERE (((b.TabN)=[a].[CurrUser]) AND _
((Left([a].[DeviceID],1))=[Forms]![fmMain]![sfDeviceGroups]![txtDevGroup]))
ORDER BY a.CurrUL, 2;

When I replaced the string with reference to control, the query did return
nothing again.

NB! I think I have something. It looks like control on parent form wasn't
initialized when combo on subform was populated. When I applied a default
value to combo, my test SQL started to work. I'll test more tomorrow, my
workday was over an hour ago!
 
A

Arvi Laanemets

Hi


Setting a default value for unbound combo, the UDF did refer to, was needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access2000: Problem with enabling/disabling controls in subform 1
Access2000 query (with subquery) 3

Top