Check to see whether a table contains a set of fields already

[tryit]

Let's say I have a table that has five fields, a, b, c, d, and e

For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
whether any records in the table already have those values for those
fields.

For example:

If my table looks like

a,b,c,d,e

1,2,3,4,5
2,3,4,5,6
3,4,5,6,7
4,5,6,7,8
etc.

Then, the first record would be a "match" with a=1,b=2,c=3.

How do I test for that?

TIA,
Tom

 

[Marshall Barton]

Thank you, but I wasn't looking for SQL code. I was looking for VBA
code that will return true or false. Is there a way to test in VBA
code whether a query is empy or not?

Since the data is in a table, you need to use a query to see
it. You may find if more convenient to use a Domain
Aggregate function (that creates and runs an equivalent
query for you):

If DCount("*", "table". "a=1 And b=2 And c=3") > 0 Then

If that's not what you want, then you can open a recordset
on the query and check its RecordCount property.

 

[PieterLinden via AccessMonster.com]

Let's say I have a table that has five fields, a, b, c, d, and e

For any given values of a,b,c (say a=1,b=2,c=3), I'd like to check
whether any records in the table already have those values for those
fields.

For example:

If my table looks like

a,b,c,d,e

1,2,3,4,5
2,3,4,5,6
3,4,5,6,7
4,5,6,7,8
etc.

Then, the first record would be a "match" with a=1,b=2,c=3.

How do I test for that?

TIA,
Tom

If you set up a unique index on a set of fields, then Access will do it for
you. Won't be pretty, but it will work -- but you can override the typical
message to fix it.

Otherwise, you can get Trevor's code here:
http://www.mvps.org/access/modules/mdl0012.htm

and then use TCount() to determine if the record exists already. Just create
the criteria string in your form and pass that to TCount... something like

Dim strCriteria As String
strCriteria = "[Field1] = '" & Me.txtField1 & "' AND [Field2]=#" & Me.
txtDateField & "#"

If tCount("FieldToCount", "SourceTable", strCriteria)>0 Then
MsgBox "Record exists already", vbokonly
Else
MsgBox "Record doesn't exist", vbOkOnly
End If

If you use Trevor's code, you can do things like allow some number of
duplicates (say 3) or something similar (you aren't limited to the "no
duplicates" rule - you can implement a "maximum number of duplicate values"
rule).