on exit event - setting a variable

[shelspa]

On exit of a field on my form I have the following code for calculating a percent yield. It assigns a variable (test)to 135, 105, or 260 into the calculation based on the part number that was inputed. The problem is that it always assigns the variable to 135. I have tried numerous variations of this code. My default value is set to nothing.

If Me!DevicePartNumberID = 83007 Or 83021 Or 83135 Or 80354 Or 83128 Or 84015 Or 83141 Then
Me!test = 135
Else
If Me!DevicePartNumberID = 83034 Or 83101 Or 83097 Or 84024 Then
Me!test = 105
Else
If Me!DevicePartNumberID = 83169 Or 83182 Then
Me!test = 260
End If
End If
End If

Me!TotalWaferPerDeviceYield.Value = Me!DeviceYield / (Me!StartingQuantity * Me!test) * 100
Me!PercentDeviceYieldPerWafer.Value = (Me!TotalWaferPerDeviceYield / Me!StartingQuantity) * 100

Ideas appreciated.

 

[SgtJarrow]

First, to make your code work it would have to be:

If Me!DevicePartNumberID = 83007 Or Me!DevicePartNumberID = 83021 Or Me!DevicePartNumberID = 83135 Or Me!DevicePartNumberID = 80354 Or Me!DevicePartNumberID = 83128 Or Me!DevicePartNumberID = 84015 Or Me!DevicePartNumberID = 83141 Then
Me!test = 135
Else
If Me!DevicePartNumberID = 83034 Or Me!DevicePartNumberID = 83101 Or Me!DevicePartNumberID = 83097 Or Me!DevicePartNumberID = 84024 Then
Me!test = 105
Else
If Me!DevicePartNumberID = 83169 Or Me!DevicePartNumberID = 83182 Then
Me!test = 260
End If
End If
End If

But an easier way to write it would be:

Select Case Me!DevicePartNumberID
Case 83007, 83021, 83135, 80354, 83128, 84015, 83141
Me!test = 135
Case 83034, 83101, 83097, 84024
Me!test = 105
Case 83169, 83182
Me!test = 260
End Select

If you values are strings and not numeric, you will need to enclose all with double quotes...such as "83007", "83021", etc

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[CajunCenturion]

The follwing statement

If Me!DevicePartNumberID = 83007 Or 83021 Or 83135 Or 80354 Or 83128 Or 84015 Or 83141 Then

will always return TRUE because it's not doing what you think it is. Each part number, after 83007, is being evaluated as a boolean and because the value is not 0, is returning TRUE, therefore the entire conditional is TRUE.

The proper syntax would the following:

If Me!DevicePartNumberID = 83007 Or Me!DevicePartNumberID = 83021 Or Me!DevicePartNumberID = 83135 Then

That being said, my personal preference would be to use a case statement here:

Select Case Me!DevicePartNumberID
   Case 83007, 83021, 83135, 80354, 83128, 84015, 83141
      Me!test = 135
   Case 83034, 83101, 83097, 84024
      Me!test = 105
   Case 83169, 83182
      Me!test = 260
   Case Else
      Me!text = 0  ' Or whatever your desired default value
End Select

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[shelspa]

The first suggestion produces nothing and no error message with or without quotes aroung the part numbers. The second suggestion always does the calculation with Me!test set to whatever the last line says. I made it 1 because it didn't like 0. This is also with or without quotes around the part numbers.

 

[CajunCenturion]

I do not understand you last post shelspa, perhaps because I'm not sure what the first suggestion is, nor the second suggestion. When you way "it didn't like 0", did you get an error message? If so, what is the error message?

Perhaps if you post the code, including the actual calculation, that you're using we might be able to provide more help.

You might also need to check for Null and convert numberic

Select Case Val(Nz(Me!DevicePartNumberID. 0))


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[shelspa]

Here is what I tried last which is based on the second suggestion of this post. It always sets test equal to 135 regardless of the part number.

