Opening a multiple filtered form from another form

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I have two forms (Form1 and Form2)> Form1 has 7
comboboxes and I want to open Form2 using any combination
of the comboboxes. The problem is that if any one of the
comboboxes has not been selected and its current value is
therefore "null", then I get a syntax error. Is there any
default value that I can assign to the comboboxes which
would be interpreted as meaning "no filter for this
column"? My Code is as follows. The individual parts work
fine. But, put them all together, like below, and leave
one combobox unselected and I get error messages

stLinkCriteria = "[Date]>" & "#" & Me![StartDate] & "#" _
& "And" & "[Date]<" & "#" & Me![EndDate] & "#" _
& "And" & "[ProducedShift]=" & "'" & Me![Shift] & "'" _
& "And" & "[TLPriKey]=" & Me![TeamLeaders] _
& "And" & "[PONum]=" & Me![PONum] _
& "And" & "[RoutingID]=" & Me![RoutingNum]
 
Ray,

Try this instead:

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>#" & Me![StartDate] & "#"
End If
If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<#" & Me![EndDate] & "#"
End If
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If
If Not IsNull(Me![TLPriKey]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If
If Not IsNull(Me![PONum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PONum]=" & Me![PONum]
End If
If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If
stLinkCriteria = vStr

HTH,
Nikos
 
Thankyou. Had come to same idea just few minutes before
-----Original Message-----
Ray,

Try this instead:

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>#" & Me![StartDate] & "#"
End If
If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<#" & Me![EndDate] & "#"
End If
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If
If Not IsNull(Me![TLPriKey]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If
If Not IsNull(Me![PONum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PONum]=" & Me![PONum]
End If
If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If
stLinkCriteria = vStr

HTH,
Nikos
I have two forms (Form1 and Form2)> Form1 has 7
comboboxes and I want to open Form2 using any combination
of the comboboxes. The problem is that if any one of the
comboboxes has not been selected and its current value is
therefore "null", then I get a syntax error. Is there any
default value that I can assign to the comboboxes which
would be interpreted as meaning "no filter for this
column"? My Code is as follows. The individual parts work
fine. But, put them all together, like below, and leave
one combobox unselected and I get error messages

stLinkCriteria = "[Date]>" & "#" & Me![StartDate] & "#" _
& "And" & "[Date]<" & "#" & Me![EndDate] & "#" _
& "And" & "[ProducedShift]=" & "'" & Me![Shift] & "'" _
& "And" & "[TLPriKey]=" & Me![TeamLeaders] _
& "And" & "[PONum]=" & Me![PONum] _
& "And" & "[RoutingID]=" & Me![RoutingNum]
Click to expand...
.
Click to expand...
 
Back
Top