Use filter on form/subform

[RJS76 via AccessMonster.com]

Hi,

I have created a quality database. Our employees process application forms.
After they have been processed few are being checked by a senior to see if
they have been processed correctly. The results are put in the quality
database.

I have created a query which shows these results (Week, type of application
form, employee, criterias on which the application form are being checked etc.
). This query named 00 - Kwaliteitssteekproef KA Adam works perfectly.

I now want to create a form in which the senior or supervisor of the team
selects three criteria.

Startweek : select a weeknumber from table tblWeek
Endweek : select a weeknumber from table tblWeek
Name : select a name from table tblMedewerker KA Adam

I want to create a form in which the abovementioned criteria can be selected
and on basis of the criteria a filter is made based upon 00 -
Kwaliteitssteekproef KA Adam which shows the records of application forms
which were not correctly processed.

For example Startweek is 10. Endweek is 14. Name is John Doe. Then all the
application forms are shown for John Doe in the weeks 10 to 14 which were not
processed correctly.

I already created a form with the selectioncriteria. I made a form for 00 -
Kwaliteitssteekproef KA Adam and used this as a subform and I made a filter
button. But I don't know how to filter the subform based upon the
selectioncriteria.

Any help would be greatly appreciated.

 

[Ken Snell \(MVP\)]

Many ways to do this......

For you, the easiest may be to change the RecordSource of the subform to be
a query SQL statement that includes the values of the main form's controls,
and have this be done when the user clicks the filter button (change my
generic names to your real names):

Private Sub FilterButtonName_Click()
Dim strSQL As String
strSQL = "SELECT * FROM QueryName " & _
"WHERE WeekNumberFieldName Between " & _
Nz(Me.Startweek.Value, 1) & _
Nz(Me.Endweek.Value, 52)
strSQL = strSQL & IIf(Len(Me.NameControl & "")>0, _
" AND NameField='" & _
Nz(Me.NameControl.Value, ""),"")
Me.SubformControlName.Form.RecordSource = strSQL
End Sub


(Note: SubformControlName in the above code is the name of the subform
control (the control that holds the subform object on the main form).)

 

[RJS76 via AccessMonster.com]

Hello Ken,

Thanks for your help.

