MS Access database support - ComboBox using SQL code

Author

ComboBox using SQL code

Stu

2005-08-29, 9:24 am

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

2005-08-29, 1:25 pm

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.& #91;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
[color=darkred]

Bob Quintal

2005-08-29, 8:26 pm

"Stu" <kardiansr@vcu.edu> wrote in
news:1125319282.601610.232680@f14g2000cwb.googlegroups.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.

Sponsored Links