Single Criteria With Multiple Parameter Query With A Twist

[mindbenderz]

I have a form (frm_docs_lookup_list) with 3 field controls: equip_sr, po_sr, vend_sr

the form has a subform attached to : subfrm_docs_lookup_list

the results in the subform display based on what text a user enters in to the controls on "frm_docs_lookup_list". multiple fields in the subform refer to each of the 3 search fields. those are listed below


equip_sr: Title, equip, notes
po_sr: [PO Number], Path
vend_sr: Vendor, Manufacturer


the current SQL doesnt work since the criteria statements I am using dont work right yet. Ill post the 2 versions of the statements Ive tried below, and explain what the goal is Im trying to achieve

first attempt (swap out vend_sr with the other names for the appropriate fields):

Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null

Now, when I entered some text in "vend_sr" and in the query put the above string on 2 different lines in the query it worked. I get that and why. however, things start to get tricky for me here. 3 fields in the query look at 1 search control field, and the other 2 look at 2 each. at any given time, using the "vend_sr" control as an example, the Vendor or Manufacturer field could contain the text I enter into "vend_sr", they might not, or they both might. If neither do, then the subforms results would be empty (obviously). I tried using the code below in the criteria for Vendor and Manufacturer

Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null
 

but that didnt work. it just displayed all records where neither Vendor or Manufacturer contain a NULL value. Its almost like the two fields need to look at each other to assist in what results to display. and then when the other fields in the query refer back to their specified form controls, they could also see the form field as NULL or 1 or more of those fields could or could not contain the text entered in to those fields.

example:

I know I have 2 records in my database where Vendor contains the text "fleet". No Manufacturer records contain that text. If I enter "fleet" in to the form field vend_sr, the subform should display both of those records, pulling the results based on the fact that field Vendor contains 2 records which have that text.

Another example. I have 13 records which contain the text "sunbelt". 12 of those records have "sunbelt" in the Vendor field, 11 of the 13 contain "sunbelt" in the Manufacturer field. the 13th record that didnt have the value in Vendor is one that has it in the Manufacturer field, so all the results are basically 1 has it, the other has it, or both have it at the same time.


How would I make a query understand that in a line of code for the criteria? This is driving me crazy. I spent all day yesterday trying to figure out why my form wasnt working, and when I did I got even more confused on how to fix it. Thank for all your help.

[dsatino]

I'm not going to attempt to code anything for you, but I will give you some advice that may be helpful...

From the sound of it, you have numerous criteria that may or may not be used in the query. You have three combo boxes that may or may not produce several filter values each (I think).

Stop tyring to make everything work at once. Start with the first box and code for that. Once that is working, add the next one and test for that, etc.

Also, it appears you're just jamming all this garbage into the criteria line of a query. You're much better off dynamically generating the 'WHERE' statement of the sql that way you are asking for something specific in the query.

[mindbenderz]

no combo boxes, just text fields on the form. something typed in to one or all of them fill filter the query more. I can get 1 to work, for the most part. but when I try making any of the others work it not only messes up the first one, but they also dont work. and I dont know how to code any of the stuff you mentioned. Im still learning vba and have quite a ways to go

[dsatino]

Open the query in SQL view and post that...it will definitely help for people to see the totality of what you're doing.

[mindbenderz]

Here is what I have so far, it just doesnt work where it is now

