Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Birds One Stone (2003)

    I have a form that our HR Generalists will be using to fill in data on phone calls that they receive from our field operations. It is important that they fill in the fields for Location and Operations Groups. I have setup combo box tha uses a pull down list for locations from a table to avoid spelling errors as much as possible. In the same table I also have a field for Ops Group. Is there a way to have the Ops Group field self populate based on the location selected from the pull down list?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Birds One Stone (2003)

    Is Ops Group completely determined by Location, or should the user be able to change the Ops Group field independently of Location?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Birds One Stone (2003)

    Ops group is determined by location. Each location belongs to only one Ops Group. It would be a very rare occasion that the User would need to enter anything for Ops group. The only instance I can think of would be when they selected Unknown for the location. They might then need to type in an Ops Group based on another criteria, such as they know the manager they are speaking with is assigned to Other Ops or Hawaii.

    But if they know the Location the Ops Group would be constant.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Birds One Stone (2003)

    Thom,
    If there aren't too many locations, you could use a select case statement, something like this
    <pre>Private Sub txtLocation_Exit()
    Select Case Me.txtLocation
    Case "Buffalo"
    Me.txtOpsGroup = "Group1"
    Case "Philadelphia"
    Me.txtOpsGroup = "Group2"
    'etc...you can continue filling this in.
    Case Else
    MsgBox "Please enter valid location", vbOkOnly
    Me.txtLocation = ""
    Me.txtLocation.SetFocus
    'Note that "Case Else" is optional
    End Select
    End Sub</pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Two Birds One Stone (2003)

    Weell

    There is a way of doing this Thom. I am assuming an Ops Group can be responsible for more than one location, so there is a onetoMany relationship of OpsGroup to Location.

    1) Create a table with a list of Ops Groups with a PK
    2) Create a table with a list of Locations with a PK and opsgroup as the FK
    3) Link the two PK to FK

    Update the tables so that you have a listing of the locations against each ops group.

    4) Create a query with a list of opsgroups and locations ( it will look like an unnuormalised table)

    For this example

    5) Create a form, add two combos; cboOps and cboLoc.
    6) Link the cboOps to the operations group table, cboLoc should be connected to the locations column in the query created. Save form give name say frmDrop.
    7) Return to query and make the criteria for opsgroups the name of the combo on frmDrop. Save
    8) Return to form, design view.
    9) to cboOps add this iece of VBA

    Private Sub cboOps_AfterUpdate()

    Me.Refresh

    End Sub

    10) save


    Now each time you change the combo for the ops department the drop down for location will change to the locations it is responsible for.
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two Birds One Stone (2003)

    OK, so Ops Group is not solely derived information (if it had been, there would have been no need for an Ops Group field in the table).

    - Include Ops Group in the Row Source of the combo box.
    - Increase the Column Count to accomodate the new column.
    - Use the Column Widths property to set the width for the new column to 0, effectively hiding it.
    - Create an After Update event procedure for the combo box:

    Private Sub Location_AfterUpdate()
    If IsNull(Me.Location) Then
    Me.[Ops Group] = Null
    Else
    Me.[Ops Group] = Me.Location.Column(1)
    End If
    End Sub

    - Substitute the correct names.
    - The column index starts counting at 0, so if Ops Group is the 3rd column in the combo box, use Column(2), etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •