Filter a subform

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

I have the below function that is called out when a user checks one of 4
boxes on the main form. There has got to be a better way to handle my code
then what I have. There are also 3 other unbound drop down boxes on the main
form that the user selects KW, VOLTS, PHASE.

From that it lists out the heater models available. This issue is depending
on the combination of the user selection of the check box the list may or may
not display the data correctly. Would there be a better way to write my code
to handle the multiple combinations?

cb = Check Box
cbo = Combo Box (drop down list)

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

If Me.cbOB = -1 Then
If Me.cbDC = -1 Then
If Me.cbSD = -1 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;OB;DC;SD;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbOB;cbDC;cbSD;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = 0 Then
If Me.cbDC = -1 Then
If Me.cbSD = -1 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;DC;SD;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbDC;cbSD;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = 0 Then
If Me.cbDC = 0 Then
If Me.cbSD = -1 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;SD;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbSD;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = 0 Then
If Me.cbDC = 0 Then
If Me.cbSD = 0 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = 0 Then
If Me.cbDC = 0 Then
If Me.cbSD = 0 Then
If Me.cbSTD = 0 Then

stLinkChild = "KW;VOLTS;PHASE;"
stLinkMaster = "cboKW;cboVolts;cboPhase;"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = -1 Then
If Me.cbDC = 0 Then
If Me.cbSD = 0 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;OB;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbOB;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If

If Me.cbOB = 0 Then
If Me.cbDC = -1 Then
If Me.cbSD = 0 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;DC;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbDC;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If


If Me.cbOB = 0 Then
If Me.cbDC = -1 Then
If Me.cbSD = -1 Then
If Me.cbSTD = -1 Then

stLinkChild = "KW;VOLTS;PHASE;DC;SD;STD"
stLinkMaster = "cboKW;cboVolts;cboPhase;cbDC;cbSD;cbSTD"

Me.frmHeaterStock.LinkChildFields = ""
Me.frmHeaterStock.LinkMasterFields = ""

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster


End If
End If
End If
End If


End Function
 
B

Bob Quintal

I would change the values returned by the four controls from -1 to
1,2,4,8 .

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

Dim iChoices as integer ' defaults to 0, no need to set.
If Me.cbOB = -1 Then ichoices = ichoices +1
If Me.cbDC = -1 Then ichoices = ichoices +2
If Me.cbSD = -1 Then ichoices = ichoices +4
If Me.cbSTD = -1 Then ichoices = ichoices +8
' this gives a single value to test, using a select case

Select Cace iChoices
Case 0
' whatever if nothing chosen
Case 15
Me.frmHeaterStock.LinkChildFields = _
"KW;VOLTS;PHASE;OB;DC;SD;STD"
Me.frmHeaterStock.LinkMasterFields = _
"cboKW;cboVolts;cboPhase;cbOB;cbDC;cbSD;cbSTD"
Case 14
Me.frmHeaterStock.LinkChildFields = _
"KW;VOLTS;PHASE;DC;SD;STD"
Me.frmHeaterStock.LinkMasterFields = _
"cboKW;cboVolts;cboPhase;cbDC;cbSD;cbSTD"
Case 12
Me.frmHeaterStock.LinkChildFields = _
"KW;VOLTS;PHASE;SD;STD"
Me.frmHeaterStock.LinkMasterFields = _
"cboKW;cboVolts;cboPhase;cbSD;cbSTD"
Case 8
Me.frmHeaterStock.LinkChildFields = _
"KW;VOLTS;PHASE;STD"
Me.frmHeaterStock.LinkMasterFields = _
"cboKW;cboVolts;cboPhase;cbSTD"
Case n
'Do the other combinations the same way.
Case Else
'handle if an illegal combination entered.
End Select
 
B

Bob Quintal

After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"

If Me.cbOB = -1 Then
stLinkChild = stLinkChild & ";OB"
stLinkMaster = stLinkMaster & ";cbOB"
end if
If Me.cbDC = -1 Then
stLinkChild = stLinkChild & ";DC"
stLinkMaster = stLinkMaster & ";cbDC"
end if
If Me.cbSD = -1 Then
stLinkChild = stLinkChild & ";SD"
stLinkMaster = stLinkMaster & ";cbSD"
end if
If Me.cbSTD = -1 Then
stLinkChild = stLinkChild & ";STD"
stLinkMaster = stLinkMaster & ";cbSTD"
end if

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster

end function
 
M

mattc66 via AccessMonster.com

This looks very promissing. I add it to my form. Do we need something when
the check box = 0?

Bob said:
After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"

If Me.cbOB = -1 Then
stLinkChild = stLinkChild & ";OB"
stLinkMaster = stLinkMaster & ";cbOB"
end if
If Me.cbDC = -1 Then
stLinkChild = stLinkChild & ";DC"
stLinkMaster = stLinkMaster & ";cbDC"
end if
If Me.cbSD = -1 Then
stLinkChild = stLinkChild & ";SD"
stLinkMaster = stLinkMaster & ";cbSD"
end if
If Me.cbSTD = -1 Then
stLinkChild = stLinkChild & ";STD"
stLinkMaster = stLinkMaster & ";cbSTD"
end if

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster

end function
I have the below function that is called out when a user checks
one of 4 boxes on the main form. There has got to be a better way
Click to expand...
[quoted text clipped - 179 lines]
End Function
Click to expand...
Click to expand...
 
