Syntax used within Criteria of Query Builder to narrow options

[Birky]

Can you please help me understand how to build Criteria syntax within the Query Builder?

If you have a field that is for a region (lets say for Midwest) and you have another field which breaks it down into a smaller geographical region like City (lets say Denver) how can I build the Criteria within the Query Builder to key off a previous select field to narrow down the location?

If
region Midwest is select within region field
THEN
only show cities within that region.

I currently have a Many to Many flow in which I am using a join table to tie them together. I just need to know the syntax for the Criteria on how to use a value from a previous selected field to narrow down the options in the next field. I am using combo boxes to drop down the appropriate options (well hopefully show the appropriate options).

Any help would be greatly appreciated.

Thanks
Mike

[NeoPa]

I don't understand how you can have a combobox available when you are designing a query. That is a control on a form.
It is important that you're clear as to exactly what you're looking for.
I'm fairly confident we know the answer to your question. It's just a matter now of expressing the question clearly and in enough detail.

[MMcCarthy]

Can you please help me understand how to build Criteria syntax within the Query Builder?

If you have a field that is for a region (lets say for Midwest) and you have another field which breaks it down into a smaller geographical region like City (lets say Denver) how can I build the Criteria within the Query Builder to key off a previous select field to narrow down the location?

If
region Midwest is select within region field
THEN
only show cities within that region.

I currently have a Many to Many flow in which I am using a join table to tie them together. I just need to know the syntax for the Criteria on how to use a value from a previous selected field to narrow down the options in the next field. I am using combo boxes to drop down the appropriate options (well hopefully show the appropriate options).

Any help would be greatly appreciated.

Thanks
Mike

OK Mike

The Lookup lists from the table are not available in the query builder in the way you think. However, you can use criteria to restrict the data. If you give us the table name and field names and tell us what yuo want to return in the query we will help you work it out.

Mary

[Birky]

OK Mike

The Lookup lists from the table are not available in the query builder in the way you think. However, you can use criteria to restrict the data. If you give us the table name and field names and tell us what yuo want to return in the query we will help you work it out.

Mary

Here is what I have:

Table name = Instance
field names:
Instance
Purpose
Designation
Notes


Table name = Schema
Field names:
Schema
Purpose
Notes


Which are tied together with a junction table named = Join_Inst_Schem
Field names:
Instance
Schema

Since I have a many to many relationship with these two tables I need the join table to do some linking for me. I plan to create a new table to show events for when new components are loaded within a schema. therefore I plan to have another table named Event_Log, with the appropriate field names from the above table within.

If I have an Instance, and Schema combo box within the form how can I get the Schema box to only show Schema's in which I have linked together with a particular Instance within the Join_Inst_Schem table?

If I have Instance "Midwest" tied to only two or these Schemas within the join table and I select "Midwest" within the Instance dropdown I would like the Schema drop down to only show the values tied to that Instance.

I hope that makes sense, being new to this I'm not sure if I'm using the right terminology or not to get my point across.

Again, any help would be greatly appreciated.

[MMcCarthy]

Here is what I have:

Table name = Instance
field names:
Instance
Purpose
Designation
Notes


Table name = Schema
Field names:
Schema
Purpose
Notes


Which are tied together with a junction table named = Join_Inst_Schem
Field names:
Instance
Schema

Since I have a many to many relationship with these two tables I need the join table to do some linking for me. I plan to create a new table to show events for when new components are loaded within a schema. therefore I plan to have another table named Event_Log, with the appropriate field names from the above table within.

If I have an Instance, and Schema combo box within the form how can I get the Schema box to only show Schema's in which I have linked together with a particular Instance within the Join_Inst_Schem table?

If I have Instance "Midwest" tied to only two or these Schemas within the join table and I select "Midwest" within the Instance dropdown I would like the Schema drop down to only show the values tied to that Instance.

I hope that makes sense, being new to this I'm not sure if I'm using the right terminology or not to get my point across.

Again, any help would be greatly appreciated.

OK Birky

I'm not really sure what you are looking for. One suggestion I have is to have the combo list based on a query to return the values to the drop down list you require. Then have a subform in data sheet format based on all the records and set a filter based on the selected value in the combo box to restrict the records shown.

If this is the kind of thing you are looking for we can go into more detail.

Mary

[Birky]

OK Birky

I'm not really sure what you are looking for. One suggestion I have is to have the combo list based on a query to return the values to the drop down list you require. Then have a subform in data sheet format based on all the records and set a filter based on the selected value in the combo box to restrict the records shown.

