Using a Form to Populate a Query

[OxDavis]

I want my users to be able to search the db through a popup form and then generate reports based on their input. Can someone post a bit of sample VBA that demonstrates a form field populating SQL statements? I'm not sure if this is even the correct procedure. Any help is appreciated.

 

[bonekrusher]

This should help you get started.....

http://www.access-programmers.co.uk/forums/showthread.php?t=100246&highlight=search

bones

 

[ejstefl]

Also, check out the "Access and SQL" tutorials parts 1-6 on this site.

 

[OxDavis]

Thats good stuff, I'm certainly going to use that. I have one more question however. The examples he uses allow the user to chose from combo and option boxes, but there isn't an example illustrating how to filter a report by a date range, as in filtering a report on the fly by hire or termination date. Anyone familiar with this process have some sample code?

 

[OxDavis]

Private Sub cmdApplyFilter_Click()
Dim strCity As String
Dim strState As String
Dim strFilter As String
Dim IntMedicaid As Integer

'Setup Option Boxes
Select Case Me!fraMedicaid.Value
    Case 1
    IntMedicaid = -1
    Case 2
    IntMedicaid = 0
    Case 3
    IntMedicaid = 2
End Select

'Define ComboBoxes and Filter

If IsNull(Me!cboCity.Value) Then
    strCity = "Like '*'"
    Else: strCity = "='" & Me!cboCity.Value & "'"
End If



If IsNull(Me!cboState.Value) Then
    strState = "Like '*'"
    Else: strState = "='" & Me!cboState.Value & "'"
End If

strFilter = "[ChildCity] " & strCity & " AND [StateAbb] " & strState & " AND [CurrMedicaid] " & IntMedicaid

'Turn on Filter

With Reports![rptMain]
         .Filter = strFilter
         .FilterOn = True
    End With
    End Sub

I'm having difficulty with the Case Select function as well as constructing the filter. I have a Yes/No checkbox that the Case Select function is supposed to filter. Case 3 is either one, how do I get the wildcard in there as an integer? Also, does my strFilter line at the end look ok? I'm having an error there as well, but it may be related to the Case Select problems I'm having up top. Also, no matter which radio bvutton I select it is not filtering. Any suggestions are appreciated.

 

[ejstefl]

Well, as far as the case select statement goes, it sounds like [CurrMedicaid] is a yes/no field. Try changing from an integer to a string and setting it to "Yes", "No", or "Like *"

Your strFilter looks ok. What is the error message?

 

[OxDavis]

Private Sub cmdApplyFilter_Click()
Dim strCity As String
Dim strState As String
Dim strFilter As String
Dim strMedicaid As String

'Setup Option Boxes
Select Case Me!fraMedicaid.Value
    Case 1
    strMedicaid = "='Yes'"
    Case 2
    strMedicaid = "='No'"
    Case 3
    strMedicaid = "Like '*'"
End Select


'Define ComboBoxes and Filter

If IsNull(Me!cboCity.Value) Then
    strCity = "Like '*'"
    Else: strCity = "='" & Me!cboCity.Value & "'"
End If



If IsNull(Me!cboState.Value) Then
    strState = "Like '*'"
    Else: strState = "='" & Me!cboState.Value & "'"
End If

strFilter = "[ChildCity] " & strCity & " AND [StateAbb] " & strState & " AND [CurrMedicaid] " & strMedicaid

'Turn on Filter

With Reports![rptMain]
         .Filter = strFilter
         .FilterOn = True
    End With
    

    
    End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
     Reports![rptMain].FilterOn = False

End Sub

Ok, this is what it looks like now. Also, I swapped the checkbox for CurrMedicaid to an actual ComboBox with selections of "Yes" and "No". I am getting the "Data Type Mismatch in Criteria Expression" error now. I've checked the code a bunch of times and cannot find the error. I know its probably something dumb, but hey, live and learn. Any clues?

 

[OxDavis]

*Bump**Bump*

 

[OxDavis]

*bumpage**bump*