Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

If/Then Combo?

Discussion in 'Information Technology' started by Tammy, Jul 28, 2006.

  1. Tammy

    Tammy
    Expand Collapse
    Guest

    How do I tie a combo box selection to another combo box? FOr example, if a
    user selects a particular customer, I want the other field/combo box to
    populate with the name of the customer service rep. Like, if K-Mart
    selected, then Sally is automatically selected from the other combo.

    Also, how would I tie the date stamp "closed field" to populate when the
    user selects "closed" from the combo box?
    Thanks,
    Tammy
     
  2. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tammy" <Tammy@discussions.microsoft.com> wrote in message
    news:486D2D71-B002-4E5A-8D1B-0F306C28EABB@microsoft.com
    > How do I tie a combo box selection to another combo box? FOr
    > example, if a user selects a particular customer, I want the other
    > field/combo box to populate with the name of the customer service
    > rep. Like, if K-Mart selected, then Sally is automatically selected
    > from the other combo.


    Can I take it that it should still be possible to select a different
    customer service rep, after you've auto-selected Sally? Otherwise, if
    the customer service rep is wholly determined by the choice of customer,
    I don't see why you'd use a combo box to display it.

    In principle, you can do what you ask using code in the AfterUpdate
    event of the first (customer) combo box, along these lines:

    Private Sub cboCustomer_AfterUpdate()

    If Me!cboCustomer = "K-Mart" Then
    Me!cboCustSvcRep = "Sally"
    End If

    End Sub

    But that's very rudimentary. Most likely there's some way other than
    hard-coding the literals that you can look up and assign the appropriate
    rep for the customer. If, that is, you need to do it at all -- if it's
    wholly determined by the customer, there are ways to get the form's
    recordsource query to "autolookup" for you, or else you can pull it out
    of a hidden column in cboCustomer.

    > Also, how would I tie the date stamp "closed field" to populate when
    > the user selects "closed" from the combo box?


    Similarly, you make an assignment in the combo's AfterUpdate event.
    E.g.,

    Private Sub cboStatus_AfterUpdate()

    If Me!cboStatus = "Closed" Then
    Me!DateClosed = Date
    End If

    End Sub

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  3. Tammy

    Tammy
    Expand Collapse
    Guest

    Thanks & yikes - i'd have to do that for each of my 225 customers and 25
    customer service reps? And it doesn't always have to be "Sally" that
    represents K-Mart because Sally might be absent one day? Any suggestions?

    "Dirk Goldgar" wrote:

    > "Tammy" <Tammy@discussions.microsoft.com> wrote in message
    > news:486D2D71-B002-4E5A-8D1B-0F306C28EABB@microsoft.com
    > > How do I tie a combo box selection to another combo box? FOr
    > > example, if a user selects a particular customer, I want the other
    > > field/combo box to populate with the name of the customer service
    > > rep. Like, if K-Mart selected, then Sally is automatically selected
    > > from the other combo.

    >
    > Can I take it that it should still be possible to select a different
    > customer service rep, after you've auto-selected Sally? Otherwise, if
    > the customer service rep is wholly determined by the choice of customer,
    > I don't see why you'd use a combo box to display it.
    >
    > In principle, you can do what you ask using code in the AfterUpdate
    > event of the first (customer) combo box, along these lines:
    >
    > Private Sub cboCustomer_AfterUpdate()
    >
    > If Me!cboCustomer = "K-Mart" Then
    > Me!cboCustSvcRep = "Sally"
    > End If
    >
    > End Sub
    >
    > But that's very rudimentary. Most likely there's some way other than
    > hard-coding the literals that you can look up and assign the appropriate
    > rep for the customer. If, that is, you need to do it at all -- if it's
    > wholly determined by the customer, there are ways to get the form's
    > recordsource query to "autolookup" for you, or else you can pull it out
    > of a hidden column in cboCustomer.
    >
    > > Also, how would I tie the date stamp "closed field" to populate when
    > > the user selects "closed" from the combo box?

    >
    > Similarly, you make an assignment in the combo's AfterUpdate event.
    > E.g.,
    >
    > Private Sub cboStatus_AfterUpdate()
    >
    > If Me!cboStatus = "Closed" Then
    > Me!DateClosed = Date
    > End If
    >
    > End Sub
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  4. Tammy

    Tammy
    Expand Collapse
    Guest

    Dear Dirk - regarding the status/closed fields. In the status fields after
    update event, I opened the code builder and I typed exactly:
    Private Sub cboStatus_AfterUpdate()

    If Me!cboStatus = "Closed" Then
    Me!DateClosed = Date
    End If

    End Sub
    However the CloseDate field did not update. Any suggestions? Thank you
    VERY much.
    Tammy

    However the

    "Tammy" wrote:

    > Thanks & yikes - i'd have to do that for each of my 225 customers and 25
    > customer service reps? And it doesn't always have to be "Sally" that
    > represents K-Mart because Sally might be absent one day? Any suggestions?
    >
    > "Dirk Goldgar" wrote:
    >
    > > "Tammy" <Tammy@discussions.microsoft.com> wrote in message
    > > news:486D2D71-B002-4E5A-8D1B-0F306C28EABB@microsoft.com
    > > > How do I tie a combo box selection to another combo box? FOr
    > > > example, if a user selects a particular customer, I want the other
    > > > field/combo box to populate with the name of the customer service
    > > > rep. Like, if K-Mart selected, then Sally is automatically selected
    > > > from the other combo.

    > >
    > > Can I take it that it should still be possible to select a different
    > > customer service rep, after you've auto-selected Sally? Otherwise, if
    > > the customer service rep is wholly determined by the choice of customer,
    > > I don't see why you'd use a combo box to display it.
    > >
    > > In principle, you can do what you ask using code in the AfterUpdate
    > > event of the first (customer) combo box, along these lines:
    > >
    > > Private Sub cboCustomer_AfterUpdate()
    > >
    > > If Me!cboCustomer = "K-Mart" Then
    > > Me!cboCustSvcRep = "Sally"
    > > End If
    > >
    > > End Sub
    > >
    > > But that's very rudimentary. Most likely there's some way other than
    > > hard-coding the literals that you can look up and assign the appropriate
    > > rep for the customer. If, that is, you need to do it at all -- if it's
    > > wholly determined by the customer, there are ways to get the form's
    > > recordsource query to "autolookup" for you, or else you can pull it out
    > > of a hidden column in cboCustomer.
    > >
    > > > Also, how would I tie the date stamp "closed field" to populate when
    > > > the user selects "closed" from the combo box?

    > >
    > > Similarly, you make an assignment in the combo's AfterUpdate event.
    > > E.g.,
    > >
    > > Private Sub cboStatus_AfterUpdate()
    > >
    > > If Me!cboStatus = "Closed" Then
    > > Me!DateClosed = Date
    > > End If
    > >
    > > End Sub
    > >
    > > --
    > > Dirk Goldgar, MS Access MVP
    > > www.datagnostics.com
    > >
    > > (please reply to the newsgroup)
    > >
    > >
    > >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tammy" <Tammy@discussions.microsoft.com> wrote in message
    news:B19B9D64-81AA-474E-B8C0-204E91D2BCC7@microsoft.com
    > Thanks & yikes - i'd have to do that for each of my 225 customers and
    > 25 customer service reps? And it doesn't always have to be "Sally"
    > that represents K-Mart because Sally might be absent one day? Any
    > suggestions?


    That's why I said there ought to be some way to determine by lookup who
    is the rep for a particular customer. But you don't give us much
    information about your form, your table structure, or the real-world
    entities that your database is representing, and what this form is
    supposed to do.

    Probably you have a table of customers and a table of customer-service
    reps. And probably you have a field in the Customers table that
    identifies the normal customer-service rep for this customer (if there's
    only one). So on the form, you can set up the customer combo, which I'm
    calling "cboCustomer", so that it also holds the ID of the default
    service rep for that customer. You'd do this by including an extra
    column in the combo box, and setting the rowsource of the combo box to
    pull that from the table. For example, you might have these tables:

    Customers
    --------------
    CustomerID (autonumber, primary key)
    CustomerName (text)
    ServiceRepID (long integer, foreign key to ServiceReps)
    (other fields ...)

    ServiceReps
    ----------------
    ServiceRepID (autonumber, primary key)
    ServiceRepName (text) (or first, middle, last name fields)
    (other fields ...)

    Then on your form, cboCustomer would have these properties:

    cboCustomer
    -----------------
    ColumnCount: 3
    ColumnWidths: 0";1.5";0"
    RowSource:
    SELECT CustomerID, CustomerName, ServiceRepID
    FROM Customers ORDER BY CustomerName;

    The other combo, cboServiceRep, might have these properties:

    cboServiceRep
    --------------------
    ColumnCount: 2
    ColumnWidths: 0";1.5"
    RowSource:
    SELECT ServiceRepID, ServiceRepName
    FROM ServiceReps ORDER BY ServiceRepName;

    Now, with this setup, your code in the AfterUpdate procedure of
    cboCustomer could look like this:

    '----- start of example code -----
    Private Sub cboCustomer_AfterUpdate()

    Me!cboCustSvcRep = Me!cboCustomer.Column(2)
    ' Note that .Column(2) is the third column of the combo box.

    End Sub
    '----- end of example code -----

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Tammy" <Tammy@discussions.microsoft.com> wrote in message
    news:72C11311-ADB3-4391-9A4A-FDFA7091434C@microsoft.com
    > Dear Dirk - regarding the status/closed fields. In the status fields
    > after update event, I opened the code builder and I typed exactly:
    > Private Sub cboStatus_AfterUpdate()
    >
    > If Me!cboStatus = "Closed" Then
    > Me!DateClosed = Date
    > End If
    >
    > End Sub
    > However the CloseDate field did not update. Any suggestions? Thank
    > you VERY much.


    Did you check the exact names of all the controls and fields involved,
    and adjust the code appropriately? What values are actually stored in
    the bound column of the status combo box (whatever its name is)? Maybe
    the word "Closed" isn't the value, even if that is the displayed column.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page