Trying to do a specific if/then statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In one of my forms, I have associates enter in a resolution and the form
calculates a currency value based on other info entered in. What I am
looking for, is to have my form not allow a certain resolution to be selected
when the dollar amount is below a certain level in another field on the same
form.

My crude example:

Ex. If [DefectCost] > $99.99, then can not = ResolutionCode #6 in the
ResolutionCode field.

This is the only forum where I seem to find answers. Any help is appreciated.

Joe

This is the only place that I seem to get answers,
 
Some VBA Code with an event

private sub ResolutionCode_AfterUpdate()
If me.DefectCost > 99.99 then me.ResolutionCode.Undo
end sub

- Raoul
 
Joe, I take it you have a combo box of Resolution Codes and you want it to
disallow one particular resolution from being selected if the cost is over
$99.99. There are a number of approaches you can take. You can create a
subroutine to set multiple record sources of the combo box based on the
DefectCost value and call it from the form's OnCurrent event and again on
the DefectCost's AfterUpdate event. Or, you can leave the combo box list as
is and instead on the combo box's BeforeUpdate event set it to not accept a
value of 6 when the cost is over your limit and instead issue a msgbox with
a description of why it can't be used. The first method is cleaner, but
could necessitate creating many different record sources for every
exception, if you have more than the one exception in your example. Is your
combo box using a value/list or a lookup table/query to populate the
resolution codes? Also, your text specifies not allowing a resolution
below a set value while your pseudo code example specifies a greater than
value. Which is it?
2005 >>>
In one of my forms, I have associates enter in a resolution and the form
calculates a currency value based on other info entered in. What I am
looking for, is to have my form not allow a certain resolution to be
selected
when the dollar amount is below a certain level in another field on the same

form.

My crude example:

Ex. If [DefectCost] > $99.99, then can not = ResolutionCode #6 in the
ResolutionCode field.

This is the only forum where I seem to find answers. Any help is
appreciated.

Joe

This is the only place that I seem to get answers,
 
1. Actually there are 2 exceptions I need to account for:

if the value of DefectCost is GREATER THAN $100, then the ResolutionCode
from the lookup table should not be able to use the resolution "Scrap - less
than $100".

if the value of the DefectCost is LESS THAN $100, then the ResolutionCode
from the lookup table should not be able to use the resolution "Scrap - No
credit/No Rerun needed".

2. The combo box is a lookup table.

Thanks,

Joe

Ron Kunce said:
Joe, I take it you have a combo box of Resolution Codes and you want it to
disallow one particular resolution from being selected if the cost is over
$99.99. There are a number of approaches you can take. You can create a
subroutine to set multiple record sources of the combo box based on the
DefectCost value and call it from the form's OnCurrent event and again on
the DefectCost's AfterUpdate event. Or, you can leave the combo box list as
is and instead on the combo box's BeforeUpdate event set it to not accept a
value of 6 when the cost is over your limit and instead issue a msgbox with
a description of why it can't be used. The first method is cleaner, but
could necessitate creating many different record sources for every
exception, if you have more than the one exception in your example. Is your
combo box using a value/list or a lookup table/query to populate the
resolution codes? Also, your text specifies not allowing a resolution
below a set value while your pseudo code example specifies a greater than
value. Which is it?
2005 >>>
In one of my forms, I have associates enter in a resolution and the form
calculates a currency value based on other info entered in. What I am
looking for, is to have my form not allow a certain resolution to be
selected
when the dollar amount is below a certain level in another field on the same

form.

My crude example:

Ex. If [DefectCost] > $99.99, then can not = ResolutionCode #6 in the
ResolutionCode field.

This is the only forum where I seem to find answers. Any help is
appreciated.

Joe

This is the only place that I seem to get answers,
Click to expand...
 
I am running into a similar problem which is why I was looking through here.
I am trying to gray out fields if the SumofNRE field is < $5000. The
graying out part works, but it doesn't seem to have anything to do with the
total in the field. (If I use quotes, being I've tried everything I can
think of, then the fields stay grayed out. Without quotes, they never gray
out.) this is my code:

