469,356 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Syntax used within Criteria of Query Builder to narrow options

52
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
Dec 14 '06 #1
33 7283
NeoPa
32,185 Expert Mod 16PB
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.
Dec 15 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 15 '06 #3
Birky
52
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.
Dec 15 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 16 '06 #5
Birky
52
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.
Dec 16 '06 #6
NeoPa
32,185 Expert Mod 16PB
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?
Dec 16 '06 #7
Birky
52
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
Dec 18 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT cities FROM city where REGION = [Forms]![FormName]![Combo1Name];
In the after update event of the first combo. Put

Expand|Select|Wrap|Line Numbers
  1. Me.Combo2Name.Requery
Mary
Dec 19 '06 #9
Birky
52
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
Dec 20 '06 #10
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #11
Birky
52
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
Jan 11 '07 #12
NeoPa
32,185 Expert Mod 16PB
I may need to look again tomorrow.
Just posting to mark the thread for another look then.
Jan 11 '07 #13
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
Jan 11 '07 #14
NeoPa
32,185 Expert Mod 16PB
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.
Jan 11 '07 #15
NeoPa
32,185 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Instance_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     strSQL = IIf(Me!Instance > "", _
  5.                  "SELECT [Schema] " & _
  6.                  "FROM [Ins_Sch_Join] " & _
  7.                  "WHERE ([Instance]='" & Me!Instance & "')", _
  8.                  "")
  9.     Me!Schema.RowSource = strSQL
  10. End Sub
Jan 11 '07 #16
Birky
52
That worked perfect, I am truly in your debt.

Thanks to both you and Mary!!!

Birky
Jan 11 '07 #17
NeoPa
32,185 Expert Mod 16PB
No problems - pleased to help :)
Jan 11 '07 #18
Birky
52
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
Jan 18 '07 #19
NeoPa
32,185 Expert Mod 16PB
Try this variation of the earlier Event Procedure.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Instance_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     strSQL = IIf(Me!Instance > "", _
  5.                  "SELECT [Schema] " & _
  6.                  "FROM [Ins_Sch_Join] " & _
  7.                  "WHERE ([Instance]='" & Me!Instance & "')", _
  8.                  "")
  9.     With Me!Schema
  10.         .RowSource = strSQL
  11.         .Enabled = (strSQL > "")
  12.     End With
  13. End Sub
The design of Schema should set it to Enabled = False so that it starts in a disabled state.
Jan 18 '07 #20
Birky
52
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
Jan 19 '07 #21
NeoPa
32,185 Expert Mod 16PB
I don't think so I'm afraid.
Unless I misunderstand what you're saying that would be a surprise to me.
Jan 19 '07 #22
Birky
52
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:

Expand|Select|Wrap|Line Numbers
  1.     With Me!Schema
  2.         .RowSource = strSQL
  3.         .Enabled = (strSQL > "")
  4.     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.

Expand|Select|Wrap|Line Numbers
  1. Dim intSQL As Integer
  2.     intSQL = IIf(Me!Project_Name > "", _
  3.                 "SELECT [COUNT(*)] " & _
  4.                 "FROM [Elements] " & _
  5.                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
  6.                 "")
Again any help would be appreciated..

Birky
Jan 19 '07 #23
NeoPa
32,185 Expert Mod 16PB
I do have a question on your suggested code:
Expand|Select|Wrap|Line Numbers
  1.     With Me!Schema
  2.         .RowSource = strSQL
  3.         .Enabled = (strSQL > "")
  4.     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?
Jan 19 '07 #24
NeoPa
32,185 Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim intSQL As Integer
  2.     intSQL = IIf(Me!Project_Name > "", _
  3.                 "SELECT [COUNT(*)] " & _
  4.                 "FROM [Elements] " & _
  5.                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
  6.                 "")
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.
Jan 19 '07 #25
Birky
52
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Project_Name_AfterUpdate()
  2. Dim strSQL As String
  3.  
  4.     Me!Element_Name.RowSource = ""
  5.  
  6.     strSQL = IIf(Me!Project_Name > "", _
  7.                  "SELECT [Element_Name] " & _
  8.                  "FROM [Elements] " & _
  9.                  "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
  10.                  "")
  11.     With Me!Element_Name
  12.         .RowSource = strSQL
  13.         .Enabled = (strSQL > "")
  14.     End With
  15.  
  16. '    Me!Element_Name.RowSource = strSQL
  17.  
  18. End Sub
Jan 20 '07 #26
NeoPa
32,185 Expert Mod 16PB
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).
Jan 20 '07 #27
Birky
52
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
Jan 21 '07 #28
NeoPa
32,185 Expert Mod 16PB
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).
Jan 21 '07 #29
NeoPa
32,185 Expert Mod 16PB
This is a modified version of the code in post #20, but it tests for the Results existing rather than the Source existing.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Instance_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     strSQL = IIf(Me!Instance > "", _
  5.                  "SELECT [Schema] " & _
  6.                  "FROM [Ins_Sch_Join] " & _
  7.                  "WHERE ([Instance]='" & Me!Instance & "')", _
  8.                  "")
  9.     With Me!Schema
  10.         .RowSource = strSQL
  11.         .Enabled = (.ListCount > 0)
  12.     End With
  13. End Sub
Jan 21 '07 #30
Birky
52
That was it; again you have saved the day. I truly appreciate the help (more than you’ll ever know).

Birky
Jan 21 '07 #31
NeoPa
32,185 Expert Mod 16PB
Pleased I could help.
Jan 21 '07 #32
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
Jul 25 '08 #33
NeoPa
32,185 Expert Mod 16PB
No problems - and thanks for popping in to say so :)
Jul 28 '08 #34

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by RivaCom | last post: by
16 posts views Thread by Chuck | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.