Solved Does Record Exist Code Error.

[HillTJ]

So, I have the following code that worked fine when the rs,findfirst line was hard coded (now commented out). I wish to make the function more universal by passing the 'find' part as a strwhere that I've built up. But, it doesn't work & returns an invalid argument error 3001. Clearly, there is something wrong with my syntax. Appreciate any help.

Option Compare Database
Option Explicit
Public Function DoesRecordExist(RecordID As Long, TableName As String, FieldtoMatch As String) As Boolean


'On Error GoTo MyErrorProc:
    
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
    
    If IsNull(RecordID) Then
        Exit Function
    Else
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(TableName, dbOpenDynaset)
    
    strWhere = FieldtoMatch & " = " & RecordID
    
    Debug.Print strWhere 'to test what the string looks like
    Debug.Print "InspectionSectionID = " & RecordID 'to test what the original string looks like
    
        'rs.FindFirst "InspectionSectionID = " & RecordID 'the original expression that works
      
        rs.FindFirst Chr(34) & strWhere & Chr(34) 'invalid argument error 3001
            If Not rs.NoMatch Then
                DoesRecordExist = True
            Else
                DoesRecordExist = False
            End If
  End If
ExitError:
        
        Set rs = Nothing
        Set db = Nothing
        On Error Resume Next
        Exit Function


MyErrorProc:
        
ErrorHandler:
    Call DisplayErrorMessage(Err.Number, "DoesRecordExist")
    
        Resume ExitError


End Function

 

[theDBguy]

What happens if you take out the Chr(34)s?

 

[HillTJ]

@theDBguy , all seems OK. you know, I think I was not passing the parameters correctly when testing!!! I got a bit frustrated & called for help. Thanks for the support. Sometimes you have to take a step back from your work.

 

[theDBguy]

@theDBguy , all seems OK. you know, I think I was not passing the parameters correctly when testing!!! I got a bit frustrated & called for help. Thanks for the support. Sometimes you have to take a step back from your work.

Ok. Glad to hear you got it sorted out. Cheers!

 

[HillTJ]

Pat, thanks for being so gentle. I kind of thought i was being clever to write this function as reusable code. As a learner and working in isolation (except for when i get stuck and call the forum for help) it is often difficult to determine the correct path. Googling code can send you in the wrong direction if you don't have an understanding of what you wish to achieve. Fully understand (at least to the extent i can understand) your explanation. Will replace my code with a couple of lines using dcount. Will look up how best to implement this. All i aim to do is confirm whether a record exists or not in another table.

 

[June7]

"or not in another table" raises another can of worms. Why would a record possibly be in more than one table?

 

[Josef P.]

[a little OT]

The task this procedure is performing would usually be handled by a dCount() function.

Why should you count all hits for "Exists"?
A DLookup (or similar as select) would also be sufficient.

 

[isladogs]

Please check out my recently updated article comparing different methods of performing this task

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

 

[HillTJ]

@june , i open a form with docmd.openform in either 'add' with no filter parameters or 'filtered' by passing a 'where' statement if a related record exists. It's the checking of the 'does record exist' that's the subject of this thread. I'll just use a dlookup as advised by @isladogs.

@Pat Hartman , my philosophy is to develop my applications to 'best practices'. I try to write tight reusable code and use vtools to catalogue these. Unfortunately, i am not blessed with a best practices detector and i often get stuck 'not knowing what i don't know'.

What i have learn't with this simple thread is invaluable stuff. Thanks to all. I hope others have found it informative too.

 

[Josef P.]

@HillTJ: I consider encapsulating the Exists exam into an extra function to be good practice. This allows to efficiently support other DBMS later on. Even if e.g. only DLookup is used in this function, I find this better than using DLookup directly.

For comparison: "Check if there is an entry for XYZ and then do something"

Variant 1 (only DLookup):

if Nz(dlookup("True", "Table", "FeldABC = 'xyz'), False) then ' Nz is not required ... only to check false ('not exists')
   DoSomething ...
end if

Variant 2 (Exists function):

If DoesRecordExists("Table", "FeldABC = 'xyz'") then
   DoSomething ...
end if

With function DoesRecordExists:

public function DoesRecordExists(Source as String, Criteria as String) as Boolean
    DoesRecordExists = Nz(dlookup("True", Source, Criteria), False)
end function

Variant 2 does the same as variant 1, but is much more readable for me.

Because since all you care is if the ID exists

Exactly! I don't want to know the number.

Assuming you have the good sense to return the ID which is what you searched for rather than some random data field which might naturally be null.

Neither. I only let True give back.

 

[ebs17]

DCount and DLookup are Access functions. They are executed just like the non-optimal recordset from the top of the frontend, i.e. with a linked table, and each by itself is a table access with virtually the same cost.
Of course, a DCount should be compared to a "SELECT Count(*) ...", not to loading a whole table with all the unneeded fields, and FindFirst is entirely not optimal for an existence check.

 

[isladogs]

I'll just use a dlookup as advised by @isladogs.

Not sure where you got that idea from. Perhaps you didn't read my article in its entirety?
I hope that the field you are checking for a specific record is indexed as that will make a huge difference to the search time whichever method you use.
For indexed fields, the domain functions DLookup & DCount are both relatively slow methods of searching if a record exists.
There are much faster methods available

 

[HillTJ]

All, you have provided me with plenty of approaches to try. I shall digest your recommendations, have a fiddle and see what works best. Thanks