Pass field value selected in Combo Box to SQL Query
in [Getting Started]
|
From: hamsa on 8 Mar 2010 07:40 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.
From: Ed Robichaud on 8 Mar 2010 09:16 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(a)uwe> wrote in message news:a4b36ceaabcbb(a)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. >
From: John Spencer on 8 Mar 2010 09:24 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. >
From: KenSheridan via AccessMonster.com on 8 Mar 2010 13:09 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
From: Ed Robichaud on 8 Mar 2010 23:07 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(a)uwe> wrote in message news:a4b64ad75050f(a)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 >
|
Next
|
Last
Pages: 1 2 Prev: Opening a database with a new form Next: Which Version of Office 2007 to Buy? |