Syntax for null numeric field

[WinDancer]

I am trying to test a form value for null-
the code below works for text fields, but is not working for a numeric field- it just pops up a message 'you entered an expression that has no value'

If IsNull(Me.TabCtl0.Value) Then

Help, please?

Thanks,
Dave

 

[dkinley]

Not sure what you want, if a numberic control - it should work, but ...

According to what you have (based on the name TabCtl0), you are testing the value of a tab control. It is my understanding that a tab control only has a value depending on which tab is selected.

Are you wanting to check if someone has selected a certain tab or if one is selected at all?

-dK

 

[WinDancer]

You are correct:
typo- sorry

I am just testing to see if there is an old value in the field

If IsNull(TabCtl0.OldValue) Then

 

[dkinley]

Okay. Suppose someone was on Tab 2 and then went to Tab 3. You want to know that they came to Tab 3 from Tab 2 (or 4 or 10 ...) ?

-dK

 

[WinDancer]

There are 4 tabs, 0,1,2,3.
There is an error checking routine on tab2.
When they leave tab2 I want the routine to run. If there are errors on tab2 I want to take them back to tab2 and display a form so they can correct the bad entries.
Since this will run ONLY when they think they are done on tab2 and choose another tab, I need to check that tere is an oldvalue. If there is no alue there I just want to exitsub.

Thanks,
Dave

 

[dkinley]

Hmmm. The OldValue property is for controls that have a bound property - a tab control is not bound.

I normally check the entire form before the users exit but if you specifically only want something on Tab 2 then this could real tricky. I was first thinking of something along the lines of the following on the OnChange event of the tab control. But there the out to the user is only to select the second tab (value of 1), make the changes and exit the form - no error checking would be performed.

If Me.Dirty Then
     If TabCtl0.Value <> 1 Then
          'do error checking
     End If
End If

I also thought about setting the error checking to the On Focus event of the first control of the next tab, but the user could skip that control and the error checking would never occur.

Another thought I had was if the form was dirtied anywhere to place the tab control value in a hidden control on the form (aka, the old value). Then when the OnChange event fired again, it would compare the current value to the stored value and determine it's logic from there.

I guess I have to ask how critical is it that the error checking is performed immediately after they leave tab 2 and before they go anywhere else? Could this error checking be done after the users mess around with other tabs but before the users exit the entire form?

-dK

 

[WinDancer]

It will read the value of the previous tab-

Because of the problems getting this to function I added a 'check data' button at the end of the last tab that only fires when the user clicks on it. They have been using it for the last few days and seem happy with it.
It just makes me crazy when I have to build systems around the software capabilities rather than the work flow.
It seems silly to me to wait for the user to complete another half-hour of work and then tell them- o yeah, remember that other tab? You need to check some of those values.

Thanks for trying
Dave

 

[dkinley]

In mulling this over some more I am thinking on the tab control properties for OnChange then you could do a (if my logic is correct) ..

If Me.Dirty Then
     Me.txtControlName = Me.TabCtl0.Value
     If Me.TabCtl0.Value <> 1 And Me.txtControlName = 1 Then
          'do error checking
     End If
End If

.. and that should get you where you need to be. The txtControlName is the control I ran on about in my previous post to store the previous tab value.

If my logic is right then first of all, nothing would happen if there were no changes to any controls. If there was and the user went from tab x to 1, then nothing would happen. If the user went from tab 1 to anywhere else, the error checking routine should fire.

-dK

 

[dkinley]

It will read the value of the previous tab-

Because of the problems getting this to function I added a 'check data' button at the end of the last tab that only fires when the user clicks on it. They have been using it for the last few days and seem happy with it.
It just makes me crazy when I have to build systems around the software capabilities rather than the work flow.
It seems silly to me to wait for the user to complete another half-hour of work and then tell them- o yeah, remember that other tab? You need to check some of those values.

Thanks for trying
Dave

I checked it with a MsgBox and kept getting an error. My test was ...

If Me.TabCtl14.Value <> 1 Then
     MsgBox "Current: " & Me.TabCtl14.Value & " Old: " & Me.TabCtl14.OldValue
