Trying to use BuildWhere to filter a form

[merlinar]

I'm trying to use the BuildWhere function by FancyPrairie to filter a form and put the results back into the form (make the recordsource the result of the function) or have a popup using the results. I put the code in the OnClick event of a command button, but I just get an error saying: Ambiguous name detected: BuildWhere. I was wondering if anybody might be able to help me get this working. Thank you!

 

[hermanlaksko]

Looks to me like you have added the function "BuildWhere" twice in your app - make a search for this name "BuildWhere" thru out your application


Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[merlinar]

Thank you for the reply.

Ok, I did have an extra copy of it on another command button. I removed it, an now I get the following error: "Compile Error: Invalid Inside Procedure" Any ideas?

 

[FancyPrairie]

Start over. Delete all of the code you copied from the FAQ. Now, create a new module and copy and paste all of the code from the FAQ into the new module. That should solve your problem.

Also, you don't want to set the Recordsource of your from equal to the return results of BuildWhere. Because BuildWhere only returns the Where clause (without the word Where) of the SQL statement. But you could set the Forms Filter property equal to BuildWhere.

 

[merlinar]

Thank you for the help! I've done what you suggested, but I'm not sure what line I would use to set the filter. Everything I've tried I get an error. What command should I use to set the filter? I've tried using docmd.applyfilter and also Me!filter = but I'm not sure what goes after the =. Any help would be much appreciated. Thank you!

 

[FancyPrairie]

Try this in the OnClick event of the button:

Me.Filter = BuildWhere(Me)
Me.FilterOn = True

To see the results of BuildWhere, try this:

Msgbox BuildWhere(Me)

 

[merlinar]

I did what you said there, and I get an error:

Compile error:
Expected variable or procedure, not module.

What am I doing wrong?

 

[PHV]

I guess you gave the same name to the Module and the Function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[merlinar]

I'm not sure what you mean. I'm pretty new to this aspect of Access, and I'm not sure what I need to change. This is what I curretly have:

Private Sub Command136_Click()

Me.Filter = Buildwhere(Me)
Me.FilterOn = True

End Sub

Thank you for your help!

 

[PHV]

When in VBE (Alt+F11), no module named Buildwhere in the Project browser window (Ctrl+R) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[merlinar]

I have a module called "Buildwhere"
Also, I noticed that in the me.filter=Buildwhere, I typed it as BuildWhere, but it kept changing it to Buildwhere (with a small w) I changed the name of the module to the same (small w) and I still get the same error.

 

[PHV]

Change the name of the module to something DIFFERENT like, say, myModule.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[merlinar]

Ok, I think I am beginning to understand...
I've changed the name of the module and left everything else the same (code). Now I'm getting an error that says:

You can't assign a value to this object.

 

[merlinar]

Thank you for your help! I seem to have got this part of everything working. Now I can't get the form to show the records that should have been filtered. I just gives me the same form with no information, except what I typed in to filter the form. Any ideas?

 

[merlinar]

Never mind, stupid error on my part.

Thank you for all your help!