SELECT tbl_documents.root_id, tbl_content_type.cont_type, tbl_documents.[Content Type], tbl_rigs.location, tbl_documents.[Assigned Location], tbl_documents.Title, tbl_documents.[Revision/Version Date], tbl_documents.[Asset Number], tbl_documents.Vendor, tbl_documents.Vend_ID, tbl_documents.Manufacturer, tbl_documents.Manuf_ID, tbl_documents.Model, tbl_documents.[Serial Number], tbl_documents.[PO Number], tbl_documents.[Pressure Rating], tbl_documents.Revision, tbl_documents.Path, tbl_asset_type.asset_subtype, tbl_documents.[Asset Type], tbl_documents.[PART NO], tbl_documents.[W/O & J/O NO], tbl_documents.modules, tbl_documents.RigID_Share, tbl_documents.[PKD Document Number], tbl_documents.qty, tbl_documents.Size, tbl_documents.[Destination Path], tbl_documents.XMIT, tbl_documents.DocID, tbl_documents.recvd_xmit, tbl_documents.draw_no, tbl_documents.[%_comp], tbl_documents.disc_type, tbl_documents.equip, tbl_documents.pkd_xmit, tbl_documents.tech_type, tbl_documents.[Received Date], tbl_documents.[End of Life Date], tbl_documents.[Supersede Date], tbl_documents.Version, tbl_documents.Ivara_Path, tbl_documents.[RO Number], tbl_documents.proj_name, tbl_documents.proj_phase, tbl_documents.issued_code, tbl_documents.approval_code, tbl_documents.owned, tbl_documents.[SO NO], tbl_documents.notes, tbl_documents.netpath, tbl_documents.user_add, tbl_documents.user_comp, tbl_documents.user_edit, tbl_documents.user_edit_date, tbl_documents.doc_filter, tbl_documents.date_added
FROM tbl_rigs RIGHT JOIN (tbl_asset_type RIGHT JOIN (tbl_content_type RIGHT JOIN tbl_documents ON tbl_content_type.ID = tbl_documents.[Content Type]) ON tbl_asset_type.ID = tbl_documents.[Asset Type]) ON tbl_rigs.ID = tbl_documents.[Assigned Location]
WHERE (((tbl_documents.Title) Like "*" & Trim([Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.Title) Is Null) AND ((tbl_documents.Vendor) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or (tbl_documents.Vendor) Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null) AND ((tbl_documents.Manufacturer) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or (tbl_documents.Manufacturer) Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null) AND ((tbl_documents.[PO Number]) Like "*" & Trim([Forms]![frm_docs_lookup_list]![po_sr]) & "*" Or (tbl_documents.[PO Number]) Is Null) AND ((tbl_documents.Path) Like "*" & Trim([Forms]![frm_docs_lookup_list]![po_sr]) & "*" Or (tbl_documents.Path) Is Null) AND ((tbl_documents.equip) Like "*" & Trim([Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.equip) Is Null) AND ((tbl_documents.notes) Like "*" & Trim(([tbl_documents].[notes])=[Forms]![frm_docs_lookup_list]![po_sr] & "*" Or ([tbl_documents].[notes])="*" & [Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.notes) Is Null))
ORDER BY tbl_documents.Path;
 

[mindbenderz]

Here is the last working version I have, just using criteria in the Vendor and Manufacturer fields of the query on different rows

SELECT tbl_documents.root_id, tbl_content_type.cont_type, tbl_documents.[Content Type], tbl_rigs.location, tbl_documents.[Assigned Location], tbl_documents.Title, tbl_documents.[Revision/Version Date], tbl_documents.[Asset Number], tbl_documents.Vendor, tbl_documents.Vend_ID, tbl_documents.Manufacturer, tbl_documents.Manuf_ID, tbl_documents.Model, tbl_documents.[Serial Number], tbl_documents.[PO Number], tbl_documents.[Pressure Rating], tbl_documents.Revision, tbl_documents.Path, tbl_asset_type.asset_subtype, tbl_documents.[Asset Type], tbl_documents.[PART NO], tbl_documents.[W/O & J/O NO], tbl_documents.modules, tbl_documents.RigID_Share, tbl_documents.[PKD Document Number], tbl_documents.qty, tbl_documents.Size, tbl_documents.[Destination Path], tbl_documents.XMIT, tbl_documents.DocID, tbl_documents.recvd_xmit, tbl_documents.draw_no, tbl_documents.[%_comp], tbl_documents.disc_type, tbl_documents.equip, tbl_documents.pkd_xmit, tbl_documents.tech_type, tbl_documents.[Received Date], tbl_documents.[End of Life Date], tbl_documents.[Supersede Date], tbl_documents.Version, tbl_documents.Ivara_Path, tbl_documents.[RO Number], tbl_documents.proj_name, tbl_documents.proj_phase, tbl_documents.issued_code, tbl_documents.approval_code, tbl_documents.owned, tbl_documents.[SO NO], tbl_documents.notes, tbl_documents.netpath, tbl_documents.user_add, tbl_documents.user_comp, tbl_documents.user_edit, tbl_documents.user_edit_date, tbl_documents.doc_filter, tbl_documents.date_added
FROM tbl_rigs RIGHT JOIN (tbl_asset_type RIGHT JOIN (tbl_content_type RIGHT JOIN tbl_documents ON tbl_content_type.ID = tbl_documents.[Content Type]) ON tbl_asset_type.ID = tbl_documents.[Asset Type]) ON tbl_rigs.ID = tbl_documents.[Assigned Location]
WHERE (((tbl_documents.Vendor) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null)) OR (((tbl_documents.Manufacturer) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null))
ORDER BY tbl_documents.Path;
 

[dsatino]

Ok. Open your vba editor and open new module. Paste this in:

Option Compare Database
Option Explicit
 
Public Const sqlSELECT As String = "SELECT docs.root_id, cont.cont_type, docs.[Content Type], rigs.location, docs.[Assigned Location], docs.Title, docs.[Revision/Version Date], docs.[Asset Number], docs.Vendor, docs.Vend_ID, docs.Manufacturer, docs.Manuf_ID, docs.Model, docs.[Serial Number], docs.[PO Number], docs.[Pressure Rating], docs.Revision, docs.Path, asset.asset_subtype, docs.[Asset Type], docs.[PART NO], docs.[W/O & J/O NO], docs.modules, docs.RigID_Share, docs.[PKD Document Number], docs.qty, docs.Size, docs.[Destination Path], docs.XMIT, docs.DocID, docs.recvd_xmit, docs.draw_no, docs.[%_comp], docs.disc_type, docs.equip, docs.pkd_xmit, docs.tech_type, docs.[Received Date], docs.[End of Life Date], docs.[Supersede Date], docs.Version, docs.Ivara_Path, docs.[RO Number], docs.proj_name, docs.proj_phase, docs.issued_code, docs.approval_code, docs.owned, docs.[SO NO], docs.notes, docs.netpath, docs.user_add, docs.user_comp, docs.user_edit, docs.user_edit_date, docs.doc_filter, docs.date_added"
Public Const sqlFROM As String = " FROM tbl_rigs rigs RIGHT JOIN (tbl_asset_type asset RIGHT JOIN (tbl_content_type cont RIGHT JOIN tbl_documents docs ON cont.ID = docs.[Content Type]) ON asset.ID = docs.[Asset Type]) ON rigs.ID = docs.[Assigned Location]"
Public Const sqlORDER As String = " ORDER BY docs.Path"
 
 
Public Sub ReWriteQDef()
    Dim qdef As QueryDef
    Set qdef = CurrentDb.QueryDefs("FormQuery")
    qdef.SQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER
    Forms!frm_docs_lookup_list.subfrm_docs_lookup_list.Requery
End Sub
 
 
Public Function sqlWHERE() As String
    Dim frm As Form
    Dim strTemp As String
 
    Set frm = Forms!frm_docs_lookup_list
    strTemp = ""
 
    If ItemIsNull(frm!equip_sr) = False Then
        strTemp = "(docs.title Like '*" & Trim(frm!equip_sr) & "*' OR docs.equip Like '*" & Trim(frm!equip_sr) & "*')"
    End If
 
    If ItemIsNull(frm!po_sr) = False Then
        If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
        strTemp = strTemp & "(docs.[PO Number] Like '*" & Trim(frm!po_sr) & "*' OR docs.path Like '*" & Trim(frm!po_sr) & "*' OR docs.notes Like '*" & Trim(frm!po_sr) & "*')"
    End If
 
    If ItemIsNull(frm!vend_sr) = False Then
        If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
        strTemp = strTemp & "(docs.vendor Like '*" & Trim(frm!vend_sr) & "*' OR docs.manufacturer Like '*" & Trim(frm!vend_sr) & "*')"
    End If
 
    If Len(strTemp) > 0 Then
        sqlWHERE = " WHERE " & strTemp
    Else
        sqlWHERE = ""
    End If
 
End Function
 
 
Public Function ItemIsNull(inputItem As Variant) As Boolean
    If IsNull(inputItem) = True Then
        ItemIsNull = True
        Exit Function
    End If
 
    If IsEmpty(inputItem) = True Then
        ItemIsNull = True
        Exit Function
    End If
 
    If Trim(inputItem) = "" Then
        ItemIsNull = True
        Exit Function
    End If
 
    ItemIsNull = False
End Function
 

From there you need to write a query and save it with the name "FormQuery". it doesn't matter what sql you put in it.

Change the recordsource of your subform to "FormQuery".

You then need to call the sub "ReWriteQueryDef" from somewhere. You can either put it in the 'Afterupdate' event of each of your lookup boxes or you can add a button to your main form that the user pushes.

You can test the syntax of the WHERE statement in the VBA editor as follows:
Open your form. Type some stuff in the boxes. in the VBA editor immediate pane, type: ?sqlWHERE
Press enter

[mindbenderz]

when I try the ?sqlWHERE

it says 'ambiguous name detected'.

[dsatino]

usually that means you have two functions with the same name...did you paste it twice?

From the DBA Editor click 'debug', then compile. It should call out ambiguities.

[mindbenderz]

In these lines, Im guessing I need to change the table names to the correct names? I just noticed that

Public Const sqlSELECT As String = "SELECT docs.root_id, cont.cont_type, docs.[Content Type], rigs.location, docs.[Assigned Location], docs.Title, docs.[Revision/Version Date], docs.[Asset Number], docs.Vendor, docs.Vend_ID, docs.Manufacturer, docs.Manuf_ID, docs.Model, docs.[Serial Number], docs.[PO Number], docs.[Pressure Rating], docs.Revision, docs.Path, asset.asset_subtype, docs.[Asset Type], docs.[PART NO], docs.[W/O & J/O NO], docs.modules, docs.RigID_Share, docs.[PKD Document Number], docs.qty, docs.Size, docs.[Destination Path], docs.XMIT, docs.DocID, docs.recvd_xmit, docs.draw_no, docs.[%_comp], docs.disc_type, docs.equip, docs.pkd_xmit, docs.tech_type, docs.[Received Date], docs.[End of Life Date], docs.[Supersede Date], docs.Version, docs.Ivara_Path, docs.[RO Number], docs.proj_name, docs.proj_phase, docs.issued_code, docs.approval_code, docs.owned, docs.[SO NO], docs.notes, docs.netpath, docs.user_add, docs.user_comp, docs.user_edit, docs.user_edit_date, docs.doc_filter, docs.date_added" 
Public Const sqlFROM As String = " FROM tbl_rigs rigs RIGHT JOIN (tbl_asset_type asset RIGHT JOIN (tbl_content_type cont RIGHT JOIN tbl_documents docs ON cont.ID = docs.[Content Type]) ON asset.ID = docs.[Asset Type]) ON rigs.ID = docs.[Assigned Location]" 
Public Const sqlORDER As String = " ORDER BY docs.Path"

[dsatino]

Usually. But since you supplied the SQL I used the table names you had in there. I did 'alias' all of them though so as to cut down on the lenghth of the SQL.

[mindbenderz]

the sql in my posts showed the tables full names. at least thats what I can see (i.e. tbl_documents, tbl_content_types, etc). I guess there is a character limited on the vba lines. does it allow carriage returns in the code?

[dsatino]

Yes, the full names were in your post, but I changed them to an alias. It will still work.

For example:

SELECT yt.AnyField1, yt.AnyField2 
From YourTable yt
 

is the same as

SELECT YourTable.AnyField1,YourTable.AnyField2 
From YourTable 
 

which is what i did to the SQL i gave you.

I wrote that in the VBA editor, so if there is a character limitation per line, it doesn't affect this. It's poor practice to give it to you like that, but it doesn't affect the functionality in any way.

Any yes, the underscore "_" character is what the VBA editor will recognize for that purpose.

[mindbenderz]

ok, nm that part. I got the module to work with the sqlWHERE fine. when, on the form, I add =ReWriteQueryDef() to AfterUpdate for the 3 fields, it says "The expresion you entered has a function name that Database can't find". Did I call it wrong in the event lines?

[dsatino]

That usually means that the function is in the form module rather than a public module. Just cut the code out of the form, create a new module, and paste it there instead.

[mindbenderz]

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

[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
=ReWriteQueryDef
=ReWriteQueryDef()
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 'ReWriteQueryDef'. You need to change that. Pick anything.

[mindbenderz]

I just renamed it to "mod_ReWriteQueryDef". 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_ReWriteQueryDef' 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 ;-)