I have tried what you suggested, but I'm getting a error 3075: Syntax error
in query expression [Week]![00 - Kwaliteitssteekproef KA Adam] Between 1015
And [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='1

This is the statement I used:

Private Sub Filter_Click()
Dim strSQL As String
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE [Week]![00 - Kwaliteitssteekproef KA Adam] Between " & _
Nz(Me.Startweek.Value, 1) & _
Nz(Me.Eindweek.Value, 52)
strSQL = strSQL & IIf(Len(Me.Medewerker & "") > 0, _
" AND [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='" & _
Nz(Me.Medewerker.Value, ""), "")
Me.[Subform KA Adam].Form.RecordSource = strSQL (<-- this is being
highlighted)
End Sub

I'm not sure if I have used the correct names.

Any suggestions? Thanks in advance.

Many ways to do this......

For you, the easiest may be to change the RecordSource of the subform to be
a query SQL statement that includes the values of the main form's controls,
and have this be done when the user clicks the filter button (change my
generic names to your real names):

Private Sub FilterButtonName_Click()
Dim strSQL As String
strSQL = "SELECT * FROM QueryName " & _
"WHERE WeekNumberFieldName Between " & _
Nz(Me.Startweek.Value, 1) & _
Nz(Me.Endweek.Value, 52)
strSQL = strSQL & IIf(Len(Me.NameControl & "")>0, _
" AND NameField='" & _
Nz(Me.NameControl.Value, ""),"")
Me.SubformControlName.Form.RecordSource = strSQL
End Sub

(Note: SubformControlName in the above code is the name of the subform
control (the control that holds the subform object on the main form).)

Hi,

[quoted text clipped - 36 lines]

Any help would be greatly appreciated.

 

[Ken Snell \(MVP\)]

You need an "ending" ' delimiter for the last value:

strSQL = strSQL & IIf(Len(Me.Medewerker & "") > 0, _
" AND [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='" & _
Nz(Me.Medewerker.Value & "'", ""), "")


--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

Thanks for your help.

I have tried what you suggested, but I'm getting a error 3075: Syntax
error
in query expression [Week]![00 - Kwaliteitssteekproef KA Adam] Between
1015
And [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='1

This is the statement I used:

Private Sub Filter_Click()
Dim strSQL As String
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE [Week]![00 - Kwaliteitssteekproef KA Adam] Between " & _
Nz(Me.Startweek.Value, 1) & _
Nz(Me.Eindweek.Value, 52)
strSQL = strSQL & IIf(Len(Me.Medewerker & "") > 0, _
" AND [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='" & _
Nz(Me.Medewerker.Value, ""), "")
Me.[Subform KA Adam].Form.RecordSource = strSQL (<-- this is being
highlighted)
End Sub

I'm not sure if I have used the correct names.

Any suggestions? Thanks in advance.

Many ways to do this......

For you, the easiest may be to change the RecordSource of the subform to
be
a query SQL statement that includes the values of the main form's
controls,
and have this be done when the user clicks the filter button (change my
generic names to your real names):

Private Sub FilterButtonName_Click()
Dim strSQL As String
strSQL = "SELECT * FROM QueryName " & _
"WHERE WeekNumberFieldName Between " & _
Nz(Me.Startweek.Value, 1) & _
Nz(Me.Endweek.Value, 52)
strSQL = strSQL & IIf(Len(Me.NameControl & "")>0, _
" AND NameField='" & _
Nz(Me.NameControl.Value, ""),"")
Me.SubformControlName.Form.RecordSource = strSQL
End Sub

(Note: SubformControlName in the above code is the name of the subform
control (the control that holds the subform object on the main form).)

Hi,

[quoted text clipped - 36 lines]

Any help would be greatly appreciated.

 

[RJS76 via AccessMonster.com]

Hello Ken,

Thanks for your suggestions. I tried it, but I got an error. With the help of
some of my colleagues we came up with the following statement:

Private Sub Filter_Click()
Dim strSQL As String
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE ([Week] Between " & Me.Startweek.Value & " And " & Me.Eindweek.
Value & _
") AND [Medewerker]= " & Me.Medewerker.Value

Me.[Subform KA Adam].Form.RecordSource = strSQL
End Sub

Fortunately this statement works, but it is not to my complete satisfaction
because if the Startweek or Eindweek or Medewerker field are blank and the
filter button is pushed we get an error. What I want is that those fields are
required. If you don't fill in anything you get an error message saying that
the field is required and that you cannot proceed without filling it in. In
the properties of the fields I entered by the validation rule: Is Not Null
and typed some text in the validation text. But when I open the form and
press TAB I still can get to the next field without being prompted that the
field is required to fill in.

Furthermore, when the form is opened you immediately see one of our employees
with the week, type of application forms etc. in the subform. This is nog
desirable. What I would like is that when the form is opened for the first
time the subform is empty.

Do you (or anybody else) know how this has to been done? Thanks again for
your help. Much appreciated!

You need an "ending" ' delimiter for the last value:

strSQL = strSQL & IIf(Len(Me.Medewerker & "") > 0, _
" AND [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='" & _
Nz(Me.Medewerker.Value & "'", ""), "")

Hello Ken,

[quoted text clipped - 54 lines]

 

[Ken Snell \(MVP\)]

So the last field is a numeric field; that is why the change you made is now
working.

In your code, add some validation steps at the beginning:


Private Sub Filter_Click()
Dim strSQL As String
If Len(Me.Startweek.Value & "") = 0 Then
MsgBox "Enter a start week number!"
ElseIf Len(Me.Eindweek.Value & "") = 0 Then
MsgBox "Enter a end week number!"
ElseIf Len(Me.Medewerker.Value & "") = 0 Then
MsgBox "Enter worker value!"
Else
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE ([Week] Between " & Me.Startweek.Value & " And " & _
Me.Eindweek.Value & _
") AND [Medewerker]= " & Me.Medewerker.Value
Me.[Subform KA Adam].Form.RecordSource = strSQL
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

Thanks for your suggestions. I tried it, but I got an error. With the help
of
some of my colleagues we came up with the following statement:

Private Sub Filter_Click()
Dim strSQL As String
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE ([Week] Between " & Me.Startweek.Value & " And " & Me.Eindweek.
Value & _
") AND [Medewerker]= " & Me.Medewerker.Value

Me.[Subform KA Adam].Form.RecordSource = strSQL
End Sub

Fortunately this statement works, but it is not to my complete
satisfaction
because if the Startweek or Eindweek or Medewerker field are blank and the
filter button is pushed we get an error. What I want is that those fields
are
required. If you don't fill in anything you get an error message saying
that
the field is required and that you cannot proceed without filling it in.
In
the properties of the fields I entered by the validation rule: Is Not Null
and typed some text in the validation text. But when I open the form and
press TAB I still can get to the next field without being prompted that
the
field is required to fill in.

Furthermore, when the form is opened you immediately see one of our
employees
with the week, type of application forms etc. in the subform. This is nog
desirable. What I would like is that when the form is opened for the first
time the subform is empty.

Do you (or anybody else) know how this has to been done? Thanks again for
your help. Much appreciated!

You need an "ending" ' delimiter for the last value:

strSQL = strSQL & IIf(Len(Me.Medewerker & "") > 0, _
" AND [Medewerker]![00 - Kwaliteitssteekproef KA Adam]='" & _
Nz(Me.Medewerker.Value & "'", ""), "")

Hello Ken,

[quoted text clipped - 54 lines]

Any help would be greatly appreciated.

 

[RJS76 via AccessMonster.com]

Thanks!, this works like a charm.

Do you also know how to solve my other "problem":

"Furthermore, when the form is opened you immediately see one of our
employees
with the week, type of application forms etc. in the subform. This is not
desirable. What I would like is that when the form is opened for the first
time the subform is empty."

Once again thanks for your help.

So the last field is a numeric field; that is why the change you made is now
working.

In your code, add some validation steps at the beginning:

Private Sub Filter_Click()
Dim strSQL As String
If Len(Me.Startweek.Value & "") = 0 Then
MsgBox "Enter a start week number!"
ElseIf Len(Me.Eindweek.Value & "") = 0 Then
MsgBox "Enter a end week number!"
ElseIf Len(Me.Medewerker.Value & "") = 0 Then
MsgBox "Enter worker value!"
Else
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE ([Week] Between " & Me.Startweek.Value & " And " & _
Me.Eindweek.Value & _
") AND [Medewerker]= " & Me.Medewerker.Value
Me.[Subform KA Adam].Form.RecordSource = strSQL
End If
End Sub

Hello Ken,

[quoted text clipped - 47 lines]

 

[Ken Snell \(MVP\)]

Sure... in the form's Load event, set the RecordSource of the subform to an
empty string:

Private Sub Form_Load()
Me.[Subform KA Adam].Form.RecordSource = ""
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks!, this works like a charm.

Do you also know how to solve my other "problem":

"Furthermore, when the form is opened you immediately see one of our
employees
with the week, type of application forms etc. in the subform. This is not
desirable. What I would like is that when the form is opened for the first
time the subform is empty."

Once again thanks for your help.

So the last field is a numeric field; that is why the change you made is
now
working.

In your code, add some validation steps at the beginning:

Private Sub Filter_Click()
Dim strSQL As String
If Len(Me.Startweek.Value & "") = 0 Then
MsgBox "Enter a start week number!"
ElseIf Len(Me.Eindweek.Value & "") = 0 Then
MsgBox "Enter a end week number!"
ElseIf Len(Me.Medewerker.Value & "") = 0 Then
MsgBox "Enter worker value!"
Else
strSQL = "SELECT * FROM [00 - Kwaliteitssteekproef KA Adam] " & _
"WHERE ([Week] Between " & Me.Startweek.Value & " And " & _
Me.Eindweek.Value & _
") AND [Medewerker]= " & Me.Medewerker.Value
Me.[Subform KA Adam].Form.RecordSource = strSQL
End If
End Sub

Hello Ken,

[quoted text clipped - 47 lines]

Any help would be greatly appreciated.

 

[RJS76 via AccessMonster.com]

Thanks Ken. This works perfectly. When I show this database to the
supervisors they will be very pleased. Thanks for all your help!

Sure... in the form's Load event, set the RecordSource of the subform to an
empty string:

Private Sub Form_Load()
Me.[Subform KA Adam].Form.RecordSource = ""
End Sub

Thanks!, this works like a charm.

[quoted text clipped - 36 lines]

 

[Ken Snell \(MVP\)]

Glad to help!