No need for table fields on a form (3 Viewers)

[Cotswold]

After all of the years using Access, the other day I accidentally dropped on something I had never come across before.

I have always used form variables as memory variables. Mainly just because I can in Access and also so I can easily see them if I need to. I will store what otherwise would be a Globals on an always-open form on startup. Also regularly using form variables when processing data and passing it between screens and objects. During development it has the advantage of displaying those variables for me to check as they are modified, or added.
When the program is completed I'll just hide them.

One thing I regularly do is to use data from a datasheet and write it to another form, use them for calculations, parameters, passing to functions etc.
In the past if a datasheet had variables I needed, then I would have all of the fields required on the datasheet form and then go to the bother of hiding, and locking them. By accident I found that simply having the variables in the underlying table, or query that feeds the datasheet is actually sufficient.
So to use a field variable a form simply has a query or table as its datasource and there is need to have all of the actual fields required on the form.

I attach a simple example with a datasheet in a subform (F10Test_1) with two fields in use and three other fields shown on its mainform (F10Test_0).
The fields on the mainform are only in the underlying table or query to the datasheet and have no need to actually be on the datasheet form.

I thought I'd post a simple example for those out there who don't know about this shortcut.

 

[isladogs]

I'm not sure I understand what your example app is showing.
However, it has always been true that fields in the form record source don't need to be form controls to be available for use
That isn't the case on reports where you do need to include all fields as controls if you need to make use of them. However they can be hidden

 

[Isaac]

Nice, @Cotswold. Not sure I've ever used form record source columns without them being 'on' the form, either

 

[arnelgp]

you can even shorten the control source on the main form to:

=[F10Test_1]![F3]

 

[Cotswold]

However, it has always been true that fields in the form record source don't need to be form controls to be available for use

I wasn't aware of that fact, hence the post.

 

[DickyP]

For what it is worth I've always (at least since Access 97 which formally introduced classes), had a class which I call clsAppDetails, which I instantiate as ThisApp at Start Up holding all the data items I may want during a session. It may, or may not, been a sensible solution to Access purists but it works well.

 

[Cotswold]

..........It may, or may not, been a sensible solution to Access purists but it works well.

I often think that your own way of doing most things is the better way.
A bit like other peoples kids, who are always more of a damn nucance than yours hve ever been!

 

[Cotswold]

=[F10Test_1]![F3][/code]

excellent

 

[MajP]

I'm not sure I understand what your example app is showing.
However, it has always been true that fields in the form record source don't need to be form controls to be available for use

I do not get the example either. It sounds as @Cotswold discovered something that is not an issue. I guess the bigger question is why did you believe you had to add the controls in the first place

By accident I found that simply having the variables in the underlying table, or query that feeds the datasheet is actually sufficient.
So to use a field variable a form simply has a query or table as its datasource and there is need to have all of the actual fields required on the form.

I do not get it because I see people do this all the time with people adding tons of hidden controls, because they think there is some kind of need to do that. This is pretty persuasive and always wondered if this is some kind of carryover from the earliest days of Access.

 

[The_Doc_Man]

Nice, @Cotswold. Not sure I've ever used form record source columns without them being 'on' the form, either

This is a reply to your comment, Isaac, but in fact it is generally applicable. If you have ever run into issues because a control and its underlying field cause confusion, it is because ALL of the fields of the recordset's Fields collection are visible in the class module's VBA whether or not they have corresponding controls on the form, visible or otherwise. But if they DO have corresponding controls and the control name is the same as the field name then Me.mumblefratz could actually be an ambiguous reference. This is why we often suggest that you should take great care to name a control and its corresponding field differently. So to remedy my ambiguous reference, you would leave the field name as-is and name its associated control as txtMumblefratz so that you never have to untangle ambiguous references.

 

[sonic8]

However, it has always been true that fields in the form record source don't need to be form controls to be available for use

It has not always been the case and it is still not always true today (in most recent Access versions).
Only if Access had the opportunity to set up the form in that way, which is not always the case.

 

[Cotswold]

I do not get the example either. It sounds as @Cotswold discovered something that is not an issue. I guess the bigger question is why did you believe you had to add the controls in the first place


I do not get it because I see people do this all the time with people adding tons of hidden controls, because they think there is some kind of need to do that. This is pretty persuasive and always wondered if this is some kind of carryover from the earliest days of Access.

Correct, it isn't an issue, it never was an issue. I thought I'd explained why I posted in #1 & #5. Basically it is something I've done since A97.

 

