CODEWITHSUNDEEP
mysqlms-accesscomboboxvba
Andrew Richey
Andrew Richey

Reputation: 1

How to select data from subform in access after filtering table by combo boxes

Here is my SQL (I'm tasked with working with the data I did not make the tables, I know they have terrible names):

SQL = "SELECT [risk - original].IDrisk AS [Risk ID], [risk - original].Riskname AS [Risk Name], [risk - original].Description, " & _
"[risk - original].Category, [risk - original].InitialProbability AS [Probability], [risk - original].InitialSeverity AS [Severity], " & _
"[risk - original].InitialProbability * [risk - original].InitialSeverity AS [Risk Score], [risk - original].RootCause AS [Root Cause] " & _
"FROM [risk - original] " & _
"WHERE ([InitialSeverity] = " & Me.cboSeverity & ") OR ([InitialProbability] = " & Me.cboProbability & ") OR ([Category] = '" & Me.cboRiskCategory & "'))"

I can make this code work with the WHERE clause using only a condition for a single combo box, but when I try to add all combo box fields the code fails, right now the combo boxes are programmed as (for example):

Dim mySeverity As String
mySeverity = "Select * from [risk - original] where ([InitialSeverity] = " & Me.cboSeverity & ")"
Me.riskOriginalSubform.Form.RecordSource = mySeverity
Me.riskOriginalSubform.Form.Requery
Me.cboRiskCategory = Null
Me.cboProbability = Null

In the future I hope to be able to sort by multiple combo boxes and then select the data from the subform, in the end the data is exported to excel.

Thanks!

Upvotes: 0

Views: 141

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

HEre's a generic answer:

vbSql = "SELECT stuff " & _
        "FROM tbl " & _
        "WHERE (cmb.value = condition "

If cmb.value = condition Then
    vbSql = vbSql & "AND moreSqlconditions"
End If

vbSql = vbSql & ");"

Me.subForm.Form.RecordSource = vbSql

Hopefully this works?

Upvotes: 0

Related Questions