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.Valu e = True Then

[AccessIdiot]

"Something else puzzles me.
How come you use
If chkWork.Value = True Then
as opposed to
If Me.chkWork.Valu e = 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.