problems with changing recordset of form

hi guys!

recently Christian (Bitsqueezer) was helping me out with a question and he shared this portion with me below

But be careful: A parameterized query assigned through the Recordset property sometimes can be a little bit strange, depending on the query, the fields in the form, the functions you implemented. It needs more tests than a normal bound query.

so i also did some reading up and came across this on FMS's website (http://blog.fmsinc.com/microsoft-access-inconsistent-compile-error-for-a-field-reference-in-a-form/)

really good stuff there.

so i just wanted to ask yall and also Christian in this question, what are the problems that yall have faced using a parameterised query as a form's recordset?

thanks so much guys!! = ))
developingprogrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

although the article has nothing to do with my statement above:

The reason for that behaviour was already described on the page: They changed the RecordSource programmatically.

Using the "!" should in my opinion ALWAYS be avoided in VBA, there is NO need to use it!
It is only needed in references in objects like in an "=" expression in a control or something like that.

ALWAYS use the "." to make sure the compiler can check anything behind, the "!" will prohibit any check after this sign.

Is a field of a record source a real property of the form class? No, it's not. The designer implements it as a property when you initially set the RecordSource property. So in a runtime where you can't use anything which has something to do with design it is most likely that using that when changing the RecordSource will cause problems (and the article proves that). It is more surprisingly that the Access programmers let the VBA programmers do that. I would have done it in a way that you would not be able to use "Me.MyField" in the form class when you set a RecordSource to a new one which does not contain "MyField".
Moreover you should remember to give a control ALWAYS another name as the underlying field name, which also prevents compiler errors.
You should simply not access fields as properties if you ever change the RecordSource. If you don't do that it's safe and the compiler can check that (and OF COURSE the user should NEVER get an ACCDB/MDB/ADP, ALWAYS ACCDE/MDE/ADE, that's the minimum to secure a frontend!).

What I meant in my statement above was, that i.e. in ADPs this method (using a parameterized query as form recordset) can produce problems. The difficulty is that Access creates INSERT/UPDATE/DELETE/SELECT commands to handle the changes made by the user. Depending on the used query (in case of ADP of course ADO) Access is not able to refresh the form, if you press F5 it loses the connection as it is not able to reapply the parameter to the query sometimes. Also in DAO sometimes you will see that the form does not react in the same way as with a simple bound form, that's what I meant with "you must test that in your scenario".

Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Hi

Not wanting to split hairs or anything, but can I just offer some peripheral nuances and other takes on the above?
As this subject falls into personal opinion in some cases. :-)

For example, renaming controls to always differ from the bound (ControlSource) field name....
That can work well - but it's not required if you stay aware of which type of object you're referencing.
Me.FieldName will refer to the control - if so named.  (Access does indeed add a property to the form which returns this control object.)
If refering to a RecordSource field which isn't named the same as a control, then it refers to an AccessField object (again created by Access as a property of the form at design time.)

If the form's source can change and you're assuming properties - then yep, you're very much in your own minefield.  (Potentially even with fields that do still appear - but obviously with those that don't.)

FWIW
Me.Recordset.Fields("Fieldname")

Open in new window

would generally be fine.

Refering to the advice:
>> Using the "!" should in my opinion ALWAYS be avoided in VBA
>> ALWAYS use the "." to make sure the compiler can check anything behind, the "!" will prohibit any check after this sign.
>> You should simply not access fields as properties if you ever change the RecordSource.

They seem slightly contradictory.  I don't see why the Bang (!) is to be avoided as a rule.  Are you saying only in certain circumstances, or just to use
Me.Controls("FieldName")
Or to use Bang only when refering to controls only - but not AccessFields when changing sources (which I certainly agree with).
Bang would push evaluation to runtime yes - but if your bound source is changing - that's a good thing.

I don't see how the Access team could have avoided allowing property references to fields under certain circumstances only.
At design time - they're valid.  If it changes at runtime - what would happen?  An error is raised if the source changes and any property references exist?
(Them failing as they do is pretty much what I'd expect.)

The ADP recordset binding and underlying object permissions requirements - yeah, I don't think that led to the downfall of ADPs, but lack of advancement of the functionality/stability can't have helped.  (They could have been great with just a fraction of the SharePoint publishing investment that there has been.)

Just random ramblings. :-)

