By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

VBA code!

P: n/a
I am trying to fix the following VBA code from an MS Access database I
inherited.

I conclude with an example of one of the symptoms resulting from this
VBA code:

Option Compare Database
Option Explicit
Private Sub Check13_AfterUpdate()
If Me![Check13] Then
Me![Combo3].Enabled = False
Me![Combo9].Enabled = False
Me![Chicago Zip].Enabled = True
Else
Me![Combo3].Enabled = True
Me![Combo9].Enabled = True
Me![Chicago Zip].Enabled = False
End If
End Sub
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 have two drop down boxes, for the fields "County" and "Category" on
a form which when selected for the respective County and Category
will
return Agency Names of Agencies within the respective County and
Category.

For example, when I select Cook County and Cardiovascular disease I
expect the query(ies) behind the form to return Names of Agencies in
Cook County that provide services regarding Cardiovascular care.

However, what is returned are Names of Agencies outside of the county
and category selected.

Any advice on where to begin?

Thank you!,

John

Aug 29 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Do you mind showing us the queries?
"zufie" <jo***********@illinois.govwrote in message
news:98**********************************@k37g2000 hsf.googlegroups.com...
>I am trying to fix the following VBA code from an MS Access database I
inherited.

I conclude with an example of one of the symptoms resulting from this
VBA code:

Option Compare Database
Option Explicit
Private Sub Check13_AfterUpdate()
If Me![Check13] Then
Me![Combo3].Enabled = False
Me![Combo9].Enabled = False
Me![Chicago Zip].Enabled = True
Else
Me![Combo3].Enabled = True
Me![Combo9].Enabled = True
Me![Chicago Zip].Enabled = False
End If
End Sub
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 have two drop down boxes, for the fields "County" and "Category" on
a form which when selected for the respective County and Category
will
return Agency Names of Agencies within the respective County and
Category.

For example, when I select Cook County and Cardiovascular disease I
expect the query(ies) behind the form to return Names of Agencies in
Cook County that provide services regarding Cardiovascular care.

However, what is returned are Names of Agencies outside of the county
and category selected.

Any advice on where to begin?

Thank you!,

John



Aug 29 '08 #2

P: n/a
On Aug 29, 1:45*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:
Do you mind showing us the queries?

"zufie" <john.marru...@illinois.govwrote in message

news:98**********************************@k37g2000 hsf.googlegroups.com...
I am trying to fix the following VBA code from an MS Access database I
inherited.
I conclude with an example of one of the symptoms resulting from this
VBA code:
Option Compare Database
Option Explicit
Private Sub Check13_AfterUpdate()
If Me![Check13] Then
*Me![Combo3].Enabled = False
*Me![Combo9].Enabled = False
*Me![Chicago Zip].Enabled = True
Else
*Me![Combo3].Enabled = True
*Me![Combo9].Enabled = True
*Me![Chicago Zip].Enabled = False
End If
End Sub
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 have two drop down boxes, for the fields "County" and "Category" on
a form which when selected for the respective County and Category
will
return Agency Names of Agencies within the respective County and
Category.
For example, when I select Cook County and Cardiovascular disease I
expect the query(ies) behind the form to return Names of Agencies in
Cook County that provide services regarding Cardiovascular care.
However, what is returned are Names of Agencies outside of the county
and category selected.
Any advice on where to begin?
Thank you!,
John- Hide quoted text -

- Show quoted text -
Sure!

Here is qry Agencies by Zip:

SELECT DISTINCTROW Agencies2.ID, Agencies2.Name, Agencies2.Address,
Agencies2.Address2, Agencies2.City, Agencies2.State, Agencies2.Zip,
Agencies2.[Opening Hour], Agencies2.[Closing Hour], Agencies2.WWWADDR,
Agencies2.EMAIL, Agencies2.Comments, Agencies2.Administrator, [Chicago
Zips serviced].[Agency ID], [Chicago Zips serviced].[Chicago Zip ID]
FROM [Report Options], Agencies2 INNER JOIN [Chicago Zips serviced] ON
Agencies2.ID = [Chicago Zips serviced].[Agency ID]
WHERE ((([Chicago Zips serviced].[Chicago Zip ID])=[rZip]));
Here is qry Agencies by Category:

