VBA code!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • zufie
    #1

    VBA code!

    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_AfterUp date()
    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_Clic k()
    ' 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





    Tags: None
    • KC-Mass
      #2
      Re: VBA code!

      Do you mind showing us the queries?


      "zufie" <john.marruffo@ illinois.govwro te in message
      news:988e6ec7-0004-4b79-9916-09b0c40d6066@k3 7g2000hsf.googl egroups.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_AfterUp date()
      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_Clic k()
      ' 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
      >
      >
      >
      >
      >

        Comment

        • zufie
          #3
          Re: VBA code!

          On Aug 29, 1:45 pm, "KC-Mass" <connearneyATco mcastDOTnetwrot e:
          Do you mind showing us the queries?
          >
          "zufie" <john.marru...@ illinois.govwro te in message
          >
          news:988e6ec7-0004-4b79-9916-09b0c40d6066@k3 7g2000hsf.googl egroups.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_AfterUp date()
          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_Clic k()
          ' 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.Addre ss,
          Agencies2.Addre ss2, Agencies2.City, Agencies2.State , Agencies2.Zip,
          Agencies2.[Opening Hour], Agencies2.[Closing Hour], Agencies2.WWWAD DR,
          Agencies2.EMAIL , Agencies2.Comme nts, Agencies2.Admin istrator, [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.Addre ss, Agencies2.Addre ss2,
          Agencies2.City, Agencies2.State , Agencies2.Zip, Agencies2.[Opening
          Hour], Agencies2.[Closing Hour], Agencies2.WWWAD DR, Agencies2.EMAIL ,
          Agencies2.Comme nts, Agencies2.Admin istrator
          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

            Comment

            • zufie
              #4
              Re: VBA code!

              On Aug 29, 1:45 pm, "KC-Mass" <connearneyATco mcastDOTnetwrot e:
              Do you mind showing us the queries?
              >
              "zufie" <john.marru...@ illinois.govwro te in message
              >
              news:988e6ec7-0004-4b79-9916-09b0c40d6066@k3 7g2000hsf.googl egroups.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_AfterUp date()
              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_Clic k()
              ' 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.Addre ss,
              Agencies2.Addre ss2, Agencies2.City, Agencies2.State , Agencies2.Zip,
              Agencies2.[Opening Hour], Agencies2.[Closing Hour], Agencies2.WWWAD DR,
              Agencies2.EMAIL , Agencies2.Comme nts, Agencies2.Admin istrator, [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.Addre ss, Agencies2.Addre ss2,
              Agencies2.City, Agencies2.State , Agencies2.Zip, Agencies2.[Opening
              Hour], Agencies2.[Closing Hour], Agencies2.WWWAD DR, Agencies2.EMAIL ,
              Agencies2.Comme nts, Agencies2.Admin istrator
              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

                Comment

                Previous template Next
                Working...