MS Access database support - Problem with VBA Filter function

Author

Problem with VBA Filter function

hadardo@bezeqint.net

2006-04-06, 3:29 am

Hello all.
Let me start by saying that I don't use VBA so often, so I ask for your
forgivness if my questions is somewhat silly.
I'm trying to find if a user typed the same value in two or more
controls in an unbound form. In order to do so, I moved the content of
the controls to an array (that was the easy part) and used the Filter
function in order to count the occurence of a each value. However, when
I ran the code I got the 450 error message: "Wrong number of arguments
or invalid property assignment" and the Filter word was highlighted.
After a bit of more trials, I wrote the simplest code:

Private Sub Button1_Click()
Dim StringArray(2) As String
Dim ResultArray() As String
Dim SearchString As String
Dim i As Integer

StringArray(0)="a"
StringArray(1)="b"
StringArray(2)="c"

SearchString="a"

ResultArray = Filter (StringArray,SearchS
tring)
i = Ubound(ResultArray)- Lbound(ResultArray)+
1
MsgBox i
End Sub

Still got the error. I tried to run the code both on Access 2K and
Access 2002. Same results.
I tried to use the other two argument in the Filter function - no use.
What am I doing wrong?

Anthony England

2006-04-06, 7:41 am

<hadardo@bezeqint.net> wrote in message
news:1144312096.141431.272190@i39g2000cwa.googlegroups.com...
> Hello all.
> Let me start by saying that I don't use VBA so often, so I ask for your
> forgivness if my questions is somewhat silly.
> I'm trying to find if a user typed the same value in two or more
> controls in an unbound form. In order to do so, I moved the content of
> the controls to an array (that was the easy part) and used the Filter
> function in order to count the occurence of a each value. However, when
> I ran the code I got the 450 error message: "Wrong number of arguments
> or invalid property assignment" and the Filter word was highlighted.
> After a bit of more trials, I wrote the simplest code:
>
> Private Sub Button1_Click()
> Dim StringArray(2) As String
> Dim ResultArray() As String
> Dim SearchString As String
> Dim i As Integer
>
> StringArray(0)="a"
> StringArray(1)="b"
> StringArray(2)="c"
>
> SearchString="a"
>
> ResultArray = Filter (StringArray,SearchS
tring)
> i = Ubound(ResultArray)- Lbound(ResultArray)+
1
> MsgBox i
> End Sub
>
> Still got the error. I tried to run the code both on Access 2K and
> Access 2002. Same results.
> I tried to use the other two argument in the Filter function - no use.
> What am I doing wrong?

Because you have written the code in a form's module, there may be confusion
with the Filter property of the form. Try putting VBA.Filter to clearly
show you mean to use the Filter function from the VBA library.
Having said that, I don't know if this is how I would implement what you are
trying to achieve. Could you not simply loop through the controls on the
form by using the Me.Controls collection.

hadardo@bezeqint.net

2006-04-06, 7:41 am

Anthony.
First, Many many thanks!!!
the VBA. prefix worked like a charm!

Second, your suggestion about looping through the controls is also good
and I used it as my first solution, but I had to ditch it since I need
to take only part of the controls and not all of them. At First, I
thought that I can't use a statement like Me.Controls(x).ControlType
(Thank you Microsoft so much for that UNDOCUMENTED feature). But after
your reply, I thought it over again and tried it. It worked!

Many thanks.

Anthony England

2006-04-06, 7:41 am

<hadardo@bezeqint.net> wrote in message
news:1144317957.094313.148280@u72g2000cwu.googlegroups.com...
> Anthony.
> First, Many many thanks!!!
> the VBA. prefix worked like a charm!
>
> Second, your suggestion about looping through the controls is also good
> and I used it as my first solution, but I had to ditch it since I need
> to take only part of the controls and not all of them. At First, I
> thought that I can't use a statement like Me.Controls(x).ControlType
> (Thank you Microsoft so much for that UNDOCUMENTED feature). But after
> your reply, I thought it over again and tried it. It worked!
>
> Many thanks.

Great.
Thanks for the feedback.

salad

2006-04-06, 11:34 am

hadardo@bezeqint.net wrote:

> Anthony.
> First, Many many thanks!!!
> the VBA. prefix worked like a charm!
>
> Second, your suggestion about looping through the controls is also good
> and I used it as my first solution, but I had to ditch it since I need
> to take only part of the controls and not all of them. At First, I
> thought that I can't use a statement like Me.Controls(x).ControlType
> (Thank you Microsoft so much for that UNDOCUMENTED feature). But after
> your reply, I thought it over again and tried it. It worked!
>
> Many thanks.
>
Sometimes you can put a value in the Tag property. That way you can
look through the controls...maybe test if its a text or combo, and look
at the tag. If it contains a value, say 1, then process, else ignore.

Sponsored Links