variable not defined error for field that exists?

[AccessIdiot]

I am using a MySQL back end and Access front end. One of my MySQL tables has three fields all defined as tiny ints, no nulls, default 0, field width 2.

On my form I have three checkboxes, each linked to the three fields. I have the EXACT SAME CODE for each after update:

Private Sub chkJHA_AfterUpdate()
If chkJHA.Value = True Then
dpr_safety = 1
Else
dpr_safety = 0
End If
End Sub
---------------------------------
Private Sub chkVehicle_AfterUpdate()
If chkVehicle.Value = True Then
dpr_vehicle = 1
Else
dpr_vehicle = 0
End If
End Sub
------------------------------
Private Sub chkWork_AfterUpdate()
If chkWork.Value = True Then
dpr_work = 1
Else
dpr_work = 0
End If
End Sub
 

The first 2 work beautifully, the third gives me a compile error, variable not definded on dpr_work.

The other strange thing is that you know how the VBA editor does an autofill suggestion when you start typing me.? When I scroll through the list it shows me everything in my database EXCEPT that one field it is throwing the error on.

I can not for the life of me figure out why and I'm about totally and completely lose my mind. I've triple checked spellings, updated tables, rebooted my machine and nothing. I've confirmed that the field is in the table - both through phpmyadmin (which I'm using to create/edit the tables) and even in Access. You can see the field in the table and in the table design view. The only place it doesn't show up is in the VBA editor auto fill thing.

W.T.F.

Thanks for any help.

[AccessIdiot]

If I create a blank form and add a checkbox, set the control source to the field in question, then go into the VBA editor it shows up? and works? So something is explicitly in my form that prevents it from working?

[Mariostg]

I bet you are using Option Explicit and you did not delcare the variable...

[AccessIdiot]

Yes and yes, although is a field name a variable? I haven't declared anything anywhere and yet all the others work? dpr_vehicle, dpr_safety, and dpr_work are all tiny int fields in the MySQL db. The code for dpr_vehicle and dpr_safety work just fine.

One of my coworkers made a copy of the database and opened it on his machine and was able to get it to work with no problem. So we've replaced the file, but I still find it to be very strange?

[Mariostg]

That is indeed bizarre but I am unsure I understand when you say "I haven't declared anything anywhere". Just to clarify, you have declared in the General portion of your module:

Option Explicit

And you have declared your variable:

Dim dpr_work as Integer

Because when you set Option Explicit, if you do not Dim the variable, this is exactly the error message you will get.

[AccessIdiot]

I do have Option Explicit, yes. No, I did not dim any of the variables. I've done a few access projects, though I am very much a beginner, and I can't recall ever declaring field names, and yet things work. So I am obviously practicing some very bad programming!

So since it all worked great until I tried to add this one particular bit of code is Access just very forgiving up to a point and then yells at me? Do I need to declare all fields that I am using in the vb editor?

And what about the fact that the auto complete pick list that appears when you type "Me." shows all fields available except that one?

[Mariostg]

First, there is a difference between fields and variables.
Your form fields that you get from Me. will not complain whether or not you use Option Explicit. But variables will.

Make a little test you will get it...
Compile this it will work

Option Compare Database
 
Sub myTest()
y = 1
End Sub

Compile this and it will fail

Option Compare Database
Option Explicit
Sub myTest()
y = 1
End Sub
 

Now compile this and that will work

Option Compare Database
Option Explicit
Sub myTest()
Dim y As Integer
y = 1
End Sub

[AccessIdiot]

So if I'm not creating a new variable to hold anything, but just specifying an actual field in the database that I want populated, it shouldn't matter if I use Option Explicit or not right? The fact that Me. shows ALL my fields EXCEPT that one is raising a red flag for me. Especially when the database is copied and moved and then shows up in that copy? That sounds like something is corrupted in the original version of the db?

[Mariostg]

First question: Correct indeed. Using Option Explicit may seem like more work (or at least more typing) but it will ease debuging down the road and forces you to be consistent.
Second question: Not sure I get your point.
Third question: Maybe... Compact and repair often.

Also note that when Access reports an error, sometimes it will highlight an area but the error will be somewhere else than the highlighted area. Normally before it.

Something else puzzles me.
How come you use
If chkWork.Value = True Then
as opposed to
If Me.chkWork.Value = True Then

[AccessIdiot]

"Something else puzzles me.
How come you use
If chkWork.Value = True Then
as opposed to
If Me.chkWork.Value = True Then"

Heh - because I'm a hack coder who doesn't know what she is doing. :-) I'm actually a GIS Specialist trying to make some database things work. If I can get this up and running I can join it to some spatial data and impress my boss.

If I am referencing a field in the vb should I always use Me?

I guess the point I was trying to make earlier is that in the database design the field shows up as available. Also in the form design I can choose the field when I set the Control Source. But in the vb editor when I type Me. it's like it doesn't exist.

