Combo Box / Query

G

Ghagg

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
 
W

Wayne-I-M

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)
 
G

Ghagg

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






Wayne-I-M said:
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)


--
Wayne
Manchester, England.



Ghagg said:
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
Click to expand...
Click to expand...
 
G

Ghagg

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


Wayne-I-M said:
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)


--
Wayne
Manchester, England.



Ghagg said:
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
Click to expand...
Click to expand...
 
G

Ghagg

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








Wayne-I-M said:
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)


--
Wayne
Manchester, England.



Ghagg said:
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
Click to expand...
Click to expand...
 
W

Wayne-I-M

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.

--
Wayne
Manchester, England.



Ghagg said:
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








Wayne-I-M said:
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)


--
Wayne
Manchester, England.



Ghagg said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Ghagg

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)






Wayne-I-M said:
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.

--
Wayne
Manchester, England.



Ghagg said:
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








Wayne-I-M said:
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)


--
Wayne
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
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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron2006

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Select List Not Working 1
passing a parameter based on multiple selections 7
filtering a form based on the values of a subform 0
Messed Up QueryDef 1
Syntax re MultiSelect ListBox 0
How to Build Two Queries From One List Box 0
How to Flip-Flop Two Queries Built From Only One List box 6
How to get a List Box with nothing Chosen to be All Chosen 2

Top