Populating Database from field Selection

[Guest]

I have an access database for IT trouble tickets. I have a table for the
trouble tickets themselves and an additional table that lists all our
customers so that the techs can pick the customer via a drop down box in the
trouble ticket form I created. My boss has asked for a field that would
show status of the customer's maintenance contract with us and the expiration
period of that maintenance contract.

I have added a maintenance status and expiration period field to the
customer table. Is there a way for me to get the maintenance status and
expiration fields to pre-populate in the trouble ticket form when the tech
picks a certain customer. This way on every ticket opened, once the tech
picks a certain customer, their maintenance status and contract expiration
will automatically populate in the form.

Thanks,
Roxanne

 

[John Vinson]

I have an access database for IT trouble tickets. I have a table for the
trouble tickets themselves and an additional table that lists all our
customers so that the techs can pick the customer via a drop down box in the
trouble ticket form I created. My boss has asked for a field that would
show status of the customer's maintenance contract with us and the expiration
period of that maintenance contract.

I have added a maintenance status and expiration period field to the
customer table. Is there a way for me to get the maintenance status and
expiration fields to pre-populate in the trouble ticket form when the tech
picks a certain customer. This way on every ticket opened, once the tech
picks a certain customer, their maintenance status and contract expiration
will automatically populate in the form.

These fields should NOT be stored in the trouble ticket table - a
customer's status is an attribute of the customer, not of a trouble
ticket, so the Customer table should be the only place this info is
stored.

Just include the two fields in the RowSource query for the customer
combo box. You can use the ColumnWidths property to set the width of
these columns to zero, so they won't be visible.

Put two textboxes on the form with Control Source

=cboCustomer.Column(n)

where cboCustomer is the name of the combo box, and (n) the *zero
based* subscript of the field you want displayed - that is, if the
status is the third field in the combo's query, use (2).

John W. Vinson[MVP]

 

[Guest]

John,

I have the maintenance status and expiration fields in the customer table -
I didn't put them in the trouble ticket table.

In the customer table, I have these two fields set as text boxes - should
they be set as something else? Also, you have "include the two fields in the
RowSource query for the customer combo box" and "use the columnwidths
property to set the width of these columns to zero." What does this mean
exactly? I don't have a combo box in my Customer table.

Also, I understand adding two text boxes to my form, but don't understand
the (n) subscript of the field. I'm rather confused to the "combo's query"
you are referring to. Am I writing a query within the form?

Thank you for your help,
Roxanne

 

[John Vinson]

John,

I have the maintenance status and expiration fields in the customer table -
I didn't put them in the trouble ticket table.

Good... just checking to be sure!

In the customer table, I have these two fields set as text boxes - should
they be set as something else? Also, you have "include the two fields in the
RowSource query for the customer combo box" and "use the columnwidths
property to set the width of these columns to zero." What does this mean
exactly? I don't have a combo box in my Customer table.

I'd recommend NEVER using combo boxes in Tables. I'm talking about a
combo box on the Form. See below...

Also, I understand adding two text boxes to my form, but don't understand
the (n) subscript of the field. I'm rather confused to the "combo's query"
you are referring to. Am I writing a query within the form?

The Row Source property of the combo box on the Form should be a Query
selecting the customer information. I'm suggesting that that query
include the two fields which you want to see. For example, the
RowSource property of the combo could be

SELECT CustomerID, CustomerName, Status, ExpirationDate
FROM Customers
ORDER BY CustomerName;

The Combo's ColumnCount property would be 4 to include all fields; its
ColumnWidths property would be

0;1.5;0;0

to display the customer name and hide all the other fields. The Bound
Column would be 1 so that the CustomerID is bound to your trouble
ticket's CustomerID field.

A Combo Box has a "Column" property which lets you extract information
other than the bound column's value from the combo. If your combo is
named cboCustomer, then simply look at the Control Source property of
the textbox on the form in which you wish to display the status, and
type in

=cboCustomer.Column(2)

This will automatically display the third (zero based, remember) field
from the combo - the status, using the example above.

John W. Vinson[MVP]

 

[Guest]

John,

I understand now what you wrote below, but this doesn't accomplish what I'm
needing. Maybe re-explanation will help in understanding what I need to
happen.

When a new trouble ticket is open, the technician uses a drop down list of
customer names to select the customer for the trouble ticket. What I am
wanting is this:

Once the technician chooses a customer from the drop down list, is there a
way to automatically populate the maintenance status and maintenance
expiration into a box below that based on which customer the technician
chooses in the "customer" drop down list?

What you wrote below will input the columns from the customer table I have,
but it requires knowing the customerID for each customer (which the
technicians do not see - they only are provided with a drop down list, in
alphabetical order, of customers to choose from).

So, is it possible to automatically populate another combo, list, or text
box with the maintenance status once the technician chooses a customer from
the drop down list?

Thanks,

Roxanne