ComboBox Sort Question

[karassik]

I am not exactly sure how to go about this problem in Access 2000.

I have a form built on employee records, and a subform with hours, operation etc.

The operations are in a combobox fed from another table. I was wondering how I limit the operations combobox depending on the employee? Ie. Joe can have operations 1,2,3,7 whereas Sue has 2,5,7,9.

One thought was to have some sort of info stored in the employees table, but I don't know how I would store 2,5,7,9 etc. so I don't think this is the way to do it.

Thanks in advance,

Nishan

 

[scriverb]

Give this a try. Create the extra field in the Employees table(Operations) and store these selection parameters in this text field in the following format: 2,5,7,9 Make sure that the last digit has a comma after it.

Now create at the database class module level a global variable and a function with the following code:

Global vStrValues As String
Function StrValues()
StrValues = vStrValues
End Function

Create a control on your Employee form with the control source being this new field - Operations

When an employee is selection to be the active record on the form execute the following code:

vStrValues = me![Operations]

Now the following query should give you the idea for modifying your existing query to select only the records identified in the Operations field for the combobox.

SELECT Operations.Description, Operations.OperationsType
FROM Operations
WHERE (((InStr(1,StrValues(),CStr([Operations]![OperationsType]) & ",&quot)>0));

This query will select Operations record where the OperationsType value is included in the string of assigned Operations for the employee. The string of Operations codes from the Function call to StrValues. The Instr function looks for records from the Operations table where the string representation of the number is found somewhere in the full string of assigned operations types stored in the Employee table. The comma seperators and ending comma are needed to assure that you don't select a record(i.e. 1) by matching to a 12 where the first digit selects the record. It must find a 1, in the string to make a match. The instr function returns the position of the search string and if it is > 0 then select the record.

I hope that I have not totally confused you with this but give it a try because if works. Get back with me if you are having problems. I did assume that the Operations type in the Operations table was a numeric Integer type. Bob Scriver