ComboBox using SQL code

[Stu]

Hi,
I've been working on trying to use a combo box to filter my records for
a while now, and can't get it to work. Right now, I have SQL code
written into IfThen statements on the afterupdate for the combobox. I
tried using the docmd.runSQL but from looking at previous posts, it
seems that I need to use DoCmd.OpenQuery instead since I do not have an
action SQL statement.
The short term problem is that when the user selects from the combobox,
Im getting a "type mis-match error." The longer term problem is that
once the records are filtered, I need to use the procedure number of
the filtered record to filter another set of documents(will worry about
this problem later). It might just something simple with my code, but
I'm not sure. Any help would be VERY much appreciated.

Some details/questions:
-combobox (combo1): unbound
-Does the filtered records need to be in a subform, or can it be in the
same form as the combobox?
-the combo box has two fields, only showing the second one that has the
text in it. The first hidden column is a [TypeID] that I was trying to
use in the code.

code:
************************
Private Sub combo1_AfterUpdate()
Dim ComboSortSQL As String
Dim dbs As Database
Dim qdf As QueryDefs

Set dbs = CurrentDb()
If combo1.Value = "1" Then 'Standard Procedures
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.[TypeID] = 1;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 2 Then 'Policy
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 2;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 3 Then 'Process Description
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 3;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 4 Then 'Program Description
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 4;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 5 Then 'Training Program
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 5;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 6 Then 'Qualification
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 6;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 7 Then 'Standard / Specification
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 7;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
ElseIf combo1.Value = 8 Then 'Guidance & Reference Documents
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID.Value = 8;"
Set qdf = dbs.CreateQueryDef("ComboSortSQL", ComboSortSQL)
DoCmd.OpenQuery qdf
DoCmd.DeleteObject acQuery, ComboSortSQL
End If

dbs.Close
Set qdf = Nothing
Set dbs = Nothing

End Sub
**********************

[Howard Brody]

What are you trying to do? If it's just display then filter records
from a table, add the appropaiate controls to your form and then add a
combo for filtering the TypeID. In the AfterUpdate event code use:

If IsNull(Me.[cboFilterType])=True Then Exit Sub
' otherwise ...
Dim i As Integer
Dim strFilter As String
i = Me.[cboFilterType]
strFilter = "[TypeID]=" & i
DoCmd.ApplyFilter , strFilter

If you want to count the number of records meeting the filter criteria,
include a DCount in your code:

Dim intRecs As Integer
(or use a Long if you have more than 32,000 recs)
intRecs = DCount("[TypeID]", "tbl_Procedures", "[TypeID]=" & i)

If that isn't what you need and you can still eliminate all the
redundant code in your If...Then...ElseIf:

Dim i As Integer
i = Me.[combo1]
ComboSortSQL = "SELECT * FROM tbl_Procedures WHERE
tbl_Procedures.TypeID=" & i

Hope this helps!

Howard Brody

> >

[Bob Quintal]

"Stu" <ka*******@vcu.edu> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Hi,
I've been working on trying to use a combo box to filter my
records for a while now, and can't get it to work. Right now,
I have SQL code written into IfThen statements on the
afterupdate for the combobox. I tried using the docmd.runSQL
but from looking at previous posts, it seems that I need to
use DoCmd.OpenQuery instead since I do not have an action SQL
statement. The short term problem is that when the user
selects from the combobox, Im getting a "type mis-match
error." The longer term problem is that once the records are
filtered, I need to use the procedure number of the filtered
record to filter another set of documents(will worry about
this problem later). It might just something simple with my
code, but I'm not sure. Any help would be VERY much
appreciated.

Some details/questions:
-combobox (combo1): unbound
-Does the filtered records need to be in a subform, or can it
be in the same form as the combobox?
-the combo box has two fields, only showing the second one
that has the text in it. The first hidden column is a
[TypeID] that I was trying to use in the code.

To filter a form,you do not need to alter the underlying query,
which contains all possible records. You just set the
form.filter property to the text of the where clause, and set
the form.filterOn property to true.

So your code becomes much simpler,

Private Sub combo1_AfterUpdate()
If combo1.Value = "1" Then 'Standard Procedures
ComboSortSQL = "tbl_Procedures.[TypeID] = 1"
me.filter = combofltrSQL
me.filterOn= true
elseif combo1.Value = 2 Then
ComboSortSQL = "tbl_Procedures.[TypeID] = 2"

etc...

Now if you want to order the records by a different column,
you would use the same technique and the form's orderby
property. You do not need an orderon.

If you want to return different columns, you would need to
generate the SQL and simply change the form's .recordsource
property

If combo1.Value = "1" Then 'Standard Procedures
me.recordsource = "Select * from tbl_Procedures"
elseif combo1.Value = 2 Then
me.recordsource = "Select * from tbl_Procedures
--
Bob Quintal

PA is y I've altered my email address.