Cheers.
Gustav BrockCIOCommented:
Using the "!" is what I ALWAYS do in VBA.

Be careful with "always" and "never" statements.

/gustav
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BitsqueezerCommented:
Hi LPurvis, Gustav,

of course you can say it is only an opinion, and Gustav, only because you are always using the "!" doesn't mean that this is the correct way. You CAN jump from the bridge to get into the water but you can also think about if it wouldn't be better to go over the bridge and down to the water to jump into it very safe. The first method is possible, but you will most likely die.

I know that this theme always creates big discussions and that there are also different opinions in there, I also know that MS themselves recommending the use of "!" (but we all know the quality of MS recommendations, to mention only DAO/ADO/DAO...).

If you think about it, WHEN makes a "!" really sense in VBA? I say absolutely and with no exception: In NO situation. The "!" makes it impossible to let the compiler do a check for anything later in the line. So simply don't use it.
In special cases like the mentioned change of a RecordSource you, LPurvis, have already written the alternative to dynamically access the field without issues, using the Fields collection of the recordset object. If you change a RecordSource dynamically, you most likely do have a list of the used fields as variables or constants or you read that out of a table and so you can insert the variable here without any problem. You can't use "Me!MyVariableName" to access "MyField".

In opposite I can simply write "Me!Mickymaus" in the code and the compiler will ignore it, but my runtime will throw an error which I could have avoided if I had the compiler check all the names.

So please: Give me ANY example where the "!" makes sense over "." (except the already mentioned expressions in forms/queries where you are forced to use it).

It's similar in case of the "don't use the same names for Controls and Fieldnames". Sure, Access will create a property in the form with the control name and normally you can be sure that the control name has the priority when Access searches the name. But "normally" is not enough, sometimes Access finds the field name property before the control name and then you could not access the BackColor property for example because your code currently works with the field which only has the Value property. Moreover the code will be more readable as you can see from the name of the control that you definitely are using the control and not the underlying field.

These methods both help to write better programs and cleaner code. Maybe others have other opinions and I don't want to CONVINCE anybody but if someone asks me to give mine, that's it and in my opinion there is a clearly NEVER and ALWAYS.

Cheers,

Christian
Leigh PurvisDatabase DeveloperCommented:
Hi

You seem to have taken this a bit personally (judging by caps use) - I was looking to avoid that and I don't want to end up trying to hamer this home.
However, since you ask...

You stand by the absolute position that the Bang is not to be used at all.
The single rationale for this seems to be that the compiler doesn't check it for validity.
That's all?  (Besides the compiler, I find testing my code/forms works rather well in determining any incorrectly referenced fields/controls.)
My example of:
Me.Recordset.Fields("Fieldname")
will also not be checked by the compiler in exactly the same way.
(Though it's worth noting that they're refering to different objects.)

The example of a variable, well I don't think it's fair to assume that a variable for the field names would be held.
And of course, you'd not try to use Bang with one of those.  You're stating the point as if we were saying Bang is the only method to achieve anything.
The dot wouldn't alow you to use a variable any better.
Me.VariableName doesn't get you anywhere.  You have to use the collection then (see later for more on that).

