- 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.
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.
5 19496 NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JL |
last post by:
Does anyone have any ideas on this please?
I don't know how to evaluate the no-blank selections against each other from
a form as follows: .
I have a form with 6 dropdown fields, each...
|
by: tamilan71 |
last post by:
Can anyone tell me a simple method of creating an application in Access
2003 that uses "Double data entry"? For me double data entry is defined
as the ability to have data entered twice. The first...
|
by: Steve B. |
last post by:
Without adding whitespace to the ComboBox datasource is there a way I can add
a blank entry or, a reset entry, to the ComboBox dropdown
Thanks
Steve
|
by: =?Utf-8?B?cmFuZHkxMjAw?= |
last post by:
The code below goes into the dropdown event handler for a standard combo box.
It makes the dropdown as wide as the widest text entry. Unfortunately, this
combo box is on the right edge of the...
|
by: Sasi Rekha |
last post by:
hi, i have a value like 'AM,AS,Testing' in the database. on the page load i need to load the dropdown with the above values but each as an individual entry, like AM as one entry in the dropdown, AS...
|
by: Brita |
last post by:
I have dropdown navigation drawing from a database. There is a dropdown box for all the states we have. When you choose a stat, the counties in the next dropdown box are created. My problem is...
|
by: Wingot |
last post by:
Hey,
I have a program I am trying to write using Visual C#, SQL Server
2005/2008, and Visual Studio 2008, and one part of it includes a Schema
called Client. Inside this schema, three tables...
|
by: admin.offshoredataentry |
last post by:
Data Entry Outsourcing provides time bound, cost effective and
qualitative Data Entry also provides numeric data entry, textual
data entry, image data entry, data format, data conversion and also...
|
by: raamay |
last post by:
I want to have a dynamic dropdown box whose entries would depend on the selection of an entry in the first dropdown box. BUT the second dropdown box should not reload, only the entries inside should...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |