Restrciting range of entry

[bartoki]

Hello,

Depending on the value of combo box #1, I want to restrict the range of text box #2.

Combo box #1 will reference a table; each row in that table will have, in addition to an ID and Name, a minimum value and a maximum value column.

Text box #2 will be limited by the values of the min and max columns.

I'm sure this is an easy question for all of you out there. Thanks in advance for the help.

-DK

 

[koresnordic]

Create a query using the control values from the form as criteria. Base the second control on this query ( I assume you mean that these are combo boxes or lists of some kind)



Graham

 

[bartoki]

I'm afraid I don't follow. I don't understand how the query will restrict the range of data a user can enter.

Here's the practical:
Tableerformance contains these fields
ID
Performance
Minimum Bonus Multiplier
Maximum Bonus Multiplier

The user, in this case a department manager, will rate each employee's performance by going to the Employees form and entering an integer from 1-5 in a text box. Then the manager will enter in the bonus multiplier, which has a specific range dependent on that employee's performance, into another box. This multiplier will be a number with one decimal (x.x).

Thanks again.

 

[koresnordic]

OK, sorry misunderstood your question (Doh!). how is the relationship setup for the score and bonus? Is it a case of 1 = 1.05 to 1.15, 2 = 1.15 to 1.30 etc. Is there a definable calculation involved? e.g. is the start always 1 with the min value being input*.05 and the max input *.3? It would help if you could supply the ranges allowed with the 5 values.



Graham

 

[bartoki]

Unfortuntely those values will change each year, so I am not able to hard code it in. This is how I've sketched it out so far...(table names with fields and descriptions).

Performance Rating Table
___________________________
Performance Rating ID: autonumber
Individual Performance Rating Name: masked value (a0)
Base Multiplier: numeric part of rating name
Minimum Multiplier: masked number
Maximum Multiplier: masked number
Company Performance Rating: masked number


Performance Table
__________________________
Performance ID: autonumber
Individual Performance Rating: (TableerformanceRatingerfrate)
Bonus Multiplier: masked; restricted range by (TableerformanceRating:minmult,maxmult).

So...Performance Rating table is populated by 25 entries. These entries are a combination of job code (alpha value) and performance rating (numeric value). For each five job codes there are 5 possible performance ratings. 25 total entries.

• Each entry in this Performance Rating table will simply have the corresponding numeric value in the field next to it (the name without the alpha part).
• It will also have the minimum and maximum multiplier values in two fields.
• It will also have the company performance rating multiplier value in another field.

The values for the min multiplier, max multiplier and company multiplier will change every year.

Depending on the value the user chooses from the combo box Formerformance:comboPerf, it will restrict the values he can enter in Formerformance:comboMult. Formerformance will be controlling Tableerformance.

Thanks again for your attention.

 

[koresnordic]

Sorry for the delay in getting back to you.
Lets see if I have got this right. The user enters the job code and performance rating and then this selects which range of bonus is allowed?

OK, on the exit event of the bonus box, try the following:

upper = DLookup("[max]", "performance rating table", "[company performance rating] = '" & Me.[dept] & "' & [rating] = '" & Me.[perform] & "'&quot
lower = DLookup("[min]", "performance rating table", "[company performance rating] = '" & Me.[dept] & "' & [rating] = '" & Me.[perform] & "'&quot

If Me.bonus > upper Then MsgBox ("To high - Maximum is " & upper)
If Me.bonus < lower Then MsgBox (&quot;To low - Minimum is &quot; & lower)

Then in the close event of the form:

upper = DLookup(&quot;[max]&quot;, &quot;performance rating table&quot;, &quot;[company performance rating] = '&quot; & Me.[dept] & &quot;' & [rating] = '&quot; & Me.[perform] & &quot;'&quot
lower = DLookup(&quot;[min]&quot;, &quot;performance rating table&quot;, &quot;[company performance rating] = '&quot; & Me.[dept] & &quot;' & [rating] = '&quot; & Me.[perform] & &quot;'&quot

If Me.bonus > upper Then MsgBox (&quot;To high - Maximum is &quot; & upper)
If Me.bonus < lower Then MsgBox (&quot;To low - Minimum is &quot; & lower)
If Me.bonus <= upper And Me.bonus >= lower Then Close

Replace [max] with the field name for your maximum value.
Replace [min] with the field name for your minimum value.
Replace [company per..] & [dept] with the table name holding the allowable values.
Replace [dept] with the field that represents the job code.
replace [rating] & [perform] with whatever represents the job performance rating.
and finally replace [bonus] with whatever the bonus entry field is on your form.

I hope you understad what I am getting at here. If not, let me know and I will try something else.



Graham

 

[bartoki]

Thank you so much for the response. I think I was able to disect that suggestion. I have not yet begun creating my forms, so I probably won't be able to test that out until tomorrow. But when I do, I'll post my results here. Hope ya got this one flagged!

But, from what you said, it sounds like there is no way to restrict the range from the table end. It has to be done from the Form end. Correct?

Thanks once again.
-DK

 

[koresnordic]

That is the only way I know - but I don't know everything. There may be a way, but this works for me.



Graham

 

[bartoki]

Well, it actually looks like I won't have the opportunity to implement this feature for the next few days (I need to get a few other problems out of the way before I'm able to tackle this one).

So, while I still have you here (thankfully), if you could help me take a deeper look at the third argument. And this isn't because you were unclear, it's because I'm not very adept.

First and second arguments make sense; find the field [maxMult] from table &quot;PerformanceRatings&quot;

But on the third argument we're using different labels, and my inadequate knowledge of access isn't enough to fill in the blanks. And, also, I dont think DLookup allows the reference to a field outside of the table listed in argument 2.

Let me more explicitly say how things are set up.
•Form is controlling Table:Bonus
•In Table:Bonus there is a reference to employeeID, connecting it to Table:Employees.
•In Table:Bonus there is a reference to perfrateID, connecting it to TableerformanceRatings (an array of 25 possible ratings based on the combination of 5 alpha parts and 5 numeric parts)
•In Table:Employees, there is a job code field, giving each employee a job code (the alpha part)

On Form:Bonus, user selects which employee to modify.
On Form:Bonus, user enters in their performance rating.

So, the third argument should first limit by the alpha part that is associated with the selected employee. Bringing the list down to 5. Then it should limit by the value the user enters in the form (and is controlling the performance rating field in Table:Bonus).

But, again, I don't think this is possible using this method because I want to limit the DLookup of TableerformanceRating by both Table:Employees:jobCode and Table:BonuserfRating.

Hmmm...any thoughts?

Thanks again!
-DK