FWIW - yes I use dot a majority of the time.  (I'm not trying to say that Bang is the be all and end all.)  But I absolutely maintain that this is not a case for a dogmatic insistence that a certain method be avoided.
Yes, what we give here is opinion.  But backed up with facts makes the difference.
Comments like
... and normally you can be sure that the control name has the priority when Access searches the name.  But "normally" is not enough, sometimes Access finds the field name property before the control name
are anacdotal at best.  That would imply a major failing in Access (i.e. a bug) and should have been raised with MS (assuming it was reproducable and not attributable to something elsle.)

What can't be refuted is simple fact:
Me!FieldName
is syntactically identical to
Me.Controls("FieldName")
by Access.  (That used to be a recommended way of referring to controls actually at one point - but again, that's a suggested preference for a reason - not an absolute.)
The controls collection in this instance includes the vitual controls that are AccessField objects.  (You'll not see it in the Count or if you iterate the collection - but it's returned by name.  Much like instantiated forms in the forms collection.)
The AccessField object is then available (without many of the standard control or field properties - but available none the less).

It's just an object.  Not the devil.  Deciding when it's OK to use it and when not is part of being a developer.  (And changing source forms is absolutely No. 1 on the list of when to.)
You mention citing a time when to use it?  Why use the recordset instead?  It's a longer, more involved, less efficient expression.

I'm just saying - this is not a hard and fast rule occasion.
It's not like we're talking about normalisation, splitting FE/BE etc.

Cheers.
BitsqueezerCommented:
Hi LPurvis,

no, no, that big letter writing was not ment to be anything personal, it was only ment as accentuation, maybe I should write such things in italic letters or anything like that - sorry. It is really sometimes difficult to write something in a way that others understand the way it was meant to be understood, that's a problem in all written communication.
Be sure that I don't take anything here personal, in the same way I do not want to affront someone with what I write.

You are of course right in comparing the statements about using a variable with the dot is also not possible and you are also right that the compiler can also not test the value if you use a literal as index in a collection. In that case I always use a constant so the compiler can at least check the constant - and I only need to check the value of the constant once, but can use it as often as I want. That's another "must have" I try to not forget that whenever I use literals, always working with constants, variables or values coming from a settings table.

You're also right that using a collection object will behave in the same way as the "!", it cannot check the object inside the collection with the compiler. So I don't say that there are not also other problems with i.e. IntelliSense or the use of non-typed collections.

I write another example which maybe make it clearer what I mean:
    Me.Controls("MyCheckBox").BackColor = RGB(100, 0, 0)
    Me!MyCheckBox.BackColor = RGB(100, 0, 0)
    Me.MyCheckBox.BackColor = RGB(100, 0, 0)

Open in new window

You will see that if you compile that, only the last variant will be checked by the compiler and will throw an error at compile time (for other readers: The CheckBox control does not have a BackColor property).

You're stating the point as if we were saying Bang is the only method to achieve anything.
Sorry, that was a misunderstanding, I didn't want to say that. I only say, there is NO advantage using the bang over a dot, so why should one use it?

That would imply a major failing in Access (i.e. a bug)
We all know that Access and also VBA is not free of bugs...:-)
Maybe you think it's a story only but it happened to me in the past and also to others if you search through different Access forums. I unfortunately have no example to prove that, but that's not important - if it CAN happen (oh, sorry, if it can happen) and I have a clear way of avoiding an issue with that then I of course do that.
Please also remember what I said about clear code and possibility to distinguish between field and control object simply by name, so it's another reason if the story is to vague for you.

Another often heard point is that "!" is more performant. Let's see what happens here (I tested only seconds so I didn't use Timer):
Public Sub TestTime()
    Dim d As Date
    Dim x As Long
    Dim s As Variant

    d = Now
    For x = 1 To 1000000
        s = Forms!frmTable1!Test
    Next x
    Debug.Print DateDiff("s", d, Now())
    d = Now
    For x = 1 To 1000000
        s = Form_frmTable1.Test
    Next x
    Debug.Print DateDiff("s", d, Now())
End Sub

Open in new window

This is the result on my current laptop Intel i7:
12
8

So we also get a better performance using the object notation ".". And why is that the case? Because the VBA interpreter must check anything after the first "!" and again after the second "!" if the object exist. It doesn't need that in case of the dot: The compiler did it once and that's enough. Moreover you would not get an IntelliSense support in the sample above as "Forms" cannot return an IntelliSense contents - in opposite to the class module name which uses the only loaded instance of the form (which in most cases is only one, in an object way of opening a form that would of course not be possible).

Two more reasons not to use "!": The performance is better using "." and also the IntelliSense support is better.

Still only an opinion?...;-)

Cheers,

Christian
Luke ChungPresidentCommented:
Given the option, it's better to generate a compile time error rather than a runtime error since the former is caught by you as the developer before deployment, and the latter is caught by the end user after deployment.

The . allows the compiler to verify the control name exists. The ! only checks when the line of code actually runs. If you assume the same rate of typos when typing the name after the . or the !, then it's better to use . to catch those mistakes. Since . also offers Intellisense, it should reduce the number of typos as well.

