CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
barker
barker

Reputation: 1055

access form checkboxes populate sql in vba

So this is slightly more complicated than your typical 

strSQL = "sql string where & forms!myform.variable & "stuff"

I have a table that changes, users add new years to it. So next year will be new 2015 data. I created a form with checkboxes for each year, this form is based on this sql 

select year from table group by year

If a user marks the check boxes for certain years, i want to write a query in vba strsql based on the users checkbox selections. So for example say I currently have 3 years, 2012, 2013, 2014, the user only marks years 2012 and 2014, skipping 2013 would look like

2012 x
2013
2014 x

So in vba strsql I want to write 

"select * from mytable where year=2012 or year=2014"

The problem is the list of years grows, so I can't hard code this it has to be dynamic. For example next year my original sql will select 2015 as well, so the user might mark

2012 x
2013 x
2014
2015 x

So now my vba strsql would be 

"select * from mytable where year=2012 or year=2013 or year=2015"

Every year the list will grow, how can I account for this in vba strsql when the checkbox marks = true?

Upvotes: 1

Views: 982

Answers (1)

crthompson
crthompson

Reputation: 15875

You need to loop your values and concatenate to your sql

Something like this should work:

Private Sub CommandButton1_Click()
  Dim strSQL = "select * from mytable where 1=1"
  Dim cCont As Control
  For Each cCont In Me.Controls
      If TypeName(cCont) = "Checkbox" And cCont.Checked Then
          strSQL = strSQL + " or year=" + cCont.Text
      End If
  Next cCont

I added the 1=1 to the where clause so you could just concatinate the OR statements without worrying about if its the first OR statement or not.

Upvotes: 1

Related Questions