End If

I ensured that I had clicked another tab and doodled in a control before I clicked on another tab (and to avoid a null). Microsoft explicity stated for an Old Value that the control had to be bound.

I understand the frustration, in thinking it through - I was seeing too many ways the user could 'avoid' the error checking.

-dK

EDIT: The error is 2427 which basically means an expression does not have a value. Based on my test it should have a value but based on MS, tab control is not bound so it shouldn't have a value.

 

[BrettM]

How about a simple calculation to test it...

if TabCtl0.OldValue + 0 <> 2 then

Hope this helps.
Brett

 

[dkinley]

I got the same 2427 error.

-dK

 

[gemma-the-husky]

surely tabctrl0 is the page, not a control ON the tab control?

isnt that why it has no value?


---------
if tabctrl0 is a field then the other reason it may have no value is that there is no data in your form- which normally occurs when you either have a form with no data, where additions are not possible - either because you have disallowed them, or your query is non-updateable

 

[dkinley]

Err ... I am assuming it is the tab control and not a control on the page.

-dK

 

[gemma-the-husky]

so if a tabctrl is just a container, i can't see what sort of "value" it could have.

What numeric value would the poster be expecting to see?

 

[WinDancer]

The tab control [tabctl0 in this instance] is a numeric field, starting at 0 and incrementing by 1 for each tab.
This is a true value and can be seen using tabctl0.value, which will return the tabs value.
My problem, trying to see what page they came from, is that when the form is opened there is no old value and using tabctl0.oldvalue errs out because the field has no old value until you choose a new tab.
I have used this tabctl field before to select where the user starts when the form is opened and which field they see to begin working with the form.
The error is that there is no value and I can't figure out how to tell the program that if this value is null, skip the routine and just exit sub.
Dave

 

[dkinley]

To your point, YES, you can assign a default tab when a user does something or when the form opens. For instance, user does x so set the value of the tab control so the form automatically goes to a particular tab because a tab control has a value and it can be captured/manipulated.

From Access Help (I added the bold emphasis) ...

The OldValue property contains the unedited data from a bound control and is read-only in all views.

Based on my testing (it was more detailed than I placed in this thread), I do not believe the system keeps up with an old value for a tab control - I even tried to capture a null for the first instance of tab selection (IsNull()/Is Null/vbNull - last one worked very funny in the test). I opine that a tab control is an unbound control; therefore it cannot have an old value like a text box would; hence the only option I see is something described in post #8.

-dK

EDIT: If you get the same error that I did, 2427, then you can perform an error-trapping routine to ignore it and move on in the code - but I still persist that in trying to use .OldValue for a tab control will always result in an error.

 

[WinDancer]

"Me.txtControlName"

Not sure what field this is?

 

[dkinley]

That's a (hidden) control that you need to add to the form to hold the previous tab's value. I just threw a generic name in there as a placeholder so you can give it a more meaningful name for your purposes.

-dK

 

[gemma-the-husky]

just try

on error resume next

before your assignment statement

 

[dkinley]

Okay .. there was a flaw with post #8. Here is the correct code for the Change event of the tab control ...

If Me.Dirty Then
     If Me.TabCtl0.Value <> 1 And Me.txtControlName = 1 Then
          MsgBox "Moved From: Tab " & Me.txtControlName & " To: Tab " & Me.TabCtl0.Value
     End If
End If
     Me.txtControlName = Me.TabCtl0.Value

I left the MsgBox stuff in there so you can test it out visually. Be sure to put a control on the form to hold the last tab's value.

Now, using Me.Dirty means if any control on the form has been changed. So if something is done in tab 0 and they do other stuff, click on tab 1 -don't change anything, and then to another - the event will fire. It shouldn't be noticeable because if it passed your error routine before, it will pass it again.

Now, you can play with this to make sure it is 100% full proof. You might also want to consider adding the line:

Me.Dirty = False

After the error routine runs and all necessary changes have been made. This will reset the dirty trigger for the form. If you have another error routine that makes use of the form's dirty event, then recommended not to use this.

-dK