MS Access 2007 "Select Case" method

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.

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

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

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

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

Select allOpen in new window

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

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

Right on the button !

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

Select allOpen in new window

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

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