Fiter records based on check box selection

[PANGAWD]

Hi!!!

I´m used to search your forum all the time if I have any problem in Java, C, etc,etc... and I always found an answer, but this time I can´t...

I know that this must be a simple answer, but I just can´t figure it out!

I´m doing a simple, simple, extremelly simple Access 2003 database and I´m stuck at a filtering problem.

1 - The users fill out a form with different check boxes for each record (among other fields)
2 - I want a user to be able to search on the main window, filling out the check boxes he wants to search and pressing a button, the records that correspond to the check boxes filled.


Example:

Form1 containing:

Name: txtbox
Age: txtbox
Occupation: txtbox

Hobbies: Sports: chkbox Movie:chkbox Internet:chkbox
Motors: chkbox

Form2 (Main Window) containg:

Button1 to insert a new record (sends you to Form1).
Button2 to change/delete a record (sends you to a clone of Form1 - let´s call it Form3)

The checkboxes Sports, Movie, Internet and Motors
And a button that (should) filters the records through the checkboxes he checked and present you Form3 filtered.


I know this is confusing, but I stink at explaining things!!

I´ve tried VBA code, SQL (through the RecordSource), Macros... I´ve tried everything I know and nothing :( !

Can anyone help me please??!!!

[abolos]

Hi!!!

I´m used to search your forum all the time if I have any problem in Java, C, etc,etc... and I always found an answer, but this time I can´t...

I know that this must be a simple answer, but I just can´t figure it out!

I´m doing a simple, simple, extremelly simple Access 2003 database and I´m stuck at a filtering problem.

1 - The users fill out a form with different check boxes for each record (among other fields)
2 - I want a user to be able to search on the main window, filling out the check boxes he wants to search and pressing a button, the records that correspond to the check boxes filled.


Example:

Form1 containing:

Name: txtbox
Age: txtbox
Occupation: txtbox

Hobbies: Sports: chkbox Movie:chkbox Internet:chkbox
Motors: chkbox

Form2 (Main Window) containg:

Button1 to insert a new record (sends you to Form1).
Button2 to change/delete a record (sends you to a clone of Form1 - let´s call it Form3)

The checkboxes Sports, Movie, Internet and Motors
And a button that (should) filters the records through the checkboxes he checked and present you Form3 filtered.


I know this is confusing, but I stink at explaining things!!

I´ve tried VBA code, SQL (through the RecordSource), Macros... I´ve tried everything I know and nothing :( !

Can anyone help me please??!!!

In your case you may use the SELECT SQL statement. Give it a try.
Abolos

[PANGAWD]

In your case you may use the SELECT SQL statement. Give it a try.
Abolos

I´ve already tried

SELECT * FROM "Table" WHERE "Field" = True

and

SELECT * FROM "Table" WHERE "Field" And "Field2" And "Field3" = True

but the problem is that the field of the search isn´t always True and you can´t put variables in the SQL statement... (through VBA) like

*************** *************** *************** *************** *************** ************
Dim MySQL As String

If ([Field from Form2] = True) Then
MySQL = "SELECT * FROM Table Where [Field from Table]=True;"
End If <--(Just one check among several)

DoCmd.OpenForm "Form3", acNormal, , , acFormEdit, acWindowNormal
Forms!Form3.Rec ordSource = MySQL
*************** *************** *************** *************** *************** *************

And this only works for one check search, if you select two options to search (more "If") it will "erase" the first and search only the last one as the MySQL variable is just one...

Thx!!

[JKing]

There are a few things you can do in VBA to get around this.

Just a little information to start though. If say the user checks Sports and Movies do you want to bring up only those persons where their hobbies are sports and Movies or do you want to show persons that may only like sports or may only like Movies?

[PANGAWD]

There are a few things you can do in VBA to get around this.

Just a little information to start though. If say the user checks Sports and Movies do you want to bring up only those persons where their hobbies are sports and Movies or do you want to show persons that may only like sports or may only like Movies?

All the people that like (checked) Sports AND Movies...
That have checked both "items" on the record...

[JKing]

What I would suggest is setting the recordsource of Form3 in the design mode property sheet to a query that retrieves all the records and has no Where criteria. Then you can build the criteria and pass it into the Where Statement parameter of the open form command.

 
Dim strWhere As String
 
If Me.chkSports Then
    strWhere = strWhere & " AND Sports = True"
End If
If Me.chkMovies Then
    strWhere = strWhere & " AND Movies = True"
End If
If Me.chkInternet Then
    strWhere = strWhere & " AND Internet = True"
End If
If Me.chkMotors Then
    strWhere = strWhere & " AND Motors = True"
End If
 
If strWhere <> "" Then
    strWhere = Mid(strWhere, 6)
    Docmd.OpenForm "form3", acNormal, , strWhere, acEdit 
Else
    Msgbox "You haven't selected anything to filter by!"
End If
 

[PANGAWD]

What I would suggest is setting the recordsource of Form3 in the design mode property sheet to a query that retrieves all the records and has no Where criteria. Then you can build the criteria and pass it into the Where Statement parameter of the open form command.

 
Dim strWhere As String
 
If Me.chkSports Then
    strWhere = strWhere & " AND Sports = True"
End If
If Me.chkMovies Then
    strWhere = strWhere & " AND Movies = True"
End If
If Me.chkInternet Then
    strWhere = strWhere & " AND Internet = True"
End If
If Me.chkMotors Then
    strWhere = strWhere & " AND Motors = True"
End If
 
If strWhere <> "" Then
    strWhere = Mid(strWhere, 6)
    Docmd.OpenForm "form3", acNormal, , strWhere, acEdit 
Else
    Msgbox "You haven't selected anything to filter by!"
End If
 

Txs A LOT, I´ll try It but sounds like it works, never thought of that!!!


DAMN!!! You´re really good!!! Many many many THANKS!!! I wish there would be a way I could repay you!! If you ever come to Portugal be sure to PM me ;)

Once again...Thnx!!

[JKing]

You're quite welcome. Glad it worked out for you.

Jking