Help Setting Static Combobox Values to SQL SELECT Query

[The.Daryl.Lu]

Hi, two parts to my problem if someone can help address either one or
both:

1. I want to SELECT everything in the table if it matches the
criteria when the query button is pressed (this is just a
commandbutton on my form). The biggest problem I'm having is that I
can't (don't know how) populate a combobox on static items such as
"True, False, Any." The Any field, would be selected by the user to
find all records where that fields is either True or False. So how
can I set the values in the combo box to those three options? Perhaps
when I start the form (it shows up) that I should use the
combobox1.AddItem "True", etc.? Then where do I write the code for
when the form initiates? Right now, I just have it set up that I go
to Forms in the Access database and double click the corresponding
Form to initiate.

2. The second problem lies in being able to write the code that would
get the text selected from the combobox (I don't want the user to just
type anything; instead, need the user to just select the proper value)
and then based on that, run the SQL query satisfying those
requirements. I understand True = -1, False = 0. It's the pain of
having to have the Any to search for both True and False records for a
particular field that adds so much trouble. So again, combobox with
values "True, False, Any" and being able to implement those
corresponding values into a SQL SELECT statement. The DoCmd.RunSQL in
VB, unfortunately, does not work with SELECT statements (only work
with action queries).

I am wondering if "recordset" will work in VB? I've never used it and
am not sure how it works. How would I "mount" the results of the
recordset into the dataview or similar table-like format?

Thanks a lot (this has hammering me for the last three days!!!)

Daryl

[King Ron]

For 1): Static is easy: set the .ComboBox .RowSourceType to
'ValueList' and the.RowSource to your list of selection values
separated by semicolons (or commas): "True";"False";"Any"

Note that choosing the 'Any' Option, is logically the same as having
NO selection at all...so just skip the selection...

For 2) The easiest way to do what you are trying to do is to build a
SQL 'WHERE' clause as a text string (without the word WHERE) and then
set the .Form's .Filter property to that SQL clause (and also
set .FilterOn true)

The sample code here is clipped from an application that selectively
filters a Form displaying set of course offerings based on date and
time offered and the Subject=>Course=>Section the user want to see.
Each time any of the selection combo boxes is changed, the filter is
rebuilt. In this sample, I use the text string "{ALL}" to indicate any
value is accepted, note that if the ALL value is selected, the filter
builder simply skips building that section of the select. The curly
braces sort the ALL value to the top of the selection list (first item
in the combo box...)

Private Sub usSetDate_OnChange()
SetFilter
End Sub

Private Sub usSetSubject_OnChange()
SetFilter
End Sub

Private Sub usSetCourse_OnChange()
SetFilter
End Sub

Private Sub usSetSection_OnChange()
SetFilter
End Sub

etc...for as many combo box selectors you want...
....
<compiled code>

Public Sub SetFilter()
Dim wkFilter As String
Dim wkDate As String
Dim wkTime As String
Dim wkSubject As String
Dim wkCourse As String
Dim wkSection As String

On Error GoTo ProcErr

Me.Filter = ""
Me.FilterOn = False
Me.Painting = False

If (Len(Me.usSetDate) 0) Then
wkDate = CDate(DatePart("m", Me.usSetDate) & "/" & DatePart("d",
Me.usSetDate) & "/" & DatePart("yyyy", Me.usSetDate))
wkTime = CDate(DatePart("h", Me.usSetDate) & ":" & DatePart("n",
Me.usSetDate) & ":" & DatePart("s", Me.usSetDate) & IIf(Right$
(Me.usSetDate, 1) = "M", Right$(Me.usSetDate, 2), ""))

Select Case (wkTime)
Case "12:00:00 AM"
wkFilter = "([EventDate] = #" & wkDate & "#) AND "
Case Else
wkFilter = "([EventDate] = #" & wkDate & "#) AND " & _
"([EventStartTime] <= #" & wkTime & "#) AND " & _
"([EventEndTime] >= #" & wkTime & "#) AND "
End Select
End If

wkSubject = Me.usSetSubject
wkCourse = Me.usSetCourse
wkSection = Me.usSetSection

If (wkSubject <"{ALL}") Then
If (Len(wkSubject) 0) Then
wkFilter = wkFilter & "([Subject] = '" & wkSubject & "') AND "
Else
wkFilter = wkFilter & "(IsNull([Subject]) = True) AND "
End If
End If

If (wkCourse <"{ALL}") Then
If (Len(wkCourse) 0) Then
wkFilter = wkFilter & "([Course] = '" & wkCourse & "') AND "
Else
wkFilter = wkFilter & "(IsNull([Course]) = True) AND "
End If
End If

If (wkSection <"{ALL}") Then
If (Len(wkSection) 0) Then
wkFilter = wkFilter & "([Section] = '" & wkSection & "') AND "
Else
wkFilter = wkFilter & "(IsNull([Section]) = True) AND "
End If
End If

If (Len(wkFilter) 0) Then
Me.Filter = Mid$(wkFilter, 1, Len(wkFilter) - 5)
Me.FilterOn = True
End If

ProcExit:
Me.Painting = True
Me.Repaint
Exit Sub

ProcErr:
Select Case Err
Case 0:
Resume Next
Case Else
' Display 0, "SetFilter Error: " & Err & " " & Err.Description &
" " & Now()
LogErr "SetFilter", Err, Err.Description
Resume ProcExit
End Select
End Sub

</end compiled code>

Good luck with sorting this out...

Ron, King of Chi

On Jul 18, 10:31 am, "[e-mail]" <[e-mail]>
wrote:

Hi, two parts to my problem if someone can help address either one or
both:

1. I want to SELECT everything in the table if it matches the
criteria when the query button is pressed (this is just a
commandbutton on my form). The biggest problem I'm having is that I
can't (don't know how) populate a combobox on static items such as
"True, False, Any." The Any field, would be selected by the user to
find all records where that fields is either True or False. So how
can I set the values in the combo box to those three options? Perhaps
when I start the form (it shows up) that I should use the
combobox1.AddItem "True", etc.? Then where do I write the code for
when the form initiates? Right now, I just have it set up that I go
to Forms in the Access database and double click the corresponding
Form to initiate.

2. The second problem lies in being able to write the code that would
get the text selected from the combobox (I don't want the user to just
type anything; instead, need the user to just select the proper value)
and then based on that, run the SQL query satisfying those
requirements. I understand True = -1, False = 0. It's the pain of
having to have the Any to search for both True and False records for a
particular field that adds so much trouble. So again, combobox with
values "True, False, Any" and being able to implement those
corresponding values into a SQL SELECT statement. The DoCmd.RunSQL in
VB, unfortunately, does not work with SELECT statements (only work
with action queries).

I am wondering if "recordset" will work in VB? I've never used it and
am not sure how it works. How would I "mount" the results of the
recordset into the dataview or similar table-like format?

Thanks a lot (this has hammering me for the last three days!!!)

Daryl