It comes down to using a process that minimizes the chances for errors to improve the quality of your application.
Leigh PurvisDatabase DeveloperCommented:
It's actually intellisense rather than the compiler that sees me use the dot more than bang.
Yes, I get compile time checking - and I'm happy to have that, but it wasn't a decisive reason for first prefering to using dot.
I still find it hard to accept that end users receiving runtime errors from using Bang are such a concern.  What do your developers do when using recordsets or calling stock functions?  Wait for the end users inform them of the typo bug in the mis-named field/function?  (Or do they check/catch it in testing, as soon as they open their form.)

I'm curious what syntax you both use in recordsets actually.  Is it:
strVariable = rst.Fields("FieldName")
That's the only way you reference recordset names?
If so - why?  What advantage do you believe that is giving you?
(And please don't cite "use of variable" as that remains obvious that you'd revert to that syntax for that use.)
Yes, it's different in forms where we can make use of dot for the property convenience and conformation.  But it's still a choice.  Referring to a form AccessField with a bang is no less risky than doing so in a recordset, certainly typo-wise.

>> Another often heard point is that "!" is more performant.
I don't know anybody that I'd listen to who would tell that tale... I, for one, would not imply it.
None the less, performance difference between the two really doesn't come into it these days (that's an old argument from what, Access 97 days? :-p).  Time the difference between a single execution, what's that?  (Or do you regularly iterate a control a million times? ;-)
But even then, that still would only make is a better option.

>> Still only an opinion?...;-)
Yes - absolutely.  It's is absolutely still an opinion of preference.  Bang would have to be fundamentally flawed for it to be considered a non-option.

