CODEWITHSUNDEEP
sqlms-access
user979616
user979616

Reputation: 273

Customizing an access query based on values of various checkboxes

I'm new to access, and I need to selectively query a database based on a set of checkboxes. For example, If a checkbox called basketball is checked, I'll want to find all records that have that in their Name field. If basketball and baseball are checked, I'll want to find records for both basketball and baseball. The current way we have of doing this is ugly and inefficient, and I'm thinking that this is a fairly common problem to have, so there must be a better way of solving it. I've seen similar things online, but these deal with only one checkbox (not 10 or so like in our form) and they simply aren't very helpful.

Thanks

Upvotes: 0

Views: 57

Answers (2)

phicon
phicon

Reputation: 3617

As no reference information is provided, i use the following for this example;

  • Formname = Form1
  • Tablename = test
  • Checkfieldname = BaseballCheck

In the form property sheet, add the following vba for the BaseBallCheck, this will requery the form after the check has been enabled/disabled.

Private Sub BaseballCheck_AfterUpdate()
    Me.Requery
End Sub

Then in the form Recordsource add the following 

SELECT test.Baseball
FROM test
WHERE (((test.Baseball)=[forms]![Form1]![BaseballCheck]));

Now when the form refreshes only the values are shown where Baseball = checked or unchecked.

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

I'll make a few assumptions, here. I'll assume you have 4 sports; Baseball, Basketball, Football and Hockey. You can add more if you like.

I'll assume you have 4 checkboxes; BaseballCheck, BasketballCheck, FootballCheck and HockeyCheck.

I'll assume you have a table called MyTable, with a field called Sport.

What you can do is add a button to your form. Call it btnSubmit. Add this VBA to your button:

Dim db as Database
Dim rec as Recordset
Dim MySQL as String

Set db = CurrentDB
MySQL = "SELECT * FROM MyTable WHERE 1 = 1"

If BaseballCheck = True then
  MySQL = MySQL & " AND Sport = 'Baseball'"
EndIf

If HockeyCheck = True then  
  MySQL = MySQL & " AND Sport = 'Hockey'"
EndIf

If BasketballCheck = True then  
  MySQL = MySQL & " AND Sport = 'Basketball'"
EndIf

Etc...

Set rec = db.OpenRecordset(MySQL)

Instead of that last statement, you can use CreateQuerydef to create a permanent query that you can then use as the basis for a report. However, you would have to add some code at the beginning to delete that querydef if it exists, otherwise it will throw an error.

If you want something a little more elegant (and one that will allow for easier scalability) you could always loop through all the checkboxes on your form and dump the sports into an array, and then use that array as the subject of a "WHERE Sport IN (...)" statement.

Upvotes: 1

Related Questions