If this is the kind of thing you are looking for we can go into more detail.

Mary

I’m just trying to narrow down the options for the user depending on what they had selected within a previous drop down.

If they select “Midwest” then I’d like to narrow down the options for the next combo field to whatever “Cities” fall within the Midwest region.

If no region is selected then they get all the Cities contained within the table, but if they select a region I need to narrow the cities down those that fall within.

[NeoPa]

In that case Birky, you need to create a query (or some SQL) to set the first dropdown to match all the regions in your main table.
The second needs to have some SQL which may be updated by code in the AfterUpdate of the first (or alternatively be generically set up to refer to the other dropdown - can't use Me. here though) one.
The subform needs to have a query or SQL set up to filter by the second dropdown if set, otherwise the first if set, otherwise nothing.
Does this help any?

[Birky]

Understood, that brings us back around to the question, how do I construct the SQL statement for the second dropdown to key off whatever data was selected in the first? How can I, or can you, pass a variable from the first selection into the where clause of the second drop down??

select cities from city where region = <??????>;

I’m assuming the <??????> can be done but I do not know the syntax.

Appreciate you help..
Mike

[MMcCarthy]

Understood, that brings us back around to the question, how do I construct the SQL statement for the second dropdown to key off whatever data was selected in the first? How can I, or can you, pass a variable from the first selection into the where clause of the second drop down??

select cities from city where region = <??????>;

I’m assuming the <??????> can be done but I do not know the syntax.

Appreciate you help..
Mike

Hi Mike

In the second combo

SELECT cities FROM city where REGION = [Forms]![FormName]![Combo1Name];

In the after update event of the first combo. Put

Me.Combo2Name.Requery

Mary

[Birky]

Mary,

I knew I was missing the boat on this one.. Thanks SOOOOO much for the help, it is truly appreciated. (if you were here I’d kiss ya). I have been working on this one for a long time and you were able to clear it up in seconds..

Thanks Again and have a Merry Christmas.

Birky

[MMcCarthy]

Mary,

I knew I was missing the boat on this one.. Thanks SOOOOO much for the help, it is truly appreciated. (if you were here I’d kiss ya). I have been working on this one for a long time and you were able to clear it up in seconds..

Thanks Again and have a Merry Christmas.

Birky

You're welcome Birky and Merry Christmas to you too.

Mary

[Birky]

Still working on narrowing down the options for my form, any help would be greatly appreciated.

I have a form named “Frm_Event_log” where I have two combo boxes name “Instance” and “Schema”. I have three tables, one for “Instances” one for “Schema” and the other to join the two together which I have called “Ins_Sch_Join”. (I was forced to use the join for there is a many to many relationship between my instances and schemas.) I am pulling data for the “Instance” combo box from the “Instances” table and I now need to populate the “Schema” combo box with data from the “Ins_Sch_Join” table using the value they had selected within the “Instance” combo box. I am just trying to narrow down the options for the user. If they select an instance that has only six schemas associated to it within the “Ins_Sch_Join” table, I want them only to be able to select one of those six within the “Schema” combo box.

Mary has tried to help me out as you can see within previous threads but I am still having issues with the select and where I need to put it. I believe the select should look like this but I believe I may have the syntax wrong (not to mention I don’t know where to put it).

SELECT Ins_Sch_Join.Schema
FROM Ins_Sch_Join
WHERE Ins_Sch_Join.Instance = Forms!Frm_Event_log!Instance;


I have tried to work through this but I am having no luck. Any help would be greatly appreciated.

Note:
Instances Table contains
Instance
Purpose
Server
Note


Schema table contains
Schema
Purpose
Note

Ins_Sch_Join table contains only
Instance
Schema

[NeoPa]

I may need to look again tomorrow.
Just posting to mark the thread for another look then.

[cybervegan]

Hiya, I have a related question.

First off, thanks for the tip - worked for me initially!

However, I tried using it on a an AutoForm generated form, which contains a subform, and it no longer works!

When I try to enter the second (i.e. dynamic) combo-box, I now get prompted for a parameter to the query. It seems that the query is unable to access the controlling widget with the Form!DbaseName!Control syntax.

Do subforms change the structure of the object tree?

Another question I've just thought of - is there a way to access the current record (i.e. at the cursor) in another database from within the query criteria? Could I use something like Table!DbaseName!Field_name ?

thanks in advance,
-cybervegan

[NeoPa]

CyberVegan,
You need to post a new question in a new thread (topic).
Hi-jacking someone else's thread is not considered good etiquette in a forum, as it will mean that the original questioners's question is no longer the focus of the thread. You may certainly link to another thread if you feel the information is relevant to your question.

If anyone feels they have an answer for this question please do NOT post it here. Only reply in the thread created for this purpose.

MODERATOR.

[NeoPa]

Still working on narrowing down the options for my form, any help would be greatly appreciated.

I have a form named “Frm_Event_log” where I have two combo boxes name “Instance” and “Schema”. I have three tables, one for “Instances” one for “Schema” and the other to join the two together which I have called “Ins_Sch_Join”. (I was forced to use the join for there is a many to many relationship between my instances and schemas.) I am pulling data for the “Instance” combo box from the “Instances” table and I now need to populate the “Schema” combo box with data from the “Ins_Sch_Join” table using the value they had selected within the “Instance” combo box. I am just trying to narrow down the options for the user. If they select an instance that has only six schemas associated to it within the “Ins_Sch_Join” table, I want them only to be able to select one of those six within the “Schema” combo box.

Mary has tried to help me out as you can see within previous threads but I am still having issues with the select and where I need to put it. I believe the select should look like this but I believe I may have the syntax wrong (not to mention I don’t know where to put it).

SELECT Ins_Sch_Join.Schema
FROM Ins_Sch_Join
WHERE Ins_Sch_Join.Instance = Forms!Frm_Event_log!Instance;


I have tried to work through this but I am having no luck. Any help would be greatly appreciated.

Note:
Instances Table contains
Instance
Purpose
Server
Note


Schema table contains
Schema
Purpose
Note

Ins_Sch_Join table contains only
Instance
Schema

Mike,
Your SQL is fine and would work, but this is how I'd do it :

Private Sub Instance_AfterUpdate()
    Dim strSQL As String
 
    strSQL = IIf(Me!Instance > "", _
                 "SELECT [Schema] " & _
                 "FROM [Ins_Sch_Join] " & _
                 "WHERE ([Instance]='" & Me!Instance & "')", _
                 "")
    Me!Schema.RowSource = strSQL
End Sub

[Birky]

That worked perfect, I am truly in your debt.

Thanks to both you and Mary!!!

Birky

[NeoPa]

No problems - pleased to help :)

