access form checkboxes populate sql in vba

Question

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?

crthompson · Accepted Answer · 2014-12-19 13:51:13Z

1

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.

edited Dec 19, 2014 at 13:51

answered Dec 17, 2014 at 19:22

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

barker Over a year ago

nice, short, smart :)

crthompson Over a year ago

@barker was something wrong with the solution? Why did you unaccept?

crthompson Over a year ago

@barker Oh, perhaps I forgot the and control is checked? I've added it here.

barker Over a year ago

Hey sorry paqogomez, for some reason this only is grabbing the first value in my form. let me type up my code here

crthompson Over a year ago

@barker you can post it in the original question if you like, i'm happy to help, though you'd get a wider audience if you posted a new question.

|

Collectives™ on Stack Overflow

access form checkboxes populate sql in vba

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related