M

mattc66 via AccessMonster.com

Its starting to work. The issue I am having with it now is it will only allow
1 of the 4 check boxes to be selected. If I check more then one the sub form
shows up blank.

Bob said:
After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"

If Me.cbOB = -1 Then
stLinkChild = stLinkChild & ";OB"
stLinkMaster = stLinkMaster & ";cbOB"
end if
If Me.cbDC = -1 Then
stLinkChild = stLinkChild & ";DC"
stLinkMaster = stLinkMaster & ";cbDC"
end if
If Me.cbSD = -1 Then
stLinkChild = stLinkChild & ";SD"
stLinkMaster = stLinkMaster & ";cbSD"
end if
If Me.cbSTD = -1 Then
stLinkChild = stLinkChild & ";STD"
stLinkMaster = stLinkMaster & ";cbSTD"
end if

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster

end function
I have the below function that is called out when a user checks
one of 4 boxes on the main form. There has got to be a better way
Click to expand...
[quoted text clipped - 179 lines]
End Function
Click to expand...
Click to expand...
 
M

mattc66 via AccessMonster.com

One more think I needed to point out. If all the check boxes are unchecked it
does display all records based on the KW,VOLTS and PHASE selection. I am
wondering if I should then have the check boxes hid the items selected.
Meaning if OB is checked it hids all items with OB.

Bob said:
After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation

Private Function apFilter()
Dim stLinkChild As String
Dim stLinkMaster As String

stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"

If Me.cbOB = -1 Then
stLinkChild = stLinkChild & ";OB"
stLinkMaster = stLinkMaster & ";cbOB"
end if
If Me.cbDC = -1 Then
stLinkChild = stLinkChild & ";DC"
stLinkMaster = stLinkMaster & ";cbDC"
end if
If Me.cbSD = -1 Then
stLinkChild = stLinkChild & ";SD"
stLinkMaster = stLinkMaster & ";cbSD"
end if
If Me.cbSTD = -1 Then
stLinkChild = stLinkChild & ";STD"
stLinkMaster = stLinkMaster & ";cbSTD"
end if

Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster

end function
I have the below function that is called out when a user checks
one of 4 boxes on the main form. There has got to be a better way
Click to expand...
[quoted text clipped - 179 lines]
End Function
Click to expand...
Click to expand...
 
R

rquintal

This looks very promissing. I add it to my form. Do we need something when
the check box = 0?





Bob said:
After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation
Click to expand...
Private Function apFilter()
 Dim stLinkChild As String
 Dim stLinkMaster As String
Click to expand...
stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"
Click to expand...
 If Me.cbOB = -1 Then
  stLinkChild =  stLinkChild & ";OB"
  stLinkMaster = stLinkMaster & ";cbOB"
 end if
 If Me.cbDC = -1 Then
  stLinkChild =  stLinkChild & ";DC"
  stLinkMaster = stLinkMaster & ";cbDC"
 end if
 If Me.cbSD = -1 Then
  stLinkChild =  stLinkChild & ";SD"
  stLinkMaster = stLinkMaster & ";cbSD"
 end if
 If Me.cbSTD = -1 Then
  stLinkChild =  stLinkChild & ";STD"
  stLinkMaster = stLinkMaster & ";cbSTD"
 end if
Click to expand...
Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster
Click to expand...
end function
I have the below function that is called out when a user checks
one of 4 boxes on the main form. There has got to be a better way
Click to expand...
[quoted text clipped - 179 lines]
End Function
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
Click to expand...

Should not need to handle 0s as the logic starts with the zeroes case
and adds only if the value of a box is -1
 
R

rquintal

One more think I needed to point out. If all the check boxes are unchecked it
does display all records based on the KW,VOLTS and PHASE selection. I am
wondering if I should then have the check boxes hid the items selected.
Meaning if OB is checked it hids all items with OB.





Bob said:
After posting my previous reply, I realized there's an even simpler
solution: create the link strings by concatenation
Click to expand...
Private Function apFilter()
 Dim stLinkChild As String
 Dim stLinkMaster As String
Click to expand...
stLinkChild = "KW;VOLTS;PHASE"
stLinkMaster = "cboKW;cboVolts;cboPhase"
Click to expand...
 If Me.cbOB = -1 Then
  stLinkChild =  stLinkChild & ";OB"
  stLinkMaster = stLinkMaster & ";cbOB"
 end if
 If Me.cbDC = -1 Then
  stLinkChild =  stLinkChild & ";DC"
  stLinkMaster = stLinkMaster & ";cbDC"
 end if
 If Me.cbSD = -1 Then
  stLinkChild =  stLinkChild & ";SD"
  stLinkMaster = stLinkMaster & ";cbSD"
 end if
 If Me.cbSTD = -1 Then
  stLinkChild =  stLinkChild & ";STD"
  stLinkMaster = stLinkMaster & ";cbSTD"
 end if
Click to expand...
Me.frmHeaterStock.LinkChildFields = stLinkChild
Me.frmHeaterStock.LinkMasterFields = stLinkMaster
Click to expand...
end function
I have the below function that is called out when a user checks
one of 4 boxes on the main form. There has got to be a better way
Click to expand...
[quoted text clipped - 179 lines]
End Function
Click to expand...
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
Click to expand...

If that's what you want, then you don't need any code at all.
 
Top