DEPENDENT COMBO BOXES FROM LOOKUP COLUMN FIELDS?

[gyli84]

I am creating a Helpdesk system whereby helpdesk operators receive calls from people with problems regarding their PCs and this is stored in a database. As part of it there will be a table such as "PROBLEM DETAILS" which will store the information as to problems received by the helpdesk operators. This table will store fields such as Staff Number, Staff Extension and a Description of the Problem that a member of staff is having. I would like the helpdesk operator to be able to narrow down the PROBLEM DESCRIPTION by having 3 dependent combo boxes with set values in each such that for example from the first problem description combo box you would be able to choose HARDWARE amongst other things (such as SOFTWARE/SERVER etc.) which would limit the choices in the second etc. So for example a problem may be narrowed down to and defined as HARDWARE>MONITOR>BROKEN SCREEN

I am able to create DEPENDENT combo boxes to DISPLAY data on a form by having a table for each combo box, creating a query that searches for the presence of records in the 2nd table using the value located in the first combo box (Criteria is [Forms]![FormName]![ComboBox1Name]) and then setting this query as the ROWSOURCE of the second combo box. For Example The data in ComboBox 1 may come from a table with fields PROBLEM TIER 1 ID, PROBLEM TIER 1, the table for the 2nd ComboBox will have PROBLEM TIER 1 ID, PROBLEM TIER 2 ID, PROBLEM TIER 2 (there will be many possible Tier 2 options stemming from Tier 1).

The problem however is that in order to create these dependent combo boxes they require their own tables to specify the links between them and thus each combo box. Rather than just being able to display tabulated data in dependent combo boxes I need a separate PROBLEM DETAILS table which will have have RECORDS as to the details of PROBLEMS logged by the helpdesk operators and this table will have the fields PROBLEM TIER 1, PROBLEM TIER 2, PROBLEM TIER 3 amongst other fields such as Staff Number etc. I don't think it is possible to create DEPENDENT combo boxes whereby these PROBLEM TIER fields are merely lookup column fields created by a lookup wizard as each combo box needs a table in order for RELATIONSHIPS between each succesive box to be made so that a query can create dependency (unless a lookup table with more than one column is created with the LOOKUP WIZARD in the TABLE DESIGN VIEW for each of the 3 Problem Tier fields, but then how can relationships be specified between the lookup table for each field?). The Helpdesk operators will input new records of PROBLEMS through a PROBLEM DETAILS FORM. As it might not be possible to create dependent combo boxes as lookup columns within the PROBLEM DETAILS TABLE; if I add 3 Dependent Problem Tier field Combo Boxes created from independent tables onto the PROBLEM DETAILS FORM created from the Problem Details Table I need some automated way (or way through use of a button) of copying the options chosen in these COMBO BOXES into the corresponding Problem Tier fields on the CURRENTLY DISPLAYED RECORD of the PROBLEM DETAILS TABLE perhaps through use of a macro (or is it possible to add the 3 Problem Tier fields from the PROBLEM DETAILS TABLE aswell-which will just be normal text fields-and linking them by some means to the 3 Dependent Problem Tier field combo boxes the Helpdesk operators will use such that they display whatever is chosen in the Combo Boxes and thus the data is stored in the PROBLEM DETAILS TABLE). The best solution would be to be able to create dependent combo boxes from lookup column fields created from a lookup wizard, but any help on the matter is welcome!

 

[paron]

I think I understand. I think. Let's say that you were going to put a city name in your Problem_Details field, instead of a problem report.

Picking a continent from the first combobox limits your choice of countries, which in turn limits your choice of cities. The user picks a city from the third box, which then goes into your Problem_details table field(s).

If that is about what you'd like to do, then I would probably just put all the user's options in one big table ("tblCities&quot, with each of the the cities having two descriptors -- like City "London", with descriptors Continent "North America" and Country "Canada" (that's London, Ontario).

The Rowsource of the first box is the list of unique Continents from tblCities, the choice of which limits the contents of the second box to those Countries that are paired up with that Continent in tblCities, and so on down to the City level.

Bind the third ComboBox to the Problem_details field you want to fill. The City unique ID gets stored in your Problem_details table. If you need to retrieve the Continent or Country, they're part of the same record in tblCities.

Hope that was some help. Hope I understood the question!

 

[shelti]

Can you globally delete hard carriage returns from text"

Using Find/Replace, what do you type in the Find box?