Versioning Issue

[Tim Johnson]

I have created a form with a number of unbound text boxes. The text boxes
control source update frequently based on user actions.

The form works great in both Access 2007 and 2003. However; when a user
tries the same actions in Access XP (2002), they receive error number 3125
and the error description lists the form's RecordSource (it's the semicolon
throwing error 3125, I assume, however, why it's looking at that property in
the first place is where I'm confused).

Below is a sample of the code I'm using to clear everything. This is the
code that is throwing the error:

Sub RefreshScreen()
Dim ctl As Control

For Each ctl In Me.Controls

If Left(ctl.Name,3) = "dyn" Then

If Right(ctl.Name,1) = "t" Then

Me.Controls(ctl.Name).ControlSource = "="""""


Me.Controls(ctl.Name).BackColor = 16777215
Me.Controls(ctl.Name).ForeColor = 0

ElseIf Right(ctl.Name, 1) = "w" Then

Me.Controls(ctl.Name).ControlSource = "=0"

End If

End If


Next ctl

Does anyone have any idea why this would throw a Field Name error in Access
XP, but work perfectly fine in 2003 AND 2007?

 

[Allen Browne]

Tim, I don't know why, but perhaps this will help track it down.
Suggestions:

1. Double-check that the control names are all different than the field
names they get bound to. Access gets confused if a control has the same name
as a field, but is bound to something else.

2. Next, before binding/unbinding, make sure any edits are committed:
If Me.Dirty Then Me.Dirty = False

3. Double-check if there is any possible way that a field is disappearing
from the RecordSource.

4. Pause the code when it errors, and examine the Name of the ctl being
examined. Is it failing on a specific control (which could be a clue to the
issue)? Or is it just failing on the first one?

5. Perform the basic maintenance steps suggested here:
http://allenbrowne.com/recover.html
Several of those items could have a bearing here, such as Name AutoCorrect
(always a suspect when Access gets confused about names), decompile (always
a possibility when crossing versions), references, ...

6. You say the error lists the form's RecordSource? Presumably this is a SQL
statement. Try saving it as a query, and using the saved query name in the
RecordSource property. Does this make any difference? Is there anything
about that query that gives a lead?

Let us know how you go tracking this down.

 

[Tim Johnson]

Thanks for your response Allen,

1.) The only field present in the form currently is the PK (it's not
necesary, but I opted to use it instead of a public variable in case we want
to add other fields later). All dynamic data is loaded through class
modules/collections for performance purposes - it would require several
cross-tabs if I did this differently, and for our remote users (using
citrix), the performance is just terrible that way. Because the PK is the
only field, and it's control name doesn't fit within the conditionals listed,
it shouldn't be the culprit.

2.) Again, because the only field is the PK, no data is being changed here;
it is commited by the class modules using the RecordSet object, or via
RunSQL. The collections are dumped prior to the changes, and loaded again
after data changes.

3.) When listing the error description, I am informed that the field name
"SELECT POG_ID FROM tblPOG;" is an improper name due to punctuation, the PK
field (when set to visible) still displays, though, so the RecordSource is
not being affected.

4.) I used the Debug.Print command in my error handler to do exactly this,
but it has no problem listing the field name just prior to the command that
attempts to change the ControlSource (the line that throws the error).

5.) I haven't looked at this just yet, but will when I get to work tomorrow.

6.) I'll have to try this one tomorrow, as well.

If I wasn't trying to deploy this to users in a Citrix Server environment,
I'd be happy as a clam to simply upgrade the client using it to 2003, but the
multi-user environment and licensing costs that it carries with it are going
to be more than it's worth. We may have to venture the route of the Runtime
instead; although explaining to users the method by which they'll have to
switch back and forth between versions every time they want to open a
different database may prove problematic, too.

Thanks again for your insight. I'll try options 5 & 6 tomorrow and let you
know the results. If those don't solve it, I'll also update if and when I
have a solution.

 

[Tim Johnson]

Well, I never could figure out what the cause of this was.

I ended up abandoning this code for an alternate method that is working fine.

Thanks for your suggestions, cheers!
Tim