Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Case (Access 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Access 2000)

    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:

    <pre>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</pre>

    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case (Access 2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Select Case (Access 2000)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case (Access 2000)

    just use the pre tag

    in the 1-click tagpanel

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Access 2000)

    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.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Access 2000)

    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. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Select Case (Access 2000)

    So that's what the PRE tag is for! Thanks.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Select Case (Access 2000)

    Thanks. I'll keep that caveat in mind.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •