Single Criteria With Multiple Parameter Query With A Twist

[mindbenderz]

I did that. I created a seperate module outside the form, a public one, and saved it as "ReWriteQueryDe f".

[dsatino]

The name of the module has no relevance, "module1" would be fine. All the code I gave you has to be in a public module, the one you created is fine, after that it should work. But since I can't test it, the best I can do is help you with any errors.

[mindbenderz]

Ive tried

=ReWriteQDef
=ReWriteQDef()
ReWriteQDef
=ReWriteQueryDe f
=ReWriteQueryDe f()
ReWriteQueryDef

in the AfterUpdate event, and they all pretty much give the same error. Either it cant find the object, or the expression has a function name it cant find.

[dsatino]

Ah. I think I know what's going on.

Open the form in design view.
Right click the field(s) which you want to run the AfterUpdate.
Click properties.
Click the Event tab.
Click the AfterUpdate line. Remove anything you typed in it.
Click the "..." at the far right of the line.
Choose 'Code builder'

This will bring you to the vba editor and put you inside the AfterUpdate event sub of the control. Type in: ReWriteQueryDef

[mindbenderz]

when I do that, and enter a value in the field I get the following error "Expected variable or procedure, not module"

[dsatino]

That's probably because you name the module 'ReWriteQueryDe f'. You need to change that. Pick anything.

[mindbenderz]

I just renamed it to "mod_ReWriteQue ryDef". Should I put ReWriteQDef in the vba event, and not ReWriteQueryDef ?

[dsatino]

I'm not sure what you're asking on that one....

As long as all the code I gave you is posted inside 'mod_ReWriteQue ryDef' then just go back to post #19 and follow that.

So if you call the sub from the AfterUpdate event of the vend_sr box, the property line of the control will say "[Event Procedure]". If you click on the "..." button (code builder), you should see this code:

Private Sub vend_sr__AfterUpdate()
     ReWriteQueryDef
End Sub

[mindbenderz]

I did all that. It still gives me the "Expected variable or procedure, not module" error. Is there a way for me to actually attach a stripped down copy of the db here so you can see what is going on?

[mindbenderz]

ok, I think I almost have it working. what I notices is, though, is that when I enter text in to one of the fields and it updates the subform results, the text I enter in to the field actually writes in to FormQuery's criteria fields. when I clear the fields on the main form, how can I get it to remove the criteria it set in to the query? also, it isnt completely displaying all the results in the subform display. Ill keep playing with the module, but basically the text I enter in to the form can be either in 1 of the query fields that is linked, a combination of them, all of them or none of them. if none of them, then it should display no results. I have to see if I can get this logic to work.

[NeoPa]

I don't plan to get involved here as it looks pretty involved already without my adding to that. What I will do, on a take it or leave it basis, is to drop in a couple of links which may help you understand the whole issue a little more clearly (or even find an alternative solution for your issue instead).

Example Filtering on a Form
Cascaded Form Filtering

[dsatino]

There is a way to attach a db, but I've never done it. I'm sure you'll have to zip it first. If you go ahead and do that, I'll take a look. At a minimum I'll be able to better understand what you're doing since I can actually see it.

[NeoPa]

See Attach Database (or other work) for how do do it properly ;-)