Insert Items from Yes/No CheckBox into Query
in [Queries]
|
From: ryguy7272 on 5 Feb 2010 11:33 I am trying to figure out how to dynamically insert results from 20 yes/no checkboxes into a Query. I'm pretty sure it will require VBA and some kind of loop will be required, right; For Each. . .Next Should I add some kind of unbound object frame? Is there a good demo of how to do this online somewhere? I googled around for an example this AM, but didn't come up with anything useful. Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.
From: KARL DEWEY on 5 Feb 2010 12:59 >>dynamically insert results from 20 yes/no checkboxes into a Query. I do not follow you question. Do you want to put different checkbox results in the query on the fly so that one time the results displays 1 through 5 and next time all or some comination? -- Build a little, test a little. "ryguy7272" wrote: > I am trying to figure out how to dynamically insert results from 20 yes/no > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > of loop will be required, right; > For Each. . .Next > > Should I add some kind of unbound object frame? Is there a good demo of how > to do this online somewhere? I googled around for an example this AM, but > didn't come up with anything useful. > > Thanks! > Ryan-- > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 5 Feb 2010 13:20 Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', and 'Southwest', and add those elements to a Query. I have this macro now: Private Sub CallQuery_Click() Dim strClass As String Dim strGroup As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Set db = CurrentDb Set qdf = db.QueryDefs("qrySummary") ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = acObjStateOpen Then DoCmd.Close acQuery, "qrySummary" End If ' Get the values from the combo boxes If IsNull(Me.cboClass.Value) Then strClass = " Like '*' " Else strClass = "='" & Me.cboClass.Value & "' " End If If IsNull(Me.cboGroup.Value) Then strGroup = " Like '*' " Else strGroup = "='" & Me.cboGroup.Value & "' " End If ' Build the SQL string strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class " & vbCrLf & _ "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = tblStocksGroup.Ticker " & vbCrLf & _ "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ "Order by SharePrices.DateTime;" ' Open the Query qdf.SQL = strSQL DoCmd.OpenQuery "qrySummary" Set qdf = Nothing Set db = Nothing End Sub This takes strings from two ComboBoxes (cboClass and cboGroup) and passes the string to a Query. this part works fine. I'm trying to add a capability to choose from 20 CheckBoxes, and add these to the Query. Is that hard to do? I'm sure it's possbile in Access, but I don't know how to do it. I've done it in Excel, but not Access. Any thoughts or suggestions? The names of the controls are pretty intuitive: chkJetBlue, chkContinental, etc. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "KARL DEWEY" wrote: > >>dynamically insert results from 20 yes/no checkboxes into a Query. > I do not follow you question. Do you want to put different checkbox results > in the query on the fly so that one time the results displays 1 through 5 and > next time all or some comination? > > -- > Build a little, test a little. > > > "ryguy7272" wrote: > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > of loop will be required, right; > > For Each. . .Next > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > to do this online somewhere? I googled around for an example this AM, but > > didn't come up with anything useful. > > > > Thanks! > > Ryan-- > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''.
From: KARL DEWEY on 5 Feb 2010 14:04 One way would be to create a table (My_Carrier_Flight) with 2 fields - Carrier - Text and Flight - Yes/No. Create update query to set all Flight to 0 (zero) - Flight_Zero Create query to pull up these 2 fields. Use command button on form that has the check boxes to run a macro with following actions and conditions -- OpenQuery Flight_Zero OpenQuery Carrier_Flight GoToControl Carrier FindRecord "Delta" SetValue Flight = -1 {Condition} chkDelta = -1 GoToControl Carrier FindRecord "JetBlue" SetValue Flight = -1 {Condition} chkJetBlue = -1 GoToControl Carrier FindRecord "Continental" SetValue Flight = -1 {Condition} chkContinental = -1 ..... etc. Then you can use My_Carrier_Flight as criteria in query design view -- FIELD YourAirLineField Flight TABLE YourTableName My_Carrier_Flight CRITERIA My_Carrier_Flight.Carrier -1 -- Build a little, test a little. "ryguy7272" wrote: > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > and 'Southwest', and add those elements to a Query. I have this macro now: > Private Sub CallQuery_Click() > > Dim strClass As String > Dim strGroup As String > > Dim db As DAO.Database > Dim qdf As DAO.QueryDef > Dim strSQL As String > Set db = CurrentDb > Set qdf = db.QueryDefs("qrySummary") > > ' Close the query if it is already open > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > acObjStateOpen Then > DoCmd.Close acQuery, "qrySummary" > End If > > ' Get the values from the combo boxes > If IsNull(Me.cboClass.Value) Then > strClass = " Like '*' " > Else > strClass = "='" & Me.cboClass.Value & "' " > End If > > If IsNull(Me.cboGroup.Value) Then > strGroup = " Like '*' " > Else > strGroup = "='" & Me.cboGroup.Value & "' " > End If > > ' Build the SQL string > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > tblStocksGroup.Class " & vbCrLf & _ > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > tblStocksGroup.Ticker " & vbCrLf & _ > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > "Order by SharePrices.DateTime;" > > > ' Open the Query > qdf.SQL = strSQL > DoCmd.OpenQuery "qrySummary" > Set qdf = Nothing > Set db = Nothing > End Sub > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > the string to a Query. this part works fine. I'm trying to add a capability > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > done it in Excel, but not Access. Any thoughts or suggestions? > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > etc. > > Thanks, > Ryan-- > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "KARL DEWEY" wrote: > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > I do not follow you question. Do you want to put different checkbox results > > in the query on the fly so that one time the results displays 1 through 5 and > > next time all or some comination? > > > > -- > > Build a little, test a little. > > > > > > "ryguy7272" wrote: > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > of loop will be required, right; > > > For Each. . .Next > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > to do this online somewhere? I googled around for an example this AM, but > > > didn't come up with anything useful. > > > > > > Thanks! > > > Ryan-- > > > -- > > > Ryan--- > > > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 5 Feb 2010 14:11 Ok; thanks! That kind of makes sense, but may be a bit beyond me. I think the SQL should be something like this: SELECT SharePrices.DateTime, SharePrices.StockSymbol, SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class, tblStocksGroup.HDVest50k FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = tblStocksGroup.Ticker WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=0,True,False))) ORDER BY SharePrices.DateTime; With this being operative: AND ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False)) However, I can't seem to get that to work. When I check the box, I get no values returned, and I would expect to see five records, actually. -1 = yes, right. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "KARL DEWEY" wrote: > One way would be to create a table (My_Carrier_Flight) with 2 fields - > Carrier - Text and Flight - Yes/No. > Create update query to set all Flight to 0 (zero) - Flight_Zero > Create query to pull up these 2 fields. > > Use command button on form that has the check boxes to run a macro with > following actions and conditions -- > OpenQuery Flight_Zero > OpenQuery Carrier_Flight > GoToControl Carrier > FindRecord "Delta" > SetValue Flight = -1 {Condition} chkDelta = -1 > GoToControl Carrier > FindRecord "JetBlue" > SetValue Flight = -1 {Condition} chkJetBlue = -1 > GoToControl Carrier > FindRecord "Continental" > SetValue Flight = -1 {Condition} chkContinental = -1 > .... etc. > > Then you can use My_Carrier_Flight as criteria in query design view -- > FIELD YourAirLineField Flight > TABLE YourTableName My_Carrier_Flight > CRITERIA My_Carrier_Flight.Carrier -1 > > -- > Build a little, test a little. > > > "ryguy7272" wrote: > > > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > > and 'Southwest', and add those elements to a Query. I have this macro now: > > Private Sub CallQuery_Click() > > > > Dim strClass As String > > Dim strGroup As String > > > > Dim db As DAO.Database > > Dim qdf As DAO.QueryDef > > Dim strSQL As String > > Set db = CurrentDb > > Set qdf = db.QueryDefs("qrySummary") > > > > ' Close the query if it is already open > > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > > acObjStateOpen Then > > DoCmd.Close acQuery, "qrySummary" > > End If > > > > ' Get the values from the combo boxes > > If IsNull(Me.cboClass.Value) Then > > strClass = " Like '*' " > > Else > > strClass = "='" & Me.cboClass.Value & "' " > > End If > > > > If IsNull(Me.cboGroup.Value) Then > > strGroup = " Like '*' " > > Else > > strGroup = "='" & Me.cboGroup.Value & "' " > > End If > > > > ' Build the SQL string > > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > tblStocksGroup.Class " & vbCrLf & _ > > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > tblStocksGroup.Ticker " & vbCrLf & _ > > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > > "Order by SharePrices.DateTime;" > > > > > > ' Open the Query > > qdf.SQL = strSQL > > DoCmd.OpenQuery "qrySummary" > > Set qdf = Nothing > > Set db = Nothing > > End Sub > > > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > the string to a Query. this part works fine. I'm trying to add a capability > > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > > done it in Excel, but not Access. Any thoughts or suggestions? > > > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > > etc. > > > > Thanks, > > Ryan-- > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "KARL DEWEY" wrote: > > > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > > I do not follow you question. Do you want to put different checkbox results > > > in the query on the fly so that one time the results displays 1 through 5 and > > > next time all or some comination? > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "ryguy7272" wrote: > > > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > > of loop will be required, right; > > > > For Each. . .Next > > > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > > to do this online somewhere? I googled around for an example this AM, but > > > > didn't come up with anything useful. > > > > > > > > Thanks! > > > > Ryan-- > > > > -- > > > > Ryan--- > > > > If this information was helpful, please indicate this by clicking ''Yes''.
|
Next
|
Last
Pages: 1 2 Prev: Delete Query Next: I need to add data to a table from another table how do I acheive |