Avatar of SirTKC
SirTKC asked on

MS Access 2007 "Select Case" method

HI Experts !

I don't know why, but I am trying to use a very simple method here called "select case" and for a reason I ignore, it simply doesnt want to return any value.
I am changing the value with a list box and based on the selection, I want the execute a calculation and insert the result in a field on the same form.

In order to facilitate the viewing, here is the code
Private Sub Combo19_Change()
On Error GoTo PoidsErr
 
Dim BoxStyle As String
Dim x, y, z, a, b As Integer
 
x = Me.D_Long
y = Me.D_Larg
z = Me.D_Haut
 
BoxStyle = Me.M_Name
 
Select Case BoxStyle
 
Case "régulière"
a = (x / 2) * z
b = (z / 2) * x
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2) + (a * 4) + (b * 4)) / 144) * 10
 
Case "Semi-régulière"
a = (x / 2) * z
b = (z / 2) * x
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2) + (a * 2) + (b * 2)) / 144) * 10
 
Case "Filler"
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2)) / 144) * 10
 
PoidsErr:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume PoidsErr
 
End Select
Me.Recalc
End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Nerdwood

8/22/2022 - Mon
Patrick Matthews

SirTKC said:
>>I am changing the value with a list box

When you only want to return one value, don't use a ListBox; use a ComboBox instead.  ListBoxes
should only be used when you want to enable 1+ selections.
ASKER
SirTKC

Oh I am sorry, My mistake - in fact this is a combobox.
And not a listbox.
Nerdwood

Hi

List boxes are fine for single selection, in fact, it's their default behaviour. Combo boxes are limited to only 1 selection whereas list boxes can have multiple, but that's not to say they shouldn't be used for single selection. It all depends how you want to display the choices to your users.

Are you sure you want the "PoidsErr:" in the select case? Are you wanting it just after the "End Select"?

Michael
Your help has saved me hundreds of hours of internet surfing.
fblack61
Nerdwood

You might also want to try putting a "Case else" in their, just to make sure that the select case is working... sometimes it's not always passed the values we think it's being passed! :P
ASKER
SirTKC

You're right... I have removed the error handling from the select range...

I have tried "Case is"

I am now trying If Then Else
And I am getting nowhere either...

This is weird..
Private Sub Combo19_Click()
On Error GoTo PoidsErr
 
Dim BoxStyle As String
Dim x, y, z, a, b As Integer
 
x = Me.D_Long
y = Me.D_Larg
z = Me.D_Haut
 
BoxStyle = Me.M_Name
 
If BoxStyle = "Régulière" Then
a = (x / 2) * z
b = (z / 2) * x
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2) + (a * 4) + (b * 4)) / 144) * 10
 
ElseIf BoxStyle = "Semi-régulière" Then
a = (x / 2) * z
b = (z / 2) * x
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2) + (a * 2) + (b * 2)) / 144) * 10
 
ElseIf BoxStyle = "Filler" Then
 
Me.EmbPoids = (((x * y * 2) + (z * y * 2)) / 144) * 10
 
End If
Me.Recalc
 
Exit_Combo19_Click:
Exit Sub
 
PoidsErr:
MsgBox Err.Description
Resume Exit_Combo19_Click
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rey Obrero (Capricorn1)

how are you setting  the value Me.M_Name ?

what is  Me.M_Name, are you setting the value in the click event of the combo19?

also, try using the change or aftereupdate event of the combo box.. not the Click event
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nerdwood

M_Name isn't being changed in the Click event... it's being referenced and used as the basis of the conditional.

Yeah, the Click event probably shouldn't be used for the combo.  :P
ASKER
SirTKC

Right on the button !
ASKER
SirTKC

Wow, how could I have figured this one by myself... The source IS in fact in the SQL statement binded to the combobox - Column 1....

NOw it work almost well. Just the error handling is still mess... I did put what Nerdwood suggested but I still get a blank pop-up message (...) By removing the msg box, it works well now.

Here is the code
Private Sub Combo19_Change()
On Error GoTo PoidsErr
 
Dim TheBox As String
Dim x As Integer
Dim y As Integer
Dim z As Integer
 
TheBox = Me.Combo19.Column(1)
x = Me.D_Long.Value
y = Me.D_Larg.Value
z = Me.D_Haut.Value
 
Select Case TheBox
Case "Filler"
Me.EmbPoids.Value = (((x * y * 2) + (z * y * 2)) / 144) * 10
Case "Régulière"
Me.EmbPoids.Value = 8888 '(((x * y * 2) + (z * y * 2) + (((x / 2) * z) * 4) + (((z / 2) * x) * 4)) / 144) * 10
Case "Semi-régulière"
Me.EmbPoids.Value = 9999 '(((x * y * 2) + (z * y * 2) + (((x / 2) * z) * 2) + (((z / 2) * x) * 2)) / 144) * 10
End Select
Me.Recalc
 
 
PoidsErr:
Resume Next
 
Exit_Combo19_Click:
 
End Sub

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
SirTKC

Thanks guy, you were great to help so fast !!
Nerdwood

Hi SirTKC

You can keep the message box to display the error message, you just need to put an Exit Sub after the main portion of your code and just before the error handling begins.

Michael