To verify entry of a dropdown

[DJRhino]

Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
    If [DrawingNo] = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
                    310030356 Or 310030359 Or 310030362 Or 310030446 Or 310030450 Or _
                    310030511 Or 310030512 Or 310030516 Or 310030517 Or 310030519 Or _
                    310030520 Or 310030521 Or 310030535 Or 310030538 Or 310030671 Or _
                    310030672 Or 310030787 Or 310030788 Or 310030789 Or 310030828 Or _
                    310030975 Or 310030976 Or 310030977 Or 310031003 Or 310031004 Or _
                    310031006 Or 310031085 Or 310031086 Or 310031087 Or 310031186 Or _
                    310031188 Or 310033843 Then
            MsgBox "Make sure you turn in your Lab Samples!", vbCritical
    End If
End Sub

I believe I'm really close. Everything compiles. It triggers the msgbox, but it will do it for everything selected in the dropdown box.

[NeoPa]

Hi DJ.

Please (separately) read the PM I just sent you. For now though, I will deal with this as well as I can for you.

This is a long list of values you want to compare, so my first advice is to do it another way. Long & complicated lists don't work well within the simple If ... Then syntax. To be clear, if done that way, it would look like :

    If [DrawingNo] = 310029923 _
    Or [DrawingNo] = 310030138 _
    Or [DrawingNo] = 310030152 _
    Or ... Then

With the number you have that would be very messy. Possible, but messy.

In SQL (We discussed earlier how SQL & VBA syntax is often similar but not always the same.) you could use In() within a WHERE clause for instance, and have something like :

WHERE [X] In(310029923,310030138,310030152,...)

It would be a long list but at least you wouldn't have to repeat what you're comparing it to for every instance as you do in VBA.

Another way to handle it in VBA is to use the Select Case syntax. This is somewhat less clumsy, and you can even group sequential numbers together using To, yet still messy compared to other alternatives I'll cover later. It might look something like :

    Select Case [X]
    Case 310029923, 310030138, 310030152, 310030346, 310030348 _
       , ...
       , 310031006, 310031085 To 310031087, 310031186
         MsgBox ...
    End Select

If I were doing something like this I would have an extra column (possibly invisible) in the ComboBox that which contained a value (True or False at the very basic level) and I would have code that checked what that was to determine whether or not to do what you want to do. Something like :

Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
    If Me.CboDrawingID.Column(3) Then
        MsgBox "Make sure you turn in your Lab Samples!", vbCritical
    End If
End Sub

This uses the column with Index of 3 in line #2, but you would have to use whatever column you had put that value into in your own ComboBox.

A last suggestion, to make it more data-driven (always a good thing), would be to have a table that either contained only those values, or another that contains all the possible values with an extra Field that shows for each value whether or not it is one of those you want to handle.

[DJRhino]

NeoPa,

I selected the Select Case Version and it worked perfectly. I chose this one as I'm doing this update on the fly along with another update. On my next bigger update, if there is one, I will use the Last suggestion as it will be simpler and a mor ingenious idea.

Again much thanks for your help and tutelage. I'm learning, slowly, but still learning. I appreciate you patience

DJ.

[NeoPa]

Hi DJ.

That sounds like good judgement based on sound reasoning.

By the way, I want to be absolutely clear with you :
Slowness at understanding the instructions/help is not really a problem. Just make sure you put the work in up front to present your questions clearly so they can be understood without too much guesswork as to what you are working with & what you need.

By the way, congratulations for passing 100 posts the other day.

[munirashraf9821]

Great advice, Using a dedicated table to manage those specific values would make things more organized and scalable. Making it data-driven could really simplify any future updates or changes. This could save a lot of time in the long run. Thanks for sharing this tip!

[NeoPa]

Hi Munir.

Thank you. This is a pretty common approach. You can also use the same table to populate the ComboBox columns and thus check the appropriate column of the selected row to determine whether you need to handle the selection (& avoid the necessity of going back to the table to determine that). I hope I made that clear in my earlier reply but that one was pitched at where DJ seems to be now so I didn't want to get into too much detail at the time.

PS. Normally we're very concerned about other members pitching in to a topic and maybe veering it away from the original question. In this case I can confirm you were very much on-topic and we appreciate your comment :-)