I repeat again.  I use dot a vast majority of the time (not for speed anymore but the other reasons mentioned).  But I would not dream of castigating someone for siding with Bang or suggest that dot is the only way.
Bear in mind - you're not trying to convince me that dot is good (you're not supposed to be anyway) - but that Bang is unaceptable.  While there is even one worthwhile use of it (which should still be very much in Luke's mind given the article), then it can't be dismissed.
Unless you prefer writing out
Me.Controls("FieldName")
rather than
Me!FieldName

(I've done both of those too over time!)

Cheers
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Wow ... here we go AGAIN (emphasis) ... Bang vs Dot
:-(
BitsqueezerCommented:
Hi LPurvis,

I think you still didn't get my point. I say: There is simply NO advantage of using the "!" so why should I ever use it? If you think a shorter code is an advantage, yeah, that's truly your opinion.

My opinion (and that is an opinion) is that a verbose code is better understandable than any shortcut, that's why I prefer to use VB (BASIC in general) and not C, Java and similar languages.

So if you want to do that, I don't want to convince you, as I said.
For me, yes there is indeed only the rs.Fields("Field") way of writing it or in most cases additionally also rs.Fields(cMyFieldName). I do not need to search through the code to find out where I have used that and yes, I also normally do not access fields in forms not even with Me.Fieldname, if I want to do that I always create a control, a hidden, if needed, and access the field by using a compiler safe Me.ControlName where I can be sure that this will even not break if I change the RecordSource.

What do your developers do when using recordsets or calling stock functions?  Wait for the end users inform them of the typo bug in the mis-named field/function?  (Or do they check/catch it in testing, as soon as they open their form.)

If you've enough time to do intensive tests, superb. But really - how often does it happen that a rarely used function was not tested in the way that the error comes up so it is indeed the end user which informs us about an error? I'm sure you do not want to say that all your programs are such perfectly tested that this NEVER happens.

So again, the point is: WHY should I use the worse technique which I can simply avoid by using another character and moreover adding more comfort in faster writing by using IntelliSense (which in some cases of course also works in using the "!")? It even happened when I wrote the time test above, I couldn't remember the correct spelling of the name of the form. Using Forms! it was not possible. Using Form_ and press ctrl-space I have a list of all forms with modules. Using Forms!frmTable1! it is not possible to find the wanted field with IntelliSense. Using Form_frmTable1.Test IntelliSense shows that to me (and that's also the reason why I rename all controls where any control which has a ControlSource has the same prefix "ctl" so I have a simple to find list of any control and with that the list of fields).

For me using the "!" is a strict no-go. And if anybody asks me I can show reasons why I think so. But nobody can show reasons why to prefer "!" over the ".", so again, I have facts, and I build my opinion from that, people who use "!" have only a preference to use it, but no facts why not use "." instead. That's pure opinion.

I go even far beyond this, although that was only an experiment and I do not use that currently in any project:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_11029-LINQ-for-VBA-and-Typed-Tables-Queries-and-Field-Names-using-VBA.html

This makes it even possible to never need to use any manually typed constant, you can choose everything very comfortable by choosing from enums.

But I think, the discussion now doesn't lead to new knowledge, we both have said what we want and we both have our personal way of doing the things right...:-)

Cheers,

Christian
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" (Besides the compiler, I find testing my code/forms works rather well in determining any incorrectly referenced fields/controls.)"
Exactly.

Let me tell you what I ALWAYS do in VBA code when referring to a Field in a Table or Query:

Me![SomeFieldName] = <whatever>

or

<whatEver> = Me![SomeFieldName]

This way, for me (no pun), I can immediately tell that I am referring to a Field in a Table or Query ... and not a Property or Control on a Form - when looking through reams of code 6 months later after I wrote it.  It's  just that simple.

I have no concern about Compiler errors or Runtime errors in this context because ... I am going to *fully test* before any deployment happens.  In fact, I constantly test code as it's written - in chuncks you might say. Consequently, I will certainly find any  typos, which is going to be rare in itself.

Disclaimer:
Any use of CAPS or italics (et al) is strictly for emphasis. No screaming or madness is intended or implied :-)

mx
Leigh PurvisDatabase DeveloperCommented:
>> I think you still didn't get my point. I say: There is simply NO advantage of using the "!" so why should I ever use it?

You're massively missing my point.  As, possibly, is everyone it seems.
This is not Bang Vs Dot.  I've written soo many post about that it's untrue - massive ones and I won't be doing so again here.
This is about assertions that there is only one way - when in fact there are options.
Personal opinion presented as the only option.  I hate seeing that.
I tried to go gently about it (Gustav was a bit more forthright) and perhaps I shouldn't have.  It's seemingly confused the issue.

Why would anyone ever use Bang?  I thought we'd covered that... On forms who's record source changes.

>> If you think a shorter code is an advantage, yeah, that's truly your opinion.
That's not my point nor have I ever said that shorter code is my objective here.  (Efficient code massively yes, but long code whe it's not required is just that.... long code - unrequired.)
My point is, if you assert there's no point in ever using Bang, then what is the point in
Me.Recordset.Fields("FieldName")

Anyway.  It's a moot point now.
Sorry to the OP if this all seems lost in the wash now.

Must get back home...
BitsqueezerCommented:
Hi mx,

it's OK if you want to do that, but that's only because you prefer to do so, nothing else.

I like your disclaimer...:-)

Hi LPurvis,

maybe you mixed up older discussions with what I've said. When did I wrote that the dot is the only possibility? I quote myself from the initial post: "ALWAYS use the "." to make sure the compiler can check anything behind, the "!" will prohibit any check after this sign." There is no "you cannot use the !".

More and more it seems that you are the one of us which takes this thing personal...I only wanted to discuss that as you opened it for discussion...:-)
Was not my intention to make you angry, so if you are, I beg your pardon.

Cheers,

Christian
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"it's OK if you want to do that, but that's only because you prefer to do so, nothing else."
On the contrary ... if refutes this comment:

"Using the "!" should in my opinion ALWAYS be avoided in VBA, there is NO need to use it!"

The is *no* reason to always avoid it.!!..! :-)

:-)
developingprogrammerAuthor Commented:
guys, in my opinion i always avoid ! because if i were to work in the library and the librarian walked behind me and saw me typing so many !s i would get chased out immediately!!!! KEEP QUIET IN THE LIBRARY!!!!

hahahahhaa but yup yup guys, library aside, i think that yall all have fantastic opinions and it's great for someone who's just learning VBA like me to learn and hear your opinions! thanks so much for all your sharing guys!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.