Access Null problem

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

GrayGuy

Thread Starter
Joined
Mar 22, 2001
Messages
47
I have a form with the following fields on it, in the order shown. The fields are also like this in the form’s Tab Order:
NameText (Text)
HouseYN (YesNo)
HouseText (Text)
StreetYN (YesNo)
StreetText (Text)
CityText (Text)

I have written AfterUpdate and OnExit Events for the YN fields with an example shown below:
If HouseYN = True Then
Me!HouseText.Enabled = True
Me!HouseText.Locked = False
Else
Me!HouseText.Enabled = False
Me!HouseText.Locked = True
End If

What I want to happen:
When I tab to a YN field I want Access to examine its condition. If it is ticked, then I want to be able to enter data in the field following the YN field.

What actually happens:
The first time I process a new record without entering anything:
1) Press Tab. Insertion bar goes to HouseYN.
2) Press Tab. Insertion bar goes back to NameText.
3) Press Tab. Insertion bar goes to StreetYN.
4) Press Tab. Insertion bar goes back to NameText.
5) Press Tab. Insertion bar goes to CityText.
The next time I process the same record again without entering anything everything works OK:
1) Press Tab. Insertion bar goes to HouseYN.
2) Press Tab. Insertion bar goes to StreetYN.
3) Press Tab. Insertion bar goes to CityText.

How can I get my form to work as I require the first time I enter it?
 
Joined
Oct 13, 2000
Messages
941
Hi Gray

Not sure from your post what exactly is wrong. (btw - is 'Insertion point' birrrrrtisssh for cursor? :confused: ) You should definitely put that code only in AfterUpdate, you don't want the same event firing twice. Also, comment out the "locked" lines (set the control to unlocked in the form-design property sheet first) so that you can see the control enable and disable. A few other things to verify:

What is the default value for the check box in question? Any null in if-then evaluates to null; there is no reason to have a null value in a Yes/No unless you're trying to use triple-state, which I would discourage anyway. Set it to Yes or No depending on which is more common.

Do you have .SetFocus statements anywhere in your code?

Is there more code than what you're showing?

If you delete the [Event Procedure] tag from the property sheet (breaks the link to the code), does it tab properly?

What is the tab cycle for the form? Are there subforms? Are these controls all in the same form subsection? Are there tab controls?

There's no way to make a tab "go backwards", or at least I've never seen one. So what's up with it going to NameText...?

Keep us posted.
 

GrayGuy

Thread Starter
Joined
Mar 22, 2001
Messages
47
Thanks for your reply.

>(btw - is 'Insertion point' birrrrrtisssh for cursor? )<
I call the Insertion Point that flashing vertical line where the text will appear when you start typing, so I guess it’s the same as the cursor.

>You should definitely put that code only in AfterUpdate, you don't want the same event firing twice.<
Surely I need to have both the AfterUpdate and OnExit events coded. It is my understanding that the former will kick in if I change the condition and the latter if I don’t make a change. Am I wrong?

>What is the default value for the check box in question? Any null in if-then evaluates to null; there is no reason to have a null value in a Yes/No unless you're trying to use triple-state, which I would discourage anyway. Set it to Yes or No depending on which is more common.<
The default settings for the check box properties are Enabled = Yes and Locked = No. The Default Value is set to No. Triple State is set to No.

>Do you have .SetFocus statements anywhere in your code?
Is there more code than what you're showing?<
I do not have any SetFocus statements. I have only shown the code for one event for one check box. There are two check boxes and each has two events. The code is identical, only the field name changes.

>If you delete the [Event Procedure] tag from the property sheet (breaks the link to the code), does it tab properly?
What is the tab cycle for the form? Are there subforms? Are these controls all in the same form subsection? Are there tab controls?<
If I delete the Events from the check boxes, the tabbing goes to each field in turn. The tab cycle for the form is the same order as I have specified the fields. There are no complications of subforms or Tab controls; the problem concerns only the 6 fields I specified at the start of my question on a basic simple form.

>There's no way to make a tab "go backwards", or at least I've never seen one. So what's up with it going to NameText...?<
Tab going backwards! Yes I guess that is the problem in a nutshell. Why does it happen? I have tried this code with Access97 running on Windows95 and with Access2000 running on Windows Me and the same thing happens. The form that I have defined in this question is very basic and only takes a few minutes to set up so you can see the effect for yourself. In fact, after setting up the table I created the form with Autoform.

It seems to me that the first time a new record is processed, when the focus reaches the first check box which is “blank” it must really be Null. So the event coding, as stated, does not work when the Tab key is pressed because the check box is only being tested for True or False. Access gets “confused” and returns to the first field on the form. This is repeated when the second check box receives focus. After tabbing through the record once without entering any data, the tabbing follows the normal cycle.
 
