lngLookup in multiple fields (1 Viewer)

kitty77

Registered User.
Local time
Today, 12:21
Joined
May 27, 2019
Messages
693
I'm using the following code to check if a number exists. How can I change it so it looks in the other fields too?
Other fields: Msamplenumber2, Msamplenumber3 and Msamplenumber4

Private Sub Msamplenumber1_BeforeUpdate(Cancel As Integer)
Dim lngLookup1 As Variant
lngLookup1 = DLookup("[Msamplenumber1]", "Main", "[Msamplenumber1] = '" & Me![Msamplenumber1] & "'")
If lngLookup1 = Me![Msamplenumber1] Then
MsgBox Me![Msamplenumber1] & " - This Number Already Exists!!!"
Cancel = True
End If
End Sub
 

GPGeorge

Grover Park George
Local time
Today, 09:21
Joined
Nov 25, 2004
Messages
1,776
Unfortunately, that reveals an inappropriate table design. It's so common there's even a name for it: "Repeating Columns". Whenever you see fields named "XXX1", "XXX2", etc. you know you've encountered a design problem. It comes from the Excel world, by the way, where that's the only option on the table (see the pun there?) The solution is to redesign the table into a valid relational database table design. One of my favorite discussions of the problem and how to correct it can be found here. Roger has several blog posts describing it and suggesting approaches to fixing it.

Now, you CAN add additional criteria into this DLookup, or use additonal DLookups for the other fields, but that's unnecessary, extra, redundant work in addition to what needs to be done.
 

kitty77

Registered User.
Local time
Today, 12:21
Joined
May 27, 2019
Messages
693
Not exactly what I'm looking for.
 

plog

Banishment Pending
Local time
Today, 11:21
Joined
May 11, 2011
Messages
11,611
First, DLookup can return null which can screw everything up. If you simply want to check for the presence of something or not use DCount()

Second and foremost, you have not set up your tables correctly if you are suffixing numbers to the end of field names. When you do that its time for a new table. Data should be stored vertically (with more rows) and not horizontally (with more columns). In that table, if you have 1 Msamplen umber you would put 1 record. If 2, 2 records, 3, 3 records, etc. Every number would go into its own record so you don't have blanks all over the place.

Perhaps you can complete your Relationship tool and post a screenshot so we can help set up your tables properly.
 

GPGeorge

Grover Park George
Local time
Today, 09:21
Joined
Nov 25, 2004
Messages
1,776
Not exactly what I'm looking for.
I know, but it's the reason for the problem. The band-aid is to add a bunch of " OR'ed " criteria to the current version, which is a PITA to set up. It's even more hassle to maintain over the long run. You now have space for up to 4 samples and not more. If and when a 5th sample is needed, you have to modify this table, then everything else in the database application that touches the table, queries, forms, this function, other functions.

Sometimes, the path of least resistance does seem to be to avoid the big problem and only deal with the little one; I get it. Over time, you have a lot of little problems to deal with anyway.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2002
Messages
42,970
When you build on a faulty foundation, you spend a lot of time shoring up the mistake. No matter the initial pain, fixing the foundation is always the best solution. Somehow that 5th sample or 8th child or 10th certificate or third contact method always turn up. When you have more than one of something, you have many and there is a common solution for "many", it is a new table where each item is stored as a row rather than a column. In a relational database, rows are free, columns are expensive.

Do NOT let presentation problems cause you to ignore defining a proper schema. This is the huge limitation of Excel. The data is stored the way you want to report on it rather in the most efficient way. In a relational database, the presentation layer and data layer are separate, giving you the opportunity to have your cake and eat it too:)
 

kitty77

Registered User.
Local time
Today, 12:21
Joined
May 27, 2019
Messages
693
Well, the code I'm using now works perfectly. When a user inputs a value in that field and it already exists, I get a message.
I'm not looking to redesign, just to want to know if I can make it look at the value in the other fields too.
 

plog

Banishment Pending
Local time
Today, 11:21
Joined
May 11, 2011
Messages
11,611
Your code doesn't work perfectly--you are posting here for help with it. I remember your prior 2 posts:


We keep giving you workarounds for this same issue of your poor design. I fear this element of your poor design is just the one we have finally teased out and there are actually other issues with your table structure. At some point you got to stop painting over the black mold on the wall and actually fix the leak in the roof it it stops the problem at its source.

Complete your Relationship tool, expand it to show all fields and post a screenshot of it for us so we can help you where it will matter.
 

Users who are viewing this thread

Top Bottom