Looking up information across multiple fields

[Someone]

Hello

I have 10 separate fields that contain numbers. The fields are formatted as
text because they need to have a 0 (zero) at the beginning. The fields
cannot be indexed as 'no duplicates', because duplication may occur on
occasion.

The code below demonstrates how I check whether the number being inputted
has been used before. While it works, it only checks the input against
information inputted in that field only - it doesn't check numbers inputted
previously in any of the other 9 fields. The field names increment by one
(e.g. field1, field2, field3 etc). The field and table names below are not
the real words used.

Could somebody assist in telling me how I can check the inputted number
against the information in ANY of the 10 fields in the table, please?

If Me.NewRecord Then

If DCount("[Field_Name]", "tbl_Name", "[Field_Name] = """ & Me![txt_Name] &
"""") > 0 Then

strName = txt_Name

If MsgBox("Message here" & vbCrLf & vbCrLf & "Would you like to close this
form and look up the previously queried number?", vbYesNo + vbQuestion,
Me.txt_Name & " already queried") = vbYes Then

Me.Undo

'strName used because after using an Undo, the txt_Name field is then empty
DoCmd.OpenForm "frm_Name", , , ("[Field_Name] = """ & strName & """")

End If
End If

 

[Allen Browne]

If you have 10 fields that you are comparing, then you probably should have
a related table where all these values appear in different records instead
of different fields. It then becomes child-play to test whether the
combination already exists.

If you cannot take than normalized approach, you could DLookup() the table,
with 10 phrases in the 3rd argument:
Dim strWhere As String
Dim varResult As Variant
strWhere = ""([Field1] = """ & Me.txt_Name & """) OR ([Field2] = """ &
Me.txt_Name & """) OR (...
varResult = DLookup("YourPrimaryKeyFieldNameHere", "tbl_Name", strWhere)
If Not IsNull(varResult) Then ...

If DLookup() is new, see:
http://allenbrowne.com/casu-07.html

 

[Someone]

Hi Allen

I understand what you mean about normalising the data; however, in this
instance, I thought it would be easier, for various reasons, to have the
data stored in the main table.

I have incorporated your suggestion and it works a charm. I'm very grateful
to you for your assistance in my projects.

Merry Christmas to you!

M

If you have 10 fields that you are comparing, then you probably should
have a related table where all these values appear in different records
instead of different fields. It then becomes child-play to test whether
the combination already exists.

If you cannot take than normalized approach, you could DLookup() the
table, with 10 phrases in the 3rd argument:
Dim strWhere As String
Dim varResult As Variant
strWhere = ""([Field1] = """ & Me.txt_Name & """) OR ([Field2] = """ &
Me.txt_Name & """) OR (...
varResult = DLookup("YourPrimaryKeyFieldNameHere", "tbl_Name",
strWhere)
If Not IsNull(varResult) Then ...

If DLookup() is new, see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

I have 10 separate fields that contain numbers. The fields are formatted
as text because they need to have a 0 (zero) at the beginning. The
fields cannot be indexed as 'no duplicates', because duplication may
occur on occasion.

The code below demonstrates how I check whether the number being inputted
has been used before. While it works, it only checks the input against
information inputted in that field only - it doesn't check numbers
inputted previously in any of the other 9 fields. The field names
increment by one (e.g. field1, field2, field3 etc). The field and table
names below are not the real words used.

Could somebody assist in telling me how I can check the inputted number
against the information in ANY of the 10 fields in the table, please?

If Me.NewRecord Then

If DCount("[Field_Name]", "tbl_Name", "[Field_Name] = """ & Me![txt_Name]
& """") > 0 Then

strName = txt_Name

If MsgBox("Message here" & vbCrLf & vbCrLf & "Would you like to close
this form and look up the previously queried number?", vbYesNo +
vbQuestion, Me.txt_Name & " already queried") = vbYes Then

Me.Undo

'strName used because after using an Undo, the txt_Name field is then
empty
DoCmd.OpenForm "frm_Name", , , ("[Field_Name] = """ & strName & """")

End If
End If