Optional filtering (was More questions..)

[Daryl2106]

Hi,



This is year two designing a DB for our adult learning department and with all the help I received last year from the forum the DB worked exceedingly well. We are now asking it to do more. So I have a lot of questions to ask over the next several weeks.

Firstly, I have a table that stores all student information and a form based on that table to enter and review data. One of the fields I have is whether they are "ACTIVE" or not. We would like to be able to display only the active students from time to time on the form by clicking a check box. Do I have to create a new form or can I create a query or event and run it within the current form?

Thanks and take care,

Daryl

[RuralGuy]

I have all of my forms bound to queries and when "Active" is an issue I have a CheckBox on the form for that and make the query aware of that control on the form through the Forms collection..

[Daryl2106]

Thanks.

What if the form isn't based on a query. can you still filter records for "active" students?

[RuralGuy]

Is there some reason you don't want to use a query? They are far more flexible and take up little or no room in a db.

[pbaldy]

I use this type of thing, which will work whichever the record source is:

  If Me.chkShowRetired = True Then
     Me.FilterOn = False   
  Else
     Me.Filter = "Retired = False"
     Me.FilterOn = True
   End If

I'm going to change your thread title to something more descriptive.

Edit: that code would be in the after update event of the check box.

[Daryl2106]

It is just that I have a fairly large form linked directly to the table and I don't know how to create a query without having to completely rebuild the form

[orange]

Investigate SELECT query
here is one site
http://www.w3schools.com/sql/sql_select.asp

[RuralGuy]

A Form cannot tell the difference between a table and a query. If you create a query that includes all of the fields of the table, you could then set it as the RecordSource of the Form and everything would just work with no other changes.

[Daryl2106]

Thanks so much for everything so far everyone. I just got back to this today.

I tried the event procedure with the following code:

Private Sub Check2107_AfterUpdate()

If Me.Check2107 = True Then
Me.FilterOn = False
Else
Me.Filter = "Check2107 = False"
Me.FilterOn = True
End If


End Sub

I am getting parameter box popping up when I "de-select" the check box. I will change the name to something more appropriate later. What did I do wrong?

Thanks,

Daryl

[pbaldy]

Is Check2107 the name of the field in the table? That's what Access is looking for there.

[Daryl2106]

The check box is not bound to a table. Does it need to be?

[pbaldy]

No, but the line setting the filter has to specify a field in the record source. You're telling Access what to filter on. It could also look like:

Me.Filter = "Status = 'Active'"

[Daryl2106]

Hi and thanks,

The field in the table is called "Active". The checkbox on the form is "Check2107".

[pbaldy]

So did you change it?

[Daryl2106]

I tried several times but I'm not great with code so I didn't get it to work. I'm not where to put the table field name.