We help IT Professionals succeed at work.

Access VBA - Compile Error

Mark01
Mark01 asked
on
Medium Priority
92 Views
Last Modified: 2019-11-13
I am modifying a procedure for a custom Query By Form. The original procedure searches multiple fields in a table, but I am modifying it to search only single field in a table

The modified code is in the click event of the Search - Test 1 button on frmMainMenu. You can view the original unmodified procedure in the click event of the Search - Test 1 (All Orig. Code).

Here are the text box names:

txtDateFrom -- Date From
txtDateTo -- Date To

Here is the modified code:
' First, validate the dates
    ' If there's something in Date From
    If Not IsNothing(Me.txtDateFrom) Then
        ' First, make sure it's a valid date
        If Not IsDate(Format(Me.txtDateFrom, "mm/dd/yyyy")) Then
            ' Nope, warn them and bail
            MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
            Exit Sub
        End If
        ' Now see if they specified a "to" date
        If Not IsNothing(Me.txtDateTo) Then
            ' First, make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
            End If
            ' Got two dates, now make sure "to" is >= "from"
            If Format(Me.txtDateTo, "mm/dd/yyyy") < _
                Format(Me.txtDateFrom, "mm/dd/yyyy") Then
                MsgBox "Contact To date must be greater than or equal to Contact From date.", _
                    vbCritical, gstrAppTitle
                Exit Sub
            End If
        End If
    Else
        ' No "from" but did they specify a "to"?
        If Not IsNothing(Me.txtDateTo) Then
            ' Make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
                End If

Open in new window


When I compile the partially modified procedure I get a "compile error - sub or function not defined." How do I correct the error?

The error occurs on the word "IsNothing" on following line:
If Not IsNothing(Me.txtDateFrom) Then

You can view a code comparison in the Code_Compare image. I can't find the cause of the error.

I am using with the Contacts database from the "Microsoft Access 2010 Inside Out" book as an example. I'm not going to upload the .accdb file from the book's CD because I think it is a copyright violation. You can view an image of the Contact Search form (image name: Contacts_Search_Form) that I am working with (using as an example) in the Contacts database.

The attached file contains the code.
Compare modified code with original codeMy Form (frmMainMenu)Contact Search formContacts databaseEE_110919.mdb
Comment
Watch Question

Microsoft Developer
CERTIFIED EXPERT
Commented:
By the looks of it, IsNothing is a custom function. It was likely defined somewhere in the original database, but you haven't copied the function definition to your new modified version. So open up the original, and look for the function. In the code window, is you place the cursor on IsNothing, and press "Shift-F2" focus should move to where the function is defined.
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
You could try without the function.
If Not (Me.txtDateTo) Is Nothing Then

Open in new window


Or try this version of a IsNothing function I found here.
Public Function IsNothing(pvarToTest As Variant) As Boolean
    On Error Resume Next
    IsNothing = (pvarToTest Is Nothing)
    Err.Clear
    On Error GoTo 0
End Function 'IsNothing

Open in new window

Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
@Norie Based on the context in which the function is used, your version of IsNothing is different.
If Not (Me.txtDateTo) Is Nothing Then

Open in new window

This for instance makes no sense, because a control reference cannot be nothing. It can be null, empty string, or have a value, but it cannot be nothing. Only object references can be nothing.
In the given context, its more likely that the function acts as something checking if the field is blank (i.e. null or empty string)
Joshua KinselComputer Programmer
CERTIFIED EXPERT

Commented:
What about isnull() or nz(field, "") = ""
If is nothing is a sub: make sure that sub or function is public

Author

Commented:
@Norie: I did change the first instance of IsNothing to the code that you suggested and the error did go away.
@Anders: Yes, I found the custom function in the modUtility module. After adding the custom function to the database, the error did go away.
@Joshua: I don't understand your comment.
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
So in modUtility, what was the function definition for IsNothing?
Joshua KinselComputer Programmer
CERTIFIED EXPERT
Commented:
Sorry how about this:
If nz(Me.txtDateFrom, "") = "" then

Just checking for null value is all.

Author

Commented:
@Anders: Yes and it solved the problem. I don't want to post the code because I think it is a copyright violation
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
Ok.  I understand that. Don't forget to close the question :)

Author

Commented:
@Joshua: I used your code in place of the original line and there was no error. I had already added the  custom function to the database, which resolved the error. I will work with your code.

Author

Commented:
Thank you, Anders, Joshua and Norie.
Joshua KinselComputer Programmer
CERTIFIED EXPERT

Commented:
Mark01:  always check for null values in vba, it will save allot of time later when you have to debug.   Error checking will also benefit you to.   (Adding line numbers to code also makes error checking really quick.)  I hope this helps.   If you need example just let me know.  :)

Author

Commented:
Thank you for the tips, Joshua. I'm using excellent sample databases that came with books written by John Viescas and Jeff Conrad.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.