I have a Toggle Button on a form and it is to return those agencies
that are located within a particular county and that deal with a
particular category (such as Health Department), both are selected
from their respective combo boxes. The problem is that the toggle box
incorrectly returns those agencies with the particular county selected
OR those agencies with the with a particular category selected. That
is, instead of correctly returning those agencies possessing both
particular county and category selected.


I am not fully sure of the meaning of the following VBA code for the
Toggle Button on form Super: Agency Lookup. The [Event Procedure]
under On Click contains the VBA code listed below. The Toggle Button
searches for the County (Combo3), and for the Category (Combo9)
selected in the respective combo boxes. The row source for the County
(Combo3) is, SELECT DISTINCTROW Counties.[County Code ID2],
Counties.County FROM Counties ORDER BY Counties.County; . The row
source for the Category (Combo9) is, SELECT DISTINCTROW Categories.
[Category ID], Categories.[Category Heading] FROM Categories ORDER BY
Categories.[Category Heading]; . There is a Check Box (Check13) to
the left of the Toggle Button. Check Box (Check13) has a default
value of =False, and limits the search to Chicago zip code. My limited
understanding of the code is: If new record or (Check13), (Combo3),
and (Combo9) are Null OR (Check13), [Chicago Zip], and (Combo9) are
Null then DataEntry is possible.
If DataEntry is not possible and if (Check13) is selected and
(Combo9) is Null then the RowSource = "qry Agencies by Zip" for the
Subform Agency Lookup. If not, and [Chicago Zip] is Null then the
Subform Agency Lookup by Category", , , " is opened, otherwise Subform
Agency Lookup by Zip Category", , , " is opened. If DataEntry is not
possible and If (Combo9) is Null then Subform: Agency Lookup by
County", , , " is opened. If (Combo3) is Null then "Subform: Agency
Lookup by Category", , , "" is opened, otherwise
"Subform: Agency Lookup by County Category", , , "" is opened.

The [Event Procedure] under On Click contains the following VBA code:


Private Sub ToggleLink_Click()
' Me![Combo3] County
' Me![Combo9] Category
Me.Requery
If Me.NewRecord Or (Me![Check13] And IsNull(Me![Combo3]) And IsNull(Me!
[Combo9])) Or _
(Me![Check13] And IsNull(Me![Chicago Zip]) And
IsNull(Me![Combo9])) Then
Forms![Sub: Agency Lookup].DataEntry = True
Else
If Me![Check13] Then
If IsNull(Me![Combo9]) Then
Forms![Sub: Agency Lookup].RowSource = "qry Agencies by Zip"
ElseIf IsNull(Me![Chicago Zip]) Then
DoCmd.OpenForm "Sub: Agency Lookup by Category", , , ""
Else
DoCmd.OpenForm "Sub: Agency Lookup by Zip Category", , , ""
End If
Else
If IsNull(Me![Combo9]) Then
DoCmd.OpenForm "Sub: Agency Lookup by County", , , ""
ElseIf IsNull(Me![Combo3]) Then
DoCmd.OpenForm "Sub: Agency Lookup by Category", , , ""
Else
DoCmd.OpenForm "Sub: Agency Lookup by County Category", , , ""
End If
End If
End If
End Sub

I suspect that the Toggle Button’s search also includes the results
from Agency County Link subform which has a row source of SELECT
DISTINCTROW [Agency County Link].[Agency ID], [Agency County Link].
[County Code ID3], [Agency County Link].[Agencies2_ID] FROM [Agency
County Link]; and also results from Category Groups subform new
which has a row source of SELECT DISTINCTROW [Category Groups].
[Category No], [Category Groups].[AgencyNo2] FROM [Category
Groups]; .

Any ideas you have would be appreciated immensely ,

John

zufie wrote:

I would recommend you put the word
STOP
at the top of the event and step thru the code.