Joined
Oct 13, 2000
Messages
941
Welllll....

It's pretty complicated to explain, but I think your Exit and AfterUpdate events must be fighting--especially if you have the same events on two separate check boxes.

I'd like to verify it. Can you zip the form and its code into a db and send it to downwitchyobadself@chez.com? Then I'll explain what's going on, and I might not even be wrong ;)
 

GrayGuy

Thread Starter
Joined
Mar 22, 2001
Messages
47
Thanks to you it seems I am half way to solving this.

I removed the OnExit events. Now the tabbing goes through the form correctly, but the cursor visits all the fields. If I check the check boxes and then uncheck them, Access takes note of the event procedures on that and all subsequent records.

Is there any way to get Access to go to the the text box only if the appropriate check box is checked the first time through?

As you can appreciate this is just a basic form representing a much bigger complex one. I would like to make the user's life as easy as possible so that he knows the cursor will always go where he expects.

I have zipped the database and it is on its way to you.

Appreciate your help.
 
Joined
Oct 13, 2000
Messages
941
Okay, I looked at your file. The basic problem, as I suspected, is that conflict between AfterUpdate and OnExit. You're right to remove the OnExit version of the code.

I'd say that a fairly common problem for "beginning" VBA (sorry if you'd call that an insult to your level...) is choosing and using events correctly.

You have to translate events as actions; in other words, start by analyzing when what it is you want to happen, not in terms of events, and then match it correctly to the events. In your case, there are two moments at which those text boxes should be turned on or off:

1) As you enter a record. For filled in records, it needs to take a look at whether or not those boxes are checked. For a new record, it needs to set the two text boxes according to the check boxes' default values.

2) When you change a check box. Obviously.

So those are your actions; but really, only the second one is an action; the first is just a cosmetic change to reflect an action already taken. The OnExit event has nothing to do with either one; you should think of it as useful only if you want to, say, force the user to do something before exiting a control (notice the Cancel, which if you set it to True will keep the focus on the control), or move the user somewhere else than where he'd be going naturally. Has nothing to do with the data in the control.

AfterUpdate, on the other hand, is just that: do something because I changed the record. So that's where the action should really go. And as far as the first instance, well, that's the form's OnCurrent event, which means when I move to a record--new or otherwise--do something. At which point you're just going to call the AfterUpdate event. But let's take it one step at a time.

Here's my version of your houseyn_AfterUpdate. You'll notice I've removed the .Locked portion, because just using .Enabled actually makes it visible to the user, whereas a disenabled, locked control looks exactly like an enabled, unlocked control. My personal philosophy is always to show the user as much as possible what's happening "behind the scenes". But that's of course your call; all you have to do is put that .Locked line back in where you had it.
Code:
Private Sub houseyn_AfterUpdate()
    If houseyn = True Then
        Me!house.Enabled = True
    Else
        Me!house.Enabled = False
    End If
End Sub
That's fine code. I would write it a little differently myself, but what I will now show you is a much-debated topic among programmers, because though "cleaner" to read, it's less explicit. Maybe you're familiar with it already. It does the same thing, though it does not trap for null:
Code:
Private Sub houseyn_AfterUpdate()
    Me!house.Enabled = Me!houseyn.Value
End Sub
There is a third possibility, which will simplify your life considerably, but takes some explaining. This is the code I actually use in all my apps to do exactly what you're trying to do: (you can just skip the "complicated digression" bits if you're not up to it.)
Code:
Public Function uEnableTextBoxForBoolean(bleTestVal As Boolean, _
                                         ctlTxtBox As TextBox)

    If bleTestVal Then
        ctlTxtBox.Enabled = True
        ctlTxtBox.SetFocus
    Else
        ctlTxtBox.Enabled = False
        ctlTxtBox.Value = Null
    End If
    
End Function
This may look very strange to you, I can't know. Let me explain its purpose:
  1. First, it's "reusable", which means from any form in any app, I can call the code and it will do what your AfterUpdate code does, by passing arguments (bleTestVal, ctlTxtBox) to it. We'll get into that a little more farther on.
  2. Second, it's a function and not a sub. The differences are many, but without entering into a huge philosophical programming discussion, for our purposes suffice it to say that I can call it directly from the form's property sheet. Which would mean, in the case of your houseyn control, copying the following into the After Update line, instead of "[Event Procedure]":
Code:
=uEnableTextBoxForBoolean([houseyn],[house])
I do the same thing for the other check box