[Birky]

Hello Again,

I’m hoping I can expand off this thread in hopes that you can help me take this to the next step. What I would like to do is have the Schema combo box disabled until there is data to be presented within. In other words after they select the Instance and the query runs, only enable the Schema field if there are records found. Is there a way to do this validation after the query is ran and if records are returned then enable the schema combo box to allow the user to narrow down the selection. (There are times when an Instance does not yet have a Schema associated to it therefore I’ll have the combo box disabled until there are valid records to choose from).

Thanks
Birky

[NeoPa]

Try this variation of the earlier Event Procedure.

Private Sub Instance_AfterUpdate()
    Dim strSQL As String
 
    strSQL = IIf(Me!Instance > "", _
                 "SELECT [Schema] " & _
                 "FROM [Ins_Sch_Join] " & _
                 "WHERE ([Instance]='" & Me!Instance & "')", _
                 "")
    With Me!Schema
        .RowSource = strSQL
        .Enabled = (strSQL > "")
    End With
End Sub

The design of Schema should set it to Enabled = False so that it starts in a disabled state.

[Birky]

I have not had a chance to run with your suggestion yet but I have noticed that when saving the record to the database it appears that it is saving the record with all the values that have been assigned to the Schema combo box. I just want the one I had selected from the Schema combo box to save; can you help me out with this one?

Thanks much,,
Birky

[NeoPa]

I don't think so I'm afraid.
Unless I misunderstand what you're saying that would be a surprise to me.

[Birky]

You are correct (Thank GOD), I had a bogus report definition therefore it was throwing my output askew. I have verified that it is only saving the appropriate saved entry. I do have a question on your suggested code:

    With Me!Schema
        .RowSource = strSQL
        .Enabled = (strSQL > "")
    End With

Correct me if I’m wrong but this will enable the Schema combo box regardless if the query returns no entries, correct?

Can I use an integer to enable or disable my combo boxes? I believe it is something like the below although I can’t figure out how to use the count(*) functionality within the VBA structure.

