If/Then Combo?

[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

 

[Dirk Goldgar]

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

 

[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?

 

[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

 

[Dirk Goldgar]

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]

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.