Losing Bound Controls (1 Viewer)

Minty

AWF VIP
Local time
Today, 06:49
Joined
Jul 26, 2013
Messages
10,355
so on post #14, you said:

Nz(Text309.Text) is not the same as Nz(Text309.Text, "")?
Ah ok, in that instance and context they are the same as the .Text property would return an empty string.
But personally, I still think I would prefer to not assume the result, and it shouldn't be used without the replacement value in a query.

Interesting results from this;
Code:
Sub Test1()
    
    Dim vVAr As Variant
    
    Debug.Print vVAr
    vVAr = Null
    Debug.Print vVAr
    Debug.Print Len(vVAr & "")
    Debug.Print IsNull(vVAr)
    Debug.Print IsNull(Nz(vVAr))
    
End Sub
Immediate window
test1
            ' This suprised me
Null
 0
True
False
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:49
Joined
May 21, 2018
Messages
8,463
A variant datatype does not default to Null it defaults to Empty. Is that the confusion?
The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context, and a zero-length string ("") if it is used in a string context.

Don't confuse Empty with Null. Null indicates that the Variant variable intentionally contains no valid data.
The problem is that this gets difficult to see this because most times when you test it you manipulate it. As soon as you do something with empty it gets promoted to the assumed datatype. If you do some string manipulation it looks like it is "" or numeric it looks like 0. I think controls are forcing the variant intentionally so they are NULL not Empty.
 

Minty

AWF VIP
Local time
Today, 06:49
Joined
Jul 26, 2013
Messages
10,355
I think it surprised me as I only use Variant data types where I need to, and almost immediately assign a value/array or object to them.
As you say Empty isn't Null and it's an important distinction. One that I did know but hadn't thought about in this context.

Always learning or improving the knowledge.
 

kirkm

Registered User.
Local time
Today, 19:49
Joined
Oct 30, 2008
Messages
1,257
I have managed a trim back demo of just the problem.
Open frmShowRecord and type 41_062 into text309
This will show data in another text box and populate some other controls.

Now delete 41_062 and press Enter.
This should do nothing, but instead removes data from all the bound controls

That's the part I can't understand. It must be blindingly obvious but I can't see it.
(Pse ignore the class module as I don't believe it's part of the problem and may just complicate things. It isn't run if text309 is nothing.)
Thanks for any advice.
 

Attachments

  • DemoTB.zip
    604.5 KB · Views: 79

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:49
Joined
May 21, 2018
Messages
8,463
In the "Other" tab of the the form property. Change Cycle to Current Page.
You can see why, by adding the navigation buttons back to the form. You are moving to another record.
 

kirkm

Registered User.
Local time
Today, 19:49
Joined
Oct 30, 2008
Messages
1,257
Gee Thanks MajP. I'd never have thought of that. Some inbuild Access function ? Even though there's no next record. I've spend days trying to figure this out.,
Cheers.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:49
Joined
May 21, 2018
Messages
8,463
It is one of those things if you never seen it you would never know.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:49
Joined
May 21, 2018
Messages
8,463
Although ot seems to work, i may not fully understand what i thought. See additional discussion
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:49
Joined
May 7, 2009
Messages
19,169
there is another quick fix to this.
on Design view of your form, Click on Tab Order from the Ribbon.
make sure Text309 is the First Tab in the order.

add Code to your form's Load event so that
the first control that has focus is txtPrefix:

private sub form_load()
me!txtPrefix.Setfocus
end sub

'now you don't need to set anything, since
when txt309 is empty and you press Enter key,
it will Stay on the Same record.
 

Attachments

  • DemoTB.zip
    580.5 KB · Views: 80

kirkm

Registered User.
Local time
Today, 19:49
Joined
Oct 30, 2008
Messages
1,257
Thanks arne, I found that worked too.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Sep 12, 2006
Messages
15,614
It's amazing when you solve a problem like that, and it's something you never thought of, or realised, but if it happens again you know instantly that it's something to check.

Making sure the tab order of controls matches the form display, and setting some controls to not being autotabbed. It's somewhat infuriating to add controls and then realise you need to re-organise the tab order. When you add controls, by copying existing controls, they get generic names, so it can be hard to work out which is which. Often the auto-order doesn't do quite what you want, either especially when you have multiple columns of controls. Fortunately you only have to do all this once per form.

Setting the cycle property to current record where necessary to prevent a record being autosaved when you aren't quite ready for that to happen is useful. I find current record is generally more useful than all records to be honest.

Pleased you got it all sorted.
 

kirkm

Registered User.
Local time
Today, 19:49
Joined
Oct 30, 2008
Messages
1,257
Thanks Dave., This forum is a great resource :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:49
Joined
Sep 21, 2011
Messages
14,048
I actually used that feature for data entry of hundreds of records, and it made data entry much easier/quicker? :)
 

Users who are viewing this thread

Top Bottom