[MajP]

It has not always been the case and it is still not always true today (in most recent Access versions).
Only if Access had the opportunity to set up the form in that way, which is not always the case

Are you saying there are cases where even using bang notation will fail without a control on the form? I understand there are cases where compile time Dot notation will not work without a control on a form, but the run time bang always seems to works without a control far as I know. If so can you explain a way to recreate a case where even bang will not work? I have seen cases where for some reason dot will not work, but if I add it to the form, save it, and then delete it, it works. In those cases I assumed it was some kind of bug and the fields were not added to the controls collection as "accessfield" controls. Adding then deleting seems to force it to work.

 

[Isaac]

This is a reply to your comment, Isaac, but in fact it is generally applicable. If you have ever run into issues because a control and its underlying field cause confusion, it is because ALL of the fields of the recordset's Fields collection are visible in the class module's VBA whether or not they have corresponding controls on the form, visible or otherwise. But if they DO have corresponding controls and the control name is the same as the field name then Me.mumblefratz could actually be an ambiguous reference. This is why we often suggest that you should take great care to name a control and its corresponding field differently. So to remedy my ambiguous reference, you would leave the field name as-is and name its associated control as txtMumblefratz so that you never have to untangle ambiguous references.

Yes, I agree. For form controls I am pretty old fashioned and despite modern ideas to the contrary, still use hungarian notation pretty much all the time, like you said ... txtUserName.

good naming conventions - including proper case capitalization - becomes even more important in other environments like sql server. with the Redgate sql prompt add-in, Intellisense becomes incredibly aware of things to even understand as you type t..u..n that you are looking for TxtUserName, as long as it is proper-cased.

 

[Isaac]

I often think that your own way of doing most things is the better way.
A bit like other peoples kids, who are always more of a damn nucance than yours hve ever been!

Lordy, lordy, ... don't I just hate other people's kids! (I'm kidding, hate is a strong word).
But part of that wasn't just the magical "they're MY kids" ,, it was that I actually reared my kids to behave quite well - they often got compliments on it when they were little. It's pretty simple, start disciplining them with teeny-tiny amounts of discipline as young as a year old. Then you won't wonder why you have a 4 yr old one day that you can't control.

 

[isladogs]

It has not always been the case and it is still not always true today (in most recent Access versions).
Only if Access had the opportunity to set up the form in that way, which is not always the case.

Perhaps you could explain both parts of that response.
In which versions doesn't it work?
I've no idea what your second sentence means.

On a quick check, I know for a fact that it has worked since at least A2000 and probably earlier than that

 

[Richard Rost]

One thing I’ll add is that even in cases where you’re passing values between forms, like using Forms!CustomerF!CustomerID to default a value on another form, you often don’t need the control on the form at all since the field is already in the record source. I’ve tested that myself and it works just fine in many cases. That said, it’s not 100% consistent. I’ve definitely run into situations where Access just refuses to resolve the reference unless the control is actually on the form. So for me it’s more of a “belt and suspenders” approach. If I know I’m going to reference it externally, I’ll sometimes include the control anyway just to avoid those weird edge cases, but otherwise I don’t bother cluttering the form with hidden fields. If I'm not sure, I'll just include the field and make it hidden.

 

[DickyP]

If you want to guarantee that your references to a contro lis correct you can always use the  formname.Controls("controlname") form of notation.

 

[tvanstiphout]

If you want to guarantee that your references to a contro lis correct you can always use the  formname.Controls("controlname") form of notation.

But what if "controlname" refers to a non-existing control? I would only find out at runtime that it does not exist.
I think Me.controlname is better, because it provides compile-time checking (as well as intellisense).

 

[RonPaii]

One thing I’ll add is that even in cases where you’re passing values between forms, like using Forms!CustomerF!CustomerID to default a value on another form, you often don’t need the control on the form at all since the field is already in the record source. I’ve tested that myself and it works just fine in many cases. That said, it’s not 100% consistent. I’ve definitely run into situations where Access just refuses to resolve the reference unless the control is actually on the form. So for me it’s more of a “belt and suspenders” approach. If I know I’m going to reference it externally, I’ll sometimes include the control anyway just to avoid those weird edge cases, but otherwise I don’t bother cluttering the form with hidden fields. If I'm not sure, I'll just include the field and make it hidden.

I use public user defined form properties for external reference. It has the added benefit of typed returns and pre-formatting.