SELECT DISTINCTROW Agencies2.ID, Agencies2.Name, [Category Groups].
[Category No], [Category Groups].[Agency No], [Category
Groups].AgencyNo2, Agencies2.Address, Agencies2.Address2,
Agencies2.City, Agencies2.State, Agencies2.Zip, Agencies2.[Opening
Hour], Agencies2.[Closing Hour], Agencies2.WWWADDR, Agencies2.EMAIL,
Agencies2.Comments, Agencies2.Administrator
FROM [Report Options], Agencies2 INNER JOIN [Category Groups] ON
Agencies2.ID = [Category Groups].AgencyNo2
WHERE ((([Category Groups].[Category No])=[rCategory]));
Here is qry Agencies by Category and Zip:

SELECT DISTINCTROW [qry Agencies by Category].ID, [qry Agencies by
Category].Name, [qry Agencies by Category].[Category No], [qry
Agencies by Category].AgencyNo2, [qry Agencies by Category].Address,
[qry Agencies by Category].Address2, [qry Agencies by Category].City,
[qry Agencies by Category].State, [qry Agencies by Category].Zip, [qry
Agencies by Category].[Opening Hour], [qry Agencies by Category].
[Closing Hour], [qry Agencies by Category].WWWADDR, [qry Agencies by
Category].EMAIL, [qry Agencies by Category].Comments, [qry Agencies by
Category].Administrator, [Chicago Zips serviced].[Agency ID], [Chicago
Zips serviced].[Chicago Zip ID]
FROM [Report Options], [Chicago Zips serviced] INNER JOIN [qry
Agencies by Category] ON [Chicago Zips serviced].[Agency ID] = [qry
Agencies by Category].ID
WHERE ((([Chicago Zips serviced].[Chicago Zip ID])=[rZip]));
Here is qry Agencies by County:

SELECT DISTINCTROW [qry Agencies generic].ID, [qry Agencies
generic].Name, [qry Agencies generic].Address, [qry Agencies
generic].Address2, [qry Agencies generic].City, [qry Agencies
generic].State, [qry Agencies generic].Zip, [qry Agencies generic].
[Opening Hour], [qry Agencies generic].[Closing Hour], [qry Agencies
generic].WWWADDR, [qry Agencies generic].EMAIL, [qry Agencies
generic].Comments, [qry Agencies generic].Administrator, Counties.
[County Code ID2], Counties.County
FROM [Report Options], Counties INNER JOIN ([qry Agencies generic]
INNER JOIN [Agency County Link] ON [qry Agencies generic].ID = [Agency
County Link].Agencies2_ID) ON Counties.[County Code ID2] = [Agency
County Link].[County Code ID3]
WHERE (((Counties.[County Code ID2])=[rCounty]));
Here is qry Agencies by Category and County:

SELECT DISTINCTROW [qry Agencies by Category].ID, [qry Agencies by
Category].Name, [qry Agencies by Category].[Category No], [qry
Agencies by Category].AgencyNo2, [qry Agencies by Category].Address,
[qry Agencies by Category].Address2, [qry Agencies by Category].City,
[qry Agencies by Category].State, [qry Agencies by Category].Zip, [qry
Agencies by Category].[Opening Hour], [qry Agencies by Category].
[Closing Hour], [qry Agencies by Category].WWWADDR, [qry Agencies by
Category].EMAIL, [qry Agencies by Category].Comments, [qry Agencies by
Category].Administrator
FROM [Report Options], [Agency County Link] INNER JOIN [qry Agencies
by Category] ON [Agency County Link].Agencies2_ID = [qry Agencies by
Category].ID
WHERE ((([Agency County Link].[County Code ID3])=[rCounty]));

These are all of the queries.

Thanks,

John
Aug 29 '08 #3

P: n/a
On Aug 29, 1:45*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:
Do you mind showing us the queries?

"zufie" <john.marru...@illinois.govwrote in message

news:98**********************************@k37g2000 hsf.googlegroups.com...
I am trying to fix the following VBA code from an MS Access database I
inherited.
I conclude with an example of one of the symptoms resulting from this
VBA code:
Option Compare Database
Option Explicit
Private Sub Check13_AfterUpdate()
If Me![Check13] Then
*Me![Combo3].Enabled = False
*Me![Combo9].Enabled = False
*Me![Chicago Zip].Enabled = True
Else
*Me![Combo3].Enabled = True
*Me![Combo9].Enabled = True
*Me![Chicago Zip].Enabled = False
End If
End Sub
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 have two drop down boxes, for the fields "County" and "Category" on
a form which when selected for the respective County and Category
will
return Agency Names of Agencies within the respective County and
Category.
For example, when I select Cook County and Cardiovascular disease I
expect the query(ies) behind the form to return Names of Agencies in
Cook County that provide services regarding Cardiovascular care.
However, what is returned are Names of Agencies outside of the county
and category selected.
Any advice on where to begin?
Thank you!,
John- Hide quoted text -