Private Sub Form_Current()
If Me.SumofNRE < 5000 Then
Me.[Director Approval].Enabled = False
Me.[Director Signature].Enabled = False
Me![Dir Current Date].Enabled = False
End If
End Sub


JaRa said:
Some VBA Code with an event

private sub ResolutionCode_AfterUpdate()
If me.DefectCost > 99.99 then me.ResolutionCode.Undo
end sub

- Raoul


JoeAccess381 said:
In one of my forms, I have associates enter in a resolution and the form
calculates a currency value based on other info entered in. What I am
looking for, is to have my form not allow a certain resolution to be selected
when the dollar amount is below a certain level in another field on the same
form.

My crude example:

Ex. If [DefectCost] > $99.99, then can not = ResolutionCode #6 in the
ResolutionCode field.

This is the only forum where I seem to find answers. Any help is appreciated.

Joe

This is the only place that I seem to get answers,
Click to expand...
Click to expand...
 
Sorry I didn't get back to you sooner. I am not a regular here and only
pop-in now and again. However, since there are only two conditions try
this:

In both the form's OnCurrent and the DefectCosts' AfterUpdate events you
should make a call to function similar to:

Private Function setReslCode() as Boolean
setReslCode = False
If me.txtDefectCosts >= 100 then
me.cboReslCode.RowSource = "SELECT * From tblResolutionCodes WHERE
ReslCode <> 6;"
ElseIf me.txtDefectCosts < 100 then
me.cboReslCode.RowSource = "SELECT * From tblResolutionCodes WHERE
ReslCode <> 3;"
ELse
me.cboReslCode.RowSource = "SELECT * From tblResolutionCodes;"
End If
setReslCode = True
End Function

Where, txtDefectCosts is the name of the Defect Costs control, cboReslCode
is the name of resolution codes combo box, tblResolutionCodes is the name of
source lookup table, ReslCode is the name of resolution code field in the
lookup table, and the integers 6 and 3 are the resolution code values you
want to exclude from the list. You can substitute a Select Case statement
for the If. . . ElseIf construct if you prefer.

2005 >>>
1. Actually there are 2 exceptions I need to account for:

if the value of DefectCost is GREATER THAN $100, then the ResolutionCode
from the lookup table should not be able to use the resolution "Scrap - less

than $100".

if the value of the DefectCost is LESS THAN $100, then the ResolutionCode
from the lookup table should not be able to use the resolution "Scrap - No
credit/No Rerun needed".

2. The combo box is a lookup table.

Thanks,

Joe

Ron Kunce said:
Joe, I take it you have a combo box of Resolution Codes and you want it to
disallow one particular resolution from being selected if the cost is over
$99.99. There are a number of approaches you can take. You can create a
subroutine to set multiple record sources of the combo box based on the
DefectCost value and call it from the form's OnCurrent event and again on
the DefectCost's AfterUpdate event. Or, you can leave the combo box list as
is and instead on the combo box's BeforeUpdate event set it to not accept a
value of 6 when the cost is over your limit and instead issue a msgbox with
a description of why it can't be used. The first method is cleaner, but
could necessitate creating many different record sources for every
exception, if you have more than the one exception in your example. Is your
combo box using a value/list or a lookup table/query to populate the
resolution codes? Also, your text specifies not allowing a resolution
below a set value while your pseudo code example specifies a greater than
value. Which is it?
21,
2005 >>>
In one of my forms, I have associates enter in a resolution and the form
calculates a currency value based on other info entered in. What I am
looking for, is to have my form not allow a certain resolution to be
selected
when the dollar amount is below a certain level in another field on the same

form.

My crude example:

Ex. If [DefectCost] > $99.99, then can not = ResolutionCode #6 in the
ResolutionCode field.

This is the only forum where I seem to find answers. Any help is
appreciated.

Joe

This is the only place that I seem to get answers,
Click to expand...
 
Back
Top