 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Combo Box / Query
From a previous effort using Cascading List Boxes, i was able to subsequently run a command button for the first listbox which then lists the results in a query selection in datasheet view. See following code. Now i would like to run a similar query that shows the results of a combobox selection in a datasheet view. Thanks for your help Best regards, Ghagg Private Sub cmdClass_Click() Dim Q As QueryDef, db As Database Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![ListClass1] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm If Len(Criteria) = 0 Then Itm = MsgBox("You must select one or more items in the" & _ " list box!", 0, "No Selection Made") Exit Sub End If ' Modify the Query. Set db = CurrentDb() Set Q = db.QueryDefs("qryMultiSelectClass") Q.SQL = "Select * From tblBuncombeWebprcls Where [ClassName] In(" & Criteria & _ ");" Q.Close ' Run the query. DoCmd.OpenQuery "qryMultiSelectClass" End Sub There is a less elegant (but more effective) method Create a query Ensure that the bound field from your 1st combo is in the query Set the criteria as Forms![FormName]![1stComboName] Create the datasheet from the query Works every times (don't forget to requiery AfterUpdate of 1st combo)  SignatureWayne Manchester, England. > From a previous effort using Cascading List Boxes, i was able to > subsequently run a command button for the first listbox which then lists the [quoted text clipped - 63 lines] > > End Sub Thank you Wayne-I-M No doubt you know what's best, Help. This code below is what i've been trying, but this gives me every record in the table and not limited by the the combo Box selection. Private Sub cmdClassName1_Click() Dim strForm As Form Dim strWhere As String With Me.cboClassName1 If Not IsNull(cboClassName1.Value) Then strWhere = Forms![frmGHCascadingCBO]![cboClassName1] = " & cboClassName1.Value" End If Me.cboClassName1.Requery DoCmd.OpenQuery "qrycboClassName1" End With End Sub > There is a less elegant (but more effective) method > [quoted text clipped - 76 lines] >> >> End Sub Wayne-I-M I think what i'm lacking in this procedure is a way to pass a variable from the combo box selection to the query. Could that be correct?? Thanks in advance Best regards, Ghagg > There is a less elegant (but more effective) method > [quoted text clipped - 76 lines] >> >> End Sub I'm trying to pass the variable from the combo box to the query, however, the code stops while passing variable. If you know of any examples that you might recommend for me, i'm somewhat new with access. Thanks for your help Ghagg > There is a less elegant (but more effective) method > [quoted text clipped - 76 lines] >> >> End Sub If I understand correctly what you're trying to do, you have a combo on a main form and you are wanting to use this to set the filter of the linked records on a subform set to datasheet. If this is correct then just base the datasheet on a querry that is filter from the combo - requery OnChange of the combo If this is not what you're trying to do then post back. SignatureWayne Manchester, England. > I'm trying to pass the variable from the combo box to the query, however, > the code stops while passing variable. [quoted text clipped - 86 lines] > >> > >> End Sub Thank you Wayne-I-M for responding OK, Consider this, i have a table with over 100K records, about 50 fields. The class field has 210 items that repeat through out the records in this table. I want to chose this class field as an example, then select one item in the drop-down combo box, and filter all records based on the combo box selection. I also have 3 cascading combo boxes. I hope the combo box works like the list box, As i previously mentioned, i have been able to get the 3 cascading list boxes to work with 3 different queries. The Second list box query selects from the first list box query... I think the problem i'm having is passing the value from the combo box selection to the query. Thanks for your help Best regards, Glenn (ghagg) > If I understand correctly what you're trying to do, you have a combo on a > main form and you are wanting to use this to set the filter of the linked [quoted text clipped - 97 lines] >> >> >> >> End Sub In general I think you are doing the proper things. The only word of warning is that what the combobox/listbox displays is not necessarily what it is bound to and that has a consequence on what you are trying to match against in the query. If the bound column for a combo is (for instance) the ID but it is showing something else, then the data in the table/query that is being used for the cascading combo needs to be the ID also. This will bite you if you are not careful, especially if you use the automatic combobox generation which always wants to include the ID and not show it and bind the combo to that ID. Ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.