- Show quoted text -
Sure!

Here is qry Agencies by Zip:

SELECT DISTINCTROW Agencies2.ID, Agencies2.Name, Agencies2.Address,
Agencies2.Address2, Agencies2.City, Agencies2.State, Agencies2.Zip,
Agencies2.[Opening Hour], Agencies2.[Closing Hour], Agencies2.WWWADDR,
Agencies2.EMAIL, Agencies2.Comments, Agencies2.Administrator, [Chicago
Zips serviced].[Agency ID], [Chicago Zips serviced].[Chicago Zip ID]
FROM [Report Options], Agencies2 INNER JOIN [Chicago Zips serviced] ON
Agencies2.ID = [Chicago Zips serviced].[Agency ID]
WHERE ((([Chicago Zips serviced].[Chicago Zip ID])=[rZip]));
Here is qry Agencies by Category:

SELECT DISTINCTROW Agencies2.ID, Agencies2.Name, [Category Groups].
[Category No], [Category Groups].[Agency No], [Category
Groups].AgencyNo2, Agencies2.Address, Agencies2.Address2,
Agencies2.City, Agencies2.State, Agencies2.Zip, Agencies2.[Opening
Hour], Agencies2.[Closing Hour], Agencies2.WWWADDR, Agencies2.EMAIL,
Agencies2.Comments, Agencies2.Administrator
FROM [Report Options], Agencies2 INNER JOIN [Category Groups] ON
Agencies2.ID = [Category Groups].AgencyNo2
WHERE ((([Category Groups].[Category No])=[rCategory]));
Here is qry Agencies by Category and Zip:

SELECT DISTINCTROW [qry Agencies by Category].ID, [qry Agencies by
Category].Name, [qry Agencies by Category].[Category No], [qry
Agencies by Category].AgencyNo2, [qry Agencies by Category].Address,
[qry Agencies by Category].Address2, [qry Agencies by Category].City,
[qry Agencies by Category].State, [qry Agencies by Category].Zip, [qry
Agencies by Category].[Opening Hour], [qry Agencies by Category].
[Closing Hour], [qry Agencies by Category].WWWADDR, [qry Agencies by
Category].EMAIL, [qry Agencies by Category].Comments, [qry Agencies by
Category].Administrator, [Chicago Zips serviced].[Agency ID], [Chicago
Zips serviced].[Chicago Zip ID]
FROM [Report Options], [Chicago Zips serviced] INNER JOIN [qry
Agencies by Category] ON [Chicago Zips serviced].[Agency ID] = [qry
Agencies by Category].ID
WHERE ((([Chicago Zips serviced].[Chicago Zip ID])=[rZip]));
Here is qry Agencies by County:

SELECT DISTINCTROW [qry Agencies generic].ID, [qry Agencies
generic].Name, [qry Agencies generic].Address, [qry Agencies
generic].Address2, [qry Agencies generic].City, [qry Agencies
generic].State, [qry Agencies generic].Zip, [qry Agencies generic].
[Opening Hour], [qry Agencies generic].[Closing Hour], [qry Agencies
generic].WWWADDR, [qry Agencies generic].EMAIL, [qry Agencies
generic].Comments, [qry Agencies generic].Administrator, Counties.
[County Code ID2], Counties.County
FROM [Report Options], Counties INNER JOIN ([qry Agencies generic]
INNER JOIN [Agency County Link] ON [qry Agencies generic].ID = [Agency
County Link].Agencies2_ID) ON Counties.[County Code ID2] = [Agency
County Link].[County Code ID3]
WHERE (((Counties.[County Code ID2])=[rCounty]));
Here is qry Agencies by Category and County:

SELECT DISTINCTROW [qry Agencies by Category].ID, [qry Agencies by
Category].Name, [qry Agencies by Category].[Category No], [qry
Agencies by Category].AgencyNo2, [qry Agencies by Category].Address,
[qry Agencies by Category].Address2, [qry Agencies by Category].City,
[qry Agencies by Category].State, [qry Agencies by Category].Zip, [qry
Agencies by Category].[Opening Hour], [qry Agencies by Category].
[Closing Hour], [qry Agencies by Category].WWWADDR, [qry Agencies by
Category].EMAIL, [qry Agencies by Category].Comments, [qry Agencies by
Category].Administrator
FROM [Report Options], [Agency County Link] INNER JOIN [qry Agencies
by Category] ON [Agency County Link].Agencies2_ID = [qry Agencies by
Category].ID
WHERE ((([Agency County Link].[County Code ID3])=[rCounty]));

These are all of the queries.

Thanks,

John
Aug 29 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.