Code:
=uEnableTextBoxForBoolean([streetyn],[street])
(Note that you may have to use ";" instead of "," between the arguments depending on your Windows regional settings. Access will tell you by bugging.)

So, as you can see, I'm able to set the "result" for the function; subs can't do that. What does it all mean? Well, first the prefixes:
u is there to tell me this is a utility piece of code.
ble means boolean, which is the programming name for a True/False value. It may only be one or the other.
ctl introduces a control-type variable. Meaning what is passed to the code is actually a control off a form, and since I specify the type (As TextBox), I go so far as to say only a text-box type control.

You can see from what's copied into the property sheet that the function's arguments are replaced with the values relevant to your action. So, let's look at the first one
Code:
=uEnableTextBoxForBoolean([houseyn],[house])
You can read this in plain English as "When I update this control (i.e. check or uncheck it), do (the equal sign) the enabling of a ctlTextBox, in this case called house, based on the bleTestVal coming from houseyn."

The rest of the code is butter; works exactly like what you have behind your form; except that it goes so far as to move the focus to the text box in question (erase that if you don't like it--you'll not want it for the OnCurrent event, but we're getting to that...) if True, and blanks out the text box if False.

----end of complicated digression-------

So. That's lots of options for AfterUpdate. What about OnCurrent? Well, regardless of how you're doing it, you'll want to just call another procedure, rather than copying the code a second time. Why? Simple--so that, if later you change the code, you only have to change it in one place. If we use either of our first two versions, you add this to your form's module:
Code:
Private Sub Form_Current()
    houseyn_AfterUpdate
    streetyn_AfterUpdate
End Sub
Remember what I said about where the action actually occurs? This is what I was talking about. Just "call" the procedure from where it happens.

----complicated digression, part 2-------
So what if you're using my reusable stuff? Well, it's not quite adapted to what you're trying to do, because I rarely use it in quite the same way. So we have to make a little change. Make it look like this:
Code:
Public Function uEnableTextBoxForBoolean(bleTestVal As Boolean, _
                                         ctlTxtBox As TextBox, _
                                Optional ByVal pbleSetFocus As Boolean = True)
                                         
    If bleTestVal Then
        ctlTxtBox.Enabled = True
        If pbleSetFocus Then ctlTxtBox.SetFocus
    Else
        ctlTxtBox.Enabled = False
        ctlTxtBox.Value = Null
    End If
    
End Function
I've added a third argument, which you don't necessarily have to call ("optional"). (Don't worry about ByVal for now.) If you send a variable, fine. If you don't, the code will pretend it's True. Note the third line, the set-focus line: If you told me in the argument to set the focus, I will. If you didn't, I'll skip that line.

So from your form's OnCurrent, you would call it this way:
Code:
Private Sub Form_Current()
    uEnableTextBoxForBoolean [houseyn], [house], False
    uEnableTextBoxForBoolean [streetyn], [street], False
End Sub
Note that the parentheses are gone; that's because we're not setting the function "equal" to anything, but that's another subject for another time. But do note that you don't have to go back and add "True" in to the form property sheets, because the third variable is *optional*, and becomes true when not provided. Handy, those optionals. (They must always be the last variables declared, but Access/VBA help will tell you more about that.)

----end of complicated digression part 2-------


Okay. So that's a lot of information. Hope it's not too overwhelming, but since you said you'll be working on much more complex stuff, I wanted to give you some coding hints that I sure as hell wish someone had shown me back in the day, it's one tiny step toward seeing how powerful and compact your code can get. Hope it's helpful; feel free to post back with any questions.
 

GrayGuy

Thread Starter
Joined
Mar 22, 2001
Messages
47
Phew! That’s some reply – and I thought the question was a simple one.

Many thanks for your detailed answer, which I shall read thoroughly. It looks quite straightforward the way you explain it, even the difficult bits, and will give me the incentive to try and find out more on my own.

My main programming language is RPG for the AS400 (iSeries), but I 'dabble' in Access and VBA, which seems to follow a different logic and needs a different mindset.

Thanks again for your help – I really appreciate it.

BTW – Can you recommend a good book for learning VBA?
 
Joined
Oct 13, 2000
Messages
941
Yes. Hands down, it's Sybex's Access Developer's Handbook, by Ken Getz and co. Available in 97 and 2000 versions. You think my reply's long--the 2000 version reaches two fat volumes. Though you really only need volume 1 to get up and running (volume 2 is for hard-core dorks like me.)

BTW, your question was simple--I just thought the rest of it might help you get a handle on stuff a little faster. I don't know RPG, but object- and event-oriented stuff is not always obvious in any case, esp. given MS's strange divisions between what's gui and what's youi.

Keep us posted.
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top