Checking for existence of a record

  • Thread starter Thread starter John Blevins
  • Start date Start date
J

John Blevins

I have a Form that I use to write new records to one table (Inspections
Header) and from this form, I have an Append query to write records from
another table to a details table (inspections details).

The two tables (Inspections Header and Inspections Details) are related by
the field InspectionsID.

Before I append to the Inspections Details Table, I need a way to test for
the existence of records matching the selected Inspections Header record so
that I don't duplicate any records.

In other words, How can I alert a user that a query won't procede because
duplicate records would be created?

Any help will be appreciated.

John
 
I use the DCount (check the Help files for details)
function for verifications like this:

' declare variables
Dim strID as String
Dim i as Integer

' see how many times the inspection id appears _
' in the tblInspectionDetails table
strID = [Me].[txtInspectionID]
i = DCount("[InspectionID]","tblInspectionDetails", _
"[InspectionID]='" & strID & "'")

' if the inspection is is already in the _
' tblInspectionDetails table, display the _
' appropriate error message. Otherwise, _
' run the append code or query to add it.
If i > 0 Then
MsgBox "This inspection is already listed."
Exit Sub
Else
RunAppendHere
End If

Hope this helps!

Howard Brody
 
Back
Top