How do compile filtering requirements in an event procedure?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to filter out a table using two fields. Does anybody know what
the VSB code is to 'compile' filtering information so that I can filter by
one field and then by the other?
 
I am trying to filter out a table using two fields. Does anybody know what
the VSB code is to 'compile' filtering information so that I can filter by
one field and then by the other?
Click to expand...

.... and confusing.

There's no such thing as VSB in Access. I guess you mean VBA? Are you
building a Query (which wouldn't need any VBA code at all), or trying
to set a Form's Filter property?

Note that a Form's Filter property is just a SQL WHERE clause (without
the WHERE); as such, it can contain many fields, not just one. If you
want, you could use

Dim strFilter As String
....
strFilter = "[ThisField] = " & intNum _
& " AND [Thatfield] = '" & strText & "'"
Me.Filter = strFilter
Me.FilterOn = True

to set a Form's filter property to filter on the numeric field
ThisField and the text field ThatField.

John W. Vinson[MVP]
 
John Vinson said:
I am trying to filter out a table using two fields. Does anybody know what
the VSB code is to 'compile' filtering information so that I can filter by
one field and then by the other?
Click to expand...

.... and confusing.

There's no such thing as VSB in Access. I guess you mean VBA? Are you
building a Query (which wouldn't need any VBA code at all), or trying
to set a Form's Filter property?

Note that a Form's Filter property is just a SQL WHERE clause (without
the WHERE); as such, it can contain many fields, not just one. If you
want, you could use

Dim strFilter As String
....
strFilter = "[ThisField] = " & intNum _
& " AND [Thatfield] = '" & strText & "'"
Me.Filter = strFilter
Me.FilterOn = True

to set a Form's filter property to filter on the numeric field
ThisField and the text field ThatField.

John W. Vinson[MVP]
Click to expand...

John Thanks for your response but I do not understand. I apologize for my
incorrect use of terminology. Here is the Event Procedure Code that I have
in VB. I don't exactly know how to implement your code into the filter. I
need to filter by "CRSE_CD" and then by "SESSION_NO". Please see below and
edit if you can. Your help is greatly appreciated!

Private Sub FilterChildForm1()

If Me.NewRecord Then
Forms![SFrmAttndc].DataEntry = True
Else
Forms![SFrmAttndc].Filter = "[CRSE_CD] = " & Me.[CRSE_CD]
Forms![SFrmAttndc].FilterOn = True
End If

End Sub
 
John Thanks for your response but I do not understand. I apologize for my
incorrect use of terminology. Here is the Event Procedure Code that I have
in VB. I don't exactly know how to implement your code into the filter. I
need to filter by "CRSE_CD" and then by "SESSION_NO". Please see below and
edit if you can. Your help is greatly appreciated!
Click to expand...

I'm not sure what you mean by the word "then" in this case. Do you
want to first display the results filtered by CRSE_CD, and then
separately display the results "drilling down" to a selected
SESSION_NO? Or is it just a two-field filter?

Assuming the latter:

Private Sub FilterChildForm1()

If Me.NewRecord Then
Forms![SFrmAttndc].DataEntry = True
Else
Forms![SFrmAttndc].Filter = "[CRSE_CD] = " & Me.[CRSE_CD] _
& " AND [SESSION_NO] = " & Me.[SESSION_NO]
Forms![SFrmAttndc].FilterOn = True
End If

End Sub


This assumes that both fields are of Number datatype. Text fields
require a ' or " delimiter. For instance, if CRSE_CD is a Text field
use:

Private Sub FilterChildForm1()

Forms![SFrmAttndc].Filter = "[CRSE_CD] = '" & Me.[CRSE_CD] _
& "' AND [SESSION_NO] = " & Me.[SESSION_NO]



John W. Vinson[MVP]
 
Back
Top