MS Access VBA using Option Explicit gives "Variable not defined" error but works on a different database

Question

I'm moving forms over from one Access database to another. When I try to compile the new database, it gives a "Variable not defined" error. This only happens when using Option Explicit. The variable is a AccessField data type that is selected by the form's SQL query. This compiles fine on the Access database that I'm moving it from, so I'm completely lost trying to figure out what's wrong?

I seem to have the exact same problem that never was solved in this old thread: https://bytes.com/topic/access/answers/896346-variable-not-defined-error-field-exists

It's more than one instance. Anything that references an AccessField that is not declared as a variable will return that error when Option Explicit is used.

For instance, the form is using the query:

Select * from BM where ClientID =143 and Month(BMDate) = 4 and year(bmdate) =2018 order by bmdate

And the VBA code inside that form would fail here with that error:

Option Explicit
Option Compare Database

Private Sub Form_Load()
    If IsNull(RecNbr) Then
        'Code fails with RecNbr on line above when I try to compile
        'RecNbr is a field selected from the query and is not declared as a variable
    End If
End Sub

Premonitory

Can you post the VBA code with the line that errors? – Cacciatore

- and the query. – Overby

Updated the question. Thanks – Premonitory

Answer 1

This may or may not work, but it's too long for a comment.

This post in your linked thread seems to have an explanation:

In the form design view, the Field List and the drop-down for the Control Source for a control reflect the current table design; but VBA seems to have a stale copy of the form's recordsource schema.

Try this to re-sync VBA's cache (?) of the record source:

• In form design, remove the record source (save it in a text editor, if necessary)
• save and close the form
• maybe compact & repair the database (I don't know if this will change the behavior)
• reopen the form in design view
• set the record source. Note how after hitting Return, Access takes a tiny moment, and then the warning triangles for bound controls should disappear.
• See if the code compiles now.

As a note, I find find code better readable when using Me.RecNbr instead of RecNbr when refering to form controls or fields (as opposed to VBA variables).

If the above still doesn't compile, you can use Me!RecNbr.

Useful reading: Bang Notation and Dot Notation in VBA and MS-Access

Wry

Excellent. I had a similar issue some time ago with a form copied to a new database, but didn't remember exactly how I solved it... @Premonitory – Wry

Access generates a collection of properties for the form based on its data source. If you using code to change the data source on the fly, then some of these properties can become invalid. Code changing the forms data source on the fly can thus cause failures. I suggest you use "bang" notation (me!SomeColumn) as opposed to "dot" notation which can and will fail without notice (me.SomeColumn). Access generates collection of fields/columns for you. Removing + re-entering the data source forces access to re-gen that collection. Don't rely on this re-gen, as it does not occur at runtime. – Esmond