If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Hello,
My form displays a combo box populated by a SQL SELECT statement. The user will select a row from the combo box. I then want the value in a field of the row selected in the combo box to be used as the selection criteria in a SQL statement. The SQL statement is a nested SELECT and the value selected in the combo box is the selection criteria in the inner select statement. The problem is how to pass the value selected in the combo box to the SQL query. Also, I have the following problem . When I call the SQL query by clicking a button, a window popups asking me to enter the value for the parameter. I don't want this popup to appear. What I want to acheive is that after the user selects a row in the combo box, I want the field value to be passed to the SQL statement's WHERE clause and the SQL should execute and display the rows selected. I tried to use the ON CHANGE event of the Combo box, but this then goes into Microsift Visual basic where I need to code a call to the SQL. I don't know anything about Visual Basic. So, if this is the way to do it, then please give me the details of how to call the SQL from within Visual basic. If there is another way to acheive this, that is also fine. I am new to MS access 2007, so please give me a step by step guidance to do this. Thanks in Advance. |
#2
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
And where would you use/display the results of this select query? You're
using a combo box on your form to select filter criteria; do you then want to show filtered records on that same form or subform, or perhaps run a report? One typical setup is to use an unbound combo box to filter display of records based on value selected in the combo. Use the combo box wizard and select the option to filter records. Another setup is to pass the value from that unbound combo box to your query. In the design grid of your query, put a statement similar to: Like IIf([forms]![frmMyForm]![cmbMyCombo] Is Null,"*",[forms]![frmMyForm]![cmbMyCombo]) in the criteria row of the column you want to filter on. Also remove the parameter statement - this is what causes the input box to display. Your query will now select records based on the value showing in the form's combo box, and display all records, if there is no selection. To run the query directly from your form, you'll need a DoCmd.RunQuery "MyQuery" statement that is put in the AfterUpdate event of your combo box, or better still on the OnClick event of a command button that you add (there's a command button wizard to help you). -Ed "hamsa" u58624@uwe wrote in message news:a4b36ceaabcbb@uwe... Hello, My form displays a combo box populated by a SQL SELECT statement. The user will select a row from the combo box. I then want the value in a field of the row selected in the combo box to be used as the selection criteria in a SQL statement. The SQL statement is a nested SELECT and the value selected in the combo box is the selection criteria in the inner select statement. The problem is how to pass the value selected in the combo box to the SQL query. Also, I have the following problem . When I call the SQL query by clicking a button, a window popups asking me to enter the value for the parameter. I don't want this popup to appear. What I want to acheive is that after the user selects a row in the combo box, I want the field value to be passed to the SQL statement's WHERE clause and the SQL should execute and display the rows selected. I tried to use the ON CHANGE event of the Combo box, but this then goes into Microsift Visual basic where I need to code a call to the SQL. I don't know anything about Visual Basic. So, if this is the way to do it, then please give me the details of how to call the SQL from within Visual basic. If there is another way to acheive this, that is also fine. I am new to MS access 2007, so please give me a step by step guidance to do this. Thanks in Advance. |
#3
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Normally, you would refer to the control in the query. The form must be open
when the query executes. For a combobox that will return the value of the row that is selected and the column that you have designated as the bound column. Forms![Name of Form]![Name of Combobox] That may not work for you depending on how you have nested the queries. If you have used a sub-query in the from clause then this may fail. If you have a saved query that you are referencing in the from clause then this should work with no problem. If this does not work for you, then you need to post the SQL of your queries so we can see what is happening. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County hamsa wrote: Hello, My form displays a combo box populated by a SQL SELECT statement. The user will select a row from the combo box. I then want the value in a field of the row selected in the combo box to be used as the selection criteria in a SQL statement. The SQL statement is a nested SELECT and the value selected in the combo box is the selection criteria in the inner select statement. The problem is how to pass the value selected in the combo box to the SQL query. Also, I have the following problem . When I call the SQL query by clicking a button, a window popups asking me to enter the value for the parameter. I don't want this popup to appear. What I want to acheive is that after the user selects a row in the combo box, I want the field value to be passed to the SQL statement's WHERE clause and the SQL should execute and display the rows selected. I tried to use the ON CHANGE event of the Combo box, but this then goes into Microsift Visual basic where I need to code a call to the SQL. I don't know anything about Visual Basic. So, if this is the way to do it, then please give me the details of how to call the SQL from within Visual basic. If there is another way to acheive this, that is also fine. I am new to MS access 2007, so please give me a step by step guidance to do this. Thanks in Advance. |
#4
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Ed:
That’s not a reliable way of 'optionalizing' a parameter I'm afraid, as any row with a NULL in the column in question won't be returned as NULL LIKE "*" evaluates to NULL, neither TRUE nor FALSE. Testing for the parameter being NULL in a Boolean OR operation is the best way to do it: WHERE ([SomeField] = [Forms]![frmMyForm]![cmbMyCombo] OR [Forms]![frmMyForm]![cmbMyCombo] IS NULL) If the control is Null the second part of the OR operation will evaluate to TRUE regardless of the value, or absence of a value, in the SomeField column. I'm not sure what you mean by "remove the parameter statement - this is what causes the input box to display". Are you saying that a PARAMETERS clause in a query will generate a prompt regardless of whether the parameter is an accessible object or not? If so that's not the case. In fact with certain parameter types, particularly date/time, its prudent (and in a crosstab query essential for any parameters) to always declare them to avoid any inadvertent misinterpretation of the data type, e.g. a date might be interpreted as an arithmetical expression; which would not cause an error, just give the wrong results. The unexpected generation of a prompt is more likely to be the result either of the referenced object not being currently exposed, or a simple misspelling of the object name. Ken Sheridan Stafford, England Ed Robichaud wrote: And where would you use/display the results of this select query? You're using a combo box on your form to select filter criteria; do you then want to show filtered records on that same form or subform, or perhaps run a report? One typical setup is to use an unbound combo box to filter display of records based on value selected in the combo. Use the combo box wizard and select the option to filter records. Another setup is to pass the value from that unbound combo box to your query. In the design grid of your query, put a statement similar to: Like IIf([forms]![frmMyForm]![cmbMyCombo] Is Null,"*",[forms]![frmMyForm]![cmbMyCombo]) in the criteria row of the column you want to filter on. Also remove the parameter statement - this is what causes the input box to display. Your query will now select records based on the value showing in the form's combo box, and display all records, if there is no selection. To run the query directly from your form, you'll need a DoCmd.RunQuery "MyQuery" statement that is put in the AfterUpdate event of your combo box, or better still on the OnClick event of a command button that you add (there's a command button wizard to help you). -Ed Hello, [quoted text clipped - 36 lines] Thanks in Advance. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
As usual, I stand corrected. I meant to write:
Like IIf([forms]![frmMyForm]![cmbMyCombo] Is Null,"*",[forms]![frmMyForm]![cmbMyCombo]) or is Null which should return the intended recordset. And yes, declared parameters are best, and the input box is probably the result of an inaccessable control. Thanks for keeping me sharp(er). "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a4b64ad75050f@uwe... Ed: That's not a reliable way of 'optionalizing' a parameter I'm afraid, as any row with a NULL in the column in question won't be returned as NULL LIKE "*" evaluates to NULL, neither TRUE nor FALSE. Testing for the parameter being NULL in a Boolean OR operation is the best way to do it: WHERE ([SomeField] = [Forms]![frmMyForm]![cmbMyCombo] OR [Forms]![frmMyForm]![cmbMyCombo] IS NULL) If the control is Null the second part of the OR operation will evaluate to TRUE regardless of the value, or absence of a value, in the SomeField column. I'm not sure what you mean by "remove the parameter statement - this is what causes the input box to display". Are you saying that a PARAMETERS clause in a query will generate a prompt regardless of whether the parameter is an accessible object or not? If so that's not the case. In fact with certain parameter types, particularly date/time, its prudent (and in a crosstab query essential for any parameters) to always declare them to avoid any inadvertent misinterpretation of the data type, e.g. a date might be interpreted as an arithmetical expression; which would not cause an error, just give the wrong results. The unexpected generation of a prompt is more likely to be the result either of the referenced object not being currently exposed, or a simple misspelling of the object name. Ken Sheridan Stafford, England Ed Robichaud wrote: And where would you use/display the results of this select query? You're using a combo box on your form to select filter criteria; do you then want to show filtered records on that same form or subform, or perhaps run a report? One typical setup is to use an unbound combo box to filter display of records based on value selected in the combo. Use the combo box wizard and select the option to filter records. Another setup is to pass the value from that unbound combo box to your query. In the design grid of your query, put a statement similar to: Like IIf([forms]![frmMyForm]![cmbMyCombo] Is Null,"*",[forms]![frmMyForm]![cmbMyCombo]) in the criteria row of the column you want to filter on. Also remove the parameter statement - this is what causes the input box to display. Your query will now select records based on the value showing in the form's combo box, and display all records, if there is no selection. To run the query directly from your form, you'll need a DoCmd.RunQuery "MyQuery" statement that is put in the AfterUpdate event of your combo box, or better still on the OnClick event of a command button that you add (there's a command button wizard to help you). -Ed Hello, [quoted text clipped - 36 lines] Thanks in Advance. -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Dear All,
Thank You very much for the reply. I will give the code that I am using below: SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId WHERE (((Index.TopicId) In (SELECT Topics.TopicId FROM Topics WHERE Topics. Topic = [Enter the Dhatu Name (Ex: - bhü)] ))) ORDER BY Sutras.SutraNo; In the Inner SELECT statement, I have the code [Enter the Dhatu Name (Ex: - bhü)] after =. I want to remove this and instead use the value in the column of the row selected in the combo box. How do I do that? I can use either a Combo box event like On Change or I can also have an EXECUTE button which when clicked will pass the value from the combo box to the Inner SELECT statement of the SQl query. My requirement is that I have to avoid the parameter popup window as the user has already selected the row in the combo box. I retained the present code as I coult not figure out how to pass the value from the combo box to the SQL query. After a row is selected in the combo box, I want the value of the column in the row selected passed as the selection criteria to the Inner SELECT. At present, the COmbo box Control source property is blank and the Bound Column property is set to 1. John Spencer wrote: Normally, you would refer to the control in the query. The form must be open when the query executes. For a combobox that will return the value of the row that is selected and the column that you have designated as the bound column. Forms![Name of Form]![Name of Combobox] That may not work for you depending on how you have nested the queries. If you have used a sub-query in the from clause then this may fail. If you have a saved query that you are referencing in the from clause then this should work with no problem. If this does not work for you, then you need to post the SQL of your queries so we can see what is happening. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Hello, [quoted text clipped - 26 lines] Thanks in Advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#7
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Set the combo box up as follows:
Name: cboTopic RowSource: SELECT TopicID, Topic FROM Topics ORDER BY Topic; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. The query will then be: SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId WHERE Index.TopicId = Forms![YourForm]![cboTopic] ORDER BY Sutras.SutraNo; You can then open the query, or better still a form or report based on it, with code in the AfterUpdate event procedure of the combo box or in the Click event procedure of a separate button on the form; e.g. to open a form named frmSutras with the above query as its RowSource you'd something kike the following code: Const MESSAGETEXT = "Please select a topic first." If Not IsNull(Me.[cboTopic]) Then DoCmd.OpenForm "frmSutras" Else MsgBox MESSAGETEXT, vbInformation, "Warning" End If Ken Sheridan Stafford, England hamsa wrote: Dear All, Thank You very much for the reply. I will give the code that I am using below: SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId WHERE (((Index.TopicId) In (SELECT Topics.TopicId FROM Topics WHERE Topics. Topic = [Enter the Dhatu Name (Ex: - bhü)] ))) ORDER BY Sutras.SutraNo; In the Inner SELECT statement, I have the code [Enter the Dhatu Name (Ex: - bhü)] after =. I want to remove this and instead use the value in the column of the row selected in the combo box. How do I do that? I can use either a Combo box event like On Change or I can also have an EXECUTE button which when clicked will pass the value from the combo box to the Inner SELECT statement of the SQl query. My requirement is that I have to avoid the parameter popup window as the user has already selected the row in the combo box. I retained the present code as I coult not figure out how to pass the value from the combo box to the SQL query. After a row is selected in the combo box, I want the value of the column in the row selected passed as the selection criteria to the Inner SELECT. At present, the COmbo box Control source property is blank and the Bound Column property is set to 1. Normally, you would refer to the control in the query. The form must be open when the query executes. For a combobox that will return the value of the row [quoted text clipped - 27 lines] Thanks in Advance. -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Pass field value selected in Combo Box to SQL Query
Hello Ken,
Thank you. After my last post and reading the replies, I got an understanding of how to pass the value from the combo box to a SQL. I used the macro OPENQUERy and called my SQl query and used the code Forms![YourForm]![cboTopic]. It worked perfectly. Now the annoying popup doesn't appear anymore. Thank You very much. All of you were very helpful and I thank each one of you. Regards. KenSheridan wrote: Set the combo box up as follows: Name: cboTopic RowSource: SELECT TopicID, Topic FROM Topics ORDER BY Topic; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. The query will then be: SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId WHERE Index.TopicId = Forms![YourForm]![cboTopic] ORDER BY Sutras.SutraNo; You can then open the query, or better still a form or report based on it, with code in the AfterUpdate event procedure of the combo box or in the Click event procedure of a separate button on the form; e.g. to open a form named frmSutras with the above query as its RowSource you'd something kike the following code: Const MESSAGETEXT = "Please select a topic first." If Not IsNull(Me.[cboTopic]) Then DoCmd.OpenForm "frmSutras" Else MsgBox MESSAGETEXT, vbInformation, "Warning" End If Ken Sheridan Stafford, England Dear All, [quoted text clipped - 30 lines] Thanks in Advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
Thread Tools | |
Display Modes | |
|
|