Except when I make a copy of the db and suddenly its available.

[Mariostg]

LOL. Let's make it work then.

If I am referencing a field in the vb should I always use Me?
I always did, but I seldomly use .value... Hey I am just a hacker too. I use Me because it is the way I learned and it clerly tells me that the variable I am dealing with is coming from a form.

I see your point... But normally, while the the VBA editor AND in the form module, when you type Me. you will get a list of all its properties and methods.

[AccessIdiot]

Well, I guess I'm going to have to chalk it up to being corrupted. :-( Very frustrating. But on to other things!

Like, why am I still getting a value of -1 when the box is checked instead of the value changing to 1?

[Mariostg]

Yes, MS Access is weird for that:
True = -1
False = 0
It can also be null (when it is neither True nor False)
In fact you could say :

If Me.chkJHA Then

to check if it true or

If NOT Me.chkJHA Then 

to check if it is false. But it will throw an error if it is null.

[AccessIdiot]

Default is 0 so shouldn't be a problem with the null (and I can use NZ right?). Thanks for the shortcut tip!

[Mariostg]

If it defaults to 0, you are good to go. It is always best to avoid null values in any situation. Nz can be a good friend indeed.

[BarksWorseThan]

I know these posts are from more than a year ago, but just want to say/clarify: I have run into this same problem, and what I notice is that the fields that are throwing the "Variable not defined" error are either fields whose names I changed recently in the table definition or new fields that I recently added to the table.

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. So far I have had no success finding a way to refresh/update VBA's "awareness" of the recordsource's current field names... Any ideas?

[NeoPa]

Oh wow!

I didn't see this before (So I won't *slap* BarksWorseThan for hijacking the thread - but I will say if you want a question answered then the way to do that is to post a question thread).

Melissa, there are a number of confusions in here. Some of which Mario has helped with, but others he's missed.

First off, it's best to understand the differences between variables, fields and controls. My guess (after the first post and hasn't changed since) is that you have a control missing for dpr_work. Either mis-spelled or just called something completely different - like Check29 or something. The field is probably fine, but you never renamed the control.

Forms actually can have Fields, but these are underlying and what you see on forms are controls (which may be bound to the fields). It gets more complicated from there, but I expect it's resolvable from that anyway. let us know how you get on ;-)

[BarksWorseThan]

NeoPa -- a pleasant surprise to get a quick response! I was not really expecting much from my post here... was more interested in trying to clarify for others who may land here that the problem is real and probably NOT related (as you propose) to confusion between fields, variables, and controls.

At least in my case, I name my controls differently from the fields that are used as the control source specifically to avoid confusion when coding between the field and the control; likewise, I have yet a third convention for naming variables. So, for example, field "TractSubCode" is the control source for control "cboTractSubCode", and if I wanted some variable referring to the TractSubCode I would name it strTractSubCode.

Now, in my case, I used to have a field (in a *linked* table, in case that's important) called simply "TractSub", which used to be the Control Source for the control cboTractSub. When I renamed the column to "TractSubCode" (for semantic reasons), I changed the control source for the drop-down, and also renamed the control to "cboTractSubCode".

In VBA, however, I cannot refer to "TractSubCode", else I get the "variable not defined" error. If I type Me. Intellisense presents me with the old list of fields (among the many other options)... so "TractSub" is on the list even though it no longer exists in the table. Also, some new fields that I have added to the table are not present via Intellisense, and if I try to use them I get the "variable not defined" error, even though I can see the new and re-named fields correctly when I'm in Form Design mode.

So it would appear to be -- at least in my case, but I suspect also in the OP's case -- some kind of "stale" schema or metadata info on the VBA side of things. The Form Design sees all of the changes and additions to the table, but in VBA it's stuck on the table fields as they were when I first created the form.

Hope this helps to clarify where the problem lies. Thanks for your help.

[BarksWorseThan]

Sorry for the typo on your id, NeoPa...

[NeoPa]

BarksWorseThan:
At least in my case,

Herein lies the rub of course.

Though you clearly describe your situation, the thread is related to the situation of the OP (AccessIdiot as she refers to herself but I happen to know she is overly self-deprecating in that). I cannot, nor should I (You sound intelligent enough to appreciate why without detailed explanation), get involved with your particulars in this thread as that would hijack it away from the OP.

If you'd like to raise this issue yourself, in a separate thread, I'll be happy to deal with it in greater detail.

PS. It's not unlikely that all such posts, unrelated directly to the OP's specific problem, will be removed as hijacks, but I won't do so myself. I'm fussy, but I can see benefit in this, at least for now (Others may be fussier though ;-)).

PS. No problem with the ID. I didn't even see it and I find people actually getting it right is a rarity. Being fussy though, I like it when they do :-)