Dim intSQL As Integer
    intSQL = IIf(Me!Project_Name > "", _
                "SELECT [COUNT(*)] " & _
                "FROM [Elements] " & _
                "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                "")

Again any help would be appreciated..

Birky

[NeoPa]

I do have a question on your suggested code:

    With Me!Schema
        .RowSource = strSQL
        .Enabled = (strSQL > "")
    End With

Correct me if I’m wrong but this will enable the Schema combo box regardless if the query returns no entries, correct?

Not exactly. This will enable the Schema ComboBox when there is anything in the RowSource of the control. Obviously there cannot be any results if the RowSource is empty, but there can also be none if the RowSource query returns no data.
Is this clearer?

[NeoPa]

Can I use an integer to enable or disable my combo boxes? I believe it is something like the below although I can’t figure out how to use the count(*) functionality within the VBA structure.

Dim intSQL As Integer
    intSQL = IIf(Me!Project_Name > "", _
                "SELECT [COUNT(*)] " & _
                "FROM [Elements] " & _
                "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                "")

Again any help would be appreciated..

Birky

This is wrong in too many ways to cover quickly.
Let me know if you need more work done on it. My original code addresses the question as asked. If that's not right and you need something (even slightly) different then just explain what and we'll help.

[Birky]

It appears that the .RowSource is getting populated with the select not the values we are expecting. Every time I select an Instance the Schema combo box is getting enabled even though there are no associated Schemas. When breaking at the line (.Enabled = (strSQL > “”), the strSQL variable is populated with the select itself therefore always enabling the Schema combo box. It appears the way I have this, the value will never be null therefore it will always enable the combo box.

Can you see what I am doing wrong?
Birky

Private Sub Project_Name_AfterUpdate()
Dim strSQL As String
 
    Me!Element_Name.RowSource = ""
 
    strSQL = IIf(Me!Project_Name > "", _
                 "SELECT [Element_Name] " & _
                 "FROM [Elements] " & _
                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                 "")
    With Me!Element_Name
        .RowSource = strSQL
        .Enabled = (strSQL > "")
    End With
 
'    Me!Element_Name.RowSource = strSQL
 
End Sub

[NeoPa]

You're confusing me now.
You wanted a situation where Element_Name is populated when a valid Project_Name is selected.
So :-
When the form opens it is not populated.
When Project_Name is cleared (No item selected) it is not populated.
When a Project_Name is chosen it is populated.

This is exactly what it does. strSQL is only set to a SELECT string if an item is chosen - otherwise it is set to "" and is disabled.
So, you've got the code right but understanding it wrong (or I misunderstood the question completely).

[Birky]

I’m sorry; I think I may be making this harder than it has to be. I think the missing element here is that although an Instance is selected there may not be an associated Schema to that Instance therefore I am hoping to keep the Schema combo box disabled. Only when there are associated Schemas to the Instance will it then enable the Schema combo box. I hope this helps, for you have been a great help to me and I truly appreciate your patience with a novice like myself.

Example:

Instance “FTEST5” has three Schemas associated to it within the database. When the query is executed upon the selection of “FTEST5” in the Instance combo box it rightfully finds the three associated schemas and enables the Schema combo box.

Instance “FTEST3” does not have any associated schemas currently defined in the database. Therefore when “FTEST3” is selected it should not return any associated schemas and therefore no enable the Schema combo box.

Again thanks for your help..
Mike

[NeoPa]

Aah - There's the difference.
Please wait while I dig up the info on what needs to be tested here (How to determine whether or not a ComboBox or ListBox has any contents).

[NeoPa]

This is a modified version of the code in post #20, but it tests for the Results existing rather than the Source existing.

Private Sub Instance_AfterUpdate()
    Dim strSQL As String
 
    strSQL = IIf(Me!Instance > "", _
                 "SELECT [Schema] " & _
                 "FROM [Ins_Sch_Join] " & _
                 "WHERE ([Instance]='" & Me!Instance & "')", _
                 "")
    With Me!Schema
        .RowSource = strSQL
        .Enabled = (.ListCount > 0)
    End With
End Sub

[Birky]

That was it; again you have saved the day. I truly appreciate the help (more than you’ll ever know).

Birky

[NeoPa]

Pleased I could help.

[wobbies]

I know that this thread ended a long time ago but I just wanted to say thank you so much to everyone who contributed...I have just solved the same problem using your advice and it's been plaguing me for days.

thanks again

[NeoPa]

No problems - and thanks for popping in to say so :)