Select Case (Access 2000)

Topic

New Reply

I cannot build properly a Select Case statement.What i want to do
is to make the following statements

Dim Customer as Control
set Customer = Forms![Frm1]![customerid]
Select Case Customer
Case Customer 123 or 9000 or 2600
If Me![Quantity] < 0 Then
Exit Sub
Else
perform a code
End If
End Select

However the Select Case does not work.What i want it to perform a code for all other customers, except 8 customers. So i should use a code of the type Not In or something like that, like . Also i want that if the field Quantity is 0, to end the action.I am sure there are better ways to do that. I will be very grateful for your advice.

Reply | Quote

Replies

Select Case evaluates a single value. When it finds a case that matches, it executes whatever code you’ve put into that case.

Try this instead:

Select Case Customer
   Case 123 , 9000 ,2600
     'do nothing if it matches these
   Case else
      If Me![Quantity] < 0 Then
         Exit Sub
      Else
        perform a code
     End If
End Select

Reply | Quote

Dear Charlotte,
I am very grateful.Thank you for your wonderful advice. I couldnt use the phrase & lt, i do not know why, but anywahy replacing it a bit it works excellent.
So you have solved the question i have asked.I came across to another problem and i wonder can i ask you to have your opinion,or shall i put the problem again on the Forum?Becasue it is yet another subject.The
problem is that forbid the customer to susbtract if the stock is 0 So the Select Case works only when stock = 0. However, if the stock is not 0 items, but say 2 items, and the customer substracts 3items,then
the result is -1 and i cannot forbid it.Can i do this with my select case statement?
Here is your Select Case suggestion that works fine with me as long as the stock is not 0 and the customer substracts a bigger figure than
available in the stock.I want to forbid it.I wonder is it possible at all what i want.

Select Case customer
Case 123, 9000, 2600
‘do nothing if it matches these
Case Else
If Me![stock] <= 0 Then
MsgBox "There are no more goods available.", vbInformation + vbOKOnly
Exit Sub
Else
do a substraction
End If
End Select

May i wish you a pleasant Sunday

Reply | Quote

You should be able to do what you’re describing. Have you tried it?

I assume the customer is entering the number in a form, so your best bet is to trap the number at input in the BeforeUpdate event of the control where they’re entering the number and compare it to see if the number entered is greater than the number in stock. If it is, then cancel the update and pop up a message letting the customer know that there is no available stock (if stock =0) or not enough to satisfy their order (if stock < number entered). That will force them to fix it before it ever gets to your select case.

Reply | Quote

How did you get your example to indent? Everytime I try I lose the leading spaces. I noticed it was in a different font, so did you copy it in from notepad or something?

Reply | Quote

just use the pre tag

in the 1-click tagpanel

Reply | Quote

So that’s what the PRE tag is for! Thanks.

Reply | Quote

As JerryC pointed out, I used [ pre] tags, which change the font to courier and preserve the indents. The only caveat is that if you *copy* code in [ pre] tags, you have to paste it into Word or WordPad and then copy it from there before you can paste it into the VBE. Otherwise you get one long line of code.

Reply | Quote

Thanks. I’ll keep that caveat in mind.

Reply | Quote