lookup in multiple fields (1 Viewer)

[kitty77]

I'm using the following code to lookup if a number already exists. Works fine.
I would like it to search in three other fields as well. Msamplenumber2, Msamplenumber3, Msamplenumber4
So when it does a lookup, it would look in all four fields. How can I combine those extra three fields below?
Or maybe there is a better way?

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 Sample ID Already Exists!!!"
Cancel = True
End If
End Sub

 

[Minty]

You could add

OR [Msamplenumber2] = '" & Me![Msamplenumber1] & "' OR [Msamplenumber3] = '" & Me![Msamplenumber1] & "' OR " etc. etc.

to your DLookup.

Not sure how efficient it would be on a large dataset.

 

[kitty77]

Could you show me exactly how that would look? Thanks.

 

[cheekybuddha]

Or maybe there is a better way?

Normalise your tables?

See here for an explanation of Normalisation

 

[sonic8]

How can I combine those extra three fields below?

You don't need to read the value, it is enough to use it in the criteria. So, you can replace DLookup with DCount; if the counts >0 the number exists.
Then add the other fields to the where condition in the DCcount function.

 

[kitty77]

You don't need to read the value, it is enough to use it in the criteria. So, you can replace DLookup with DCount; if the counts >0 the number exists.
Then add the other fields to the where condition in the DCcount function.

Can you show me how that would look? Thanks.

 

[sonic8]

Can you show me how that would look?

Why don't you try yourself first?

 

[kitty77]

Why don't you try yourself first?

Because I just want to make sure the syntax is correct. Easy to miss something and not have it work.
That's why I'm asking.

 

[Pat Hartman]

The

Because I just want to make sure the syntax is correct. Easy to miss something and not have it work.
That's why I'm asking.

The syntax you posted earlier is "air code". Without an actual copy of the database, that is the best you could get from any of us. If you don't understand the required syntax, try using the QBE. It includes wizards that build domain functions. Or build the simplistic example using only a single field in the WHERE portion of the domain function and give us the actual column name and value to be checked and someone will incorporate it into your shell.

 

[kitty77]

ok

 

[sonic8]

Because I just want to make sure the syntax is correct. Easy to miss something and not have it work.

You're very welcome to post your solution (or the failed attempt) here for review. I and others will be happy to help and address any problems with your code.
I usually don't feel inclined to comply to "write my code for me" requests.

 

[cheekybuddha]

Are your sample number fields in table Main of number or text datatype?

With your table design it becomes more complicated to inform the user which [of the] sample number is duplicated.

Is 4 the max number of samples defined in your table?

(What happens when you will want a fifth?)

You code soon becomes very complicated:

Private Sub Msamplenumber1_BeforeUpdate(Cancel As Integer)

Const MAX_SAMPLES As Integer = 4    ' Adjust this to the max samples defined in table Main
  Dim sample()    As Variant, _
      criteria    As String, _
      i           As Integer, _
      msg         As String

  ReDim sample(1 To MAX_SAMPLES)

  For i = 1 To MAX_SAMPLES
    sample(i) = "Msamplenumber" & i & " = '" & Me("Msamplenumber" & i) & "'"
  Next i

  criteria = Join(sample, " OR ")

  Cancel = DCount("*", "Main", criteria) > 0

  If Cancel Then
    For i = 1 To MAX_SAMPLES
      If DCount("*", "Main", sample(i)) > 0 Then
        msg = msg & vbNewLine & Me("Msample" & i)
      End If
    Next i
    msg = "The following sample(s) ID('s) already exist:" & msg
    MsgBox msg
  End If

End Sub

 

[cheekybuddha]

I usually don't feel inclined to comply to "write my code for me" requests.

I agree.

The code I have written above is more to illustrate the minimum necessary to handle poorly designed table structure.

@kitty77, with a proper related table for samples you would not need one line of code, and you could handle more than 4 samples if ever the need arose.