Select Case Me!DevicePartNumberID
Case "83034", "83101", "83097", "84024"
Me!test = 105
Case "83169", "83182"
Me!test = 260
Case Else
Me!test = 135
End Select

Me!TotalWaferPerDeviceYield.Value = Me!DeviceYield / (Me!StartingQuantity * Me!test) * 100
Me!PercentDeviceYieldPerWafer.Value = (Me!TotalWaferPerDeviceYield / Me!StartingQuantity) * 100

 

[fkegley]

What about using a two-column combo box on the input form? One column will be the part number, the other column will be the test value for that part number. Hide this column. Then on the AfterUpdate event of the combo box, reference column 1 of the columns collection of the combo box in your calculation.


Frank kegley
fkegley@hotmail.com

 

[RoyVidar]

Hi!

You state initially, that this code resides in the on exit event of the DevicePartNumberID control. CajunCenturion asks for your code. I think that would mean the complete code, including the "Private sub..." and the "...end sub" thingie. This would make it easier for us to help.

Now - from the last posted code, you are testing text values ("surrounded" by double quotes). What I think is needed, is to test what value is acutally in the DevicePartNumberID control when you enter this routine.

You can either test it with setting a breakpoint in the first line where the control name appears (place the cursor within the line and hit F9) or use a
[tt]debug.print me!DevicePartNumberID[/tt]
prior to doing any of the test, then, after you've run the routine, hit CTRL+G to see what values are printed to the immidiate pane.

Using the first method, the code should halt on that line, and you should be able to hover the mouse over the Me!DevicePartNumberID control, and read the value. If not, select the whole expression, and hit SHIFT+F9 to see it in quick watch.

Should you be doing this on another event than on exit, maybe using the text property might be better than the default .value? (Me!DevicePartNumberID.Text)

Two main questions: What's the value of Me!DevicePartNumberID when entering this routine and which event are you using (for which control)?

Additional, what is the data type of the value you put into the Me!DevicePartNumberID control? Is it bound or unbound? Should the controls value be converted to numeric, or is it alredy numeric?

If any of this does not provide you with solving your challenge, post what's described, and I'm sure someone will be able to provide assistance.

HTH Roy-Vidar

 

[shelspa]

Here is my entire code. The DEvicePartNumberID field is numeric and has 1 bound column.
The result is the same with or without quotes.


Private Sub DeviceYield_Exit(Cancel As Integer)

Select Case Me!DevicePartNumberID
Case "83034", "83101", "83097", "84024"
Me!test = 105
Case "83169", "83182"
Me!test = 260
Case Else
Me!test = 135
End Select

Me!TotalWaferPerDeviceYield.Value = Me!DeviceYield / (Me!StartingQuantity * Me!test) * 100
Me!PercentDeviceYieldPerWafer.Value = (Me!TotalWaferPerDeviceYield / Me!StartingQuantity) * 100


End Sub


I may pursue fkegley's idea of a two column combo box but I'm not sure how to reference them in code. The DevicePartNumberID field is coming from a table that has the following fields: DevicePartNumberID, DevicePartNumber, ChipsPerWafer, and Description. ChipsPerWafer would take the place of test in this scenerio.

 

[RoyVidar]

Hi again!

Did you try with debug.print or setting a breakpoint, or perhaps a message box to check if it contains a value?

This routine is done on another controls on exit, and since it's always giving the me!test the value of 135, from the else clause, my suspection is that the devicepartnumber control either does not contain any value when it enters the routine or is does not contain one of the values you check for or the value in the control does not match the formatting in your test.

Perhaps also use a test of the kind suggested by CajunCenturion in previous post.

Just a little question, in you test, you are testing for DevicePartNumberID, which I assume is the primary key of your table, should you be testing for DevicePartNumber in stead?

But, collecting the values from a column of the combo, might also be revarding. To Refer to different columns of a combo, remember they are zero-based, and referring to values in different columns could be:

[tt]Me!cboComboName.Column(0) ' first column
Me!cboComboName.Column(1) ' second column...[/tt]

when the combo is namec cboComboName

HTH Roy-Vidar