Losing Bound Controls

[Minty]

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;

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

 

[MajP]

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]

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]

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.

 

[MajP]

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]

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]

It is one of those things if you never seen it you would never know.

 

[MajP]

Although ot seems to work, i may not fully understand what i thought. See additional discussion

Prevent moving to next record when hitting Enter?

I have a form in Access 2003 that should only be working with a single record. I can set the Cycle property to Current Record, but the form still jumps to the next record when I press Enter. My f...

stackoverflow.com

 

[arnelgp]

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.

 

[kirkm]

Thanks arne, I found that worked too.

 

[gemma-the-husky]

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]

Thanks Dave., This forum is a great resource

 

[Gasman]

I actually used that feature for data entry of hundreds of records, and it made data entry much easier/quicker?