SetFocus doesn't work if recordset is empty?

[Jeremy Gollehon]

I'm not sure if I can adequately articulate this problem, but here goes.

I have code that changes the RecordSource a form on the fly based on what's
typed into txtFilter.

It looks something like this:
---------------------------------------------------------------
Me.txtCursorHider.SetFocus

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, NewWhereClause)

Me.txtFilter.SetFocus
Me.txtFilter.Text = Nz(sFilterText, "")
Me.txtFilter.SelStart = Len(Nz(sFilterText, ""))
---------------------------------------------------------------

There's obviously more going on in the code but this is the root of the
problem. The code runs perfectly if the RecordSet returned by the new
RecordSouce is not empty. If it's empty (returns no records) then the code
pukes on the line Me.txtFilter = Nz(sFilterText, "") with a run-time error
424, "Object Required".

I know I can get around this like:
---------------------------------------------------------------
If Me.RecordsetClone.RecordCount <> 0 Then
Me.txtFilter.SetFocus
Me.txtFilter.Text = Nz(sFilterText, "")
Me.txtFilter.SelStart = Len(Nz(sFilterText, ""))
End If
---------------------------------------------------------------

But the problem presents itself again in another place in my app where that
solution isn't very useful.

Can someone set some light on why the .Text property of a textbox can't be
set if there aren't any records in the recordset of the form. Is it that
focus isn't truely being set (again when there aren't any records in the
recordset).

Any help in understanding what's going on here and why would be greatly
appreciated.

Thanks for your help!
-Jeremy

 

[Allen Browne]

Jeremy, this is a real problem, with no simple solution.

If you have a form that:
a) contains no records, and
b) does not accept new records,
the Detail section goes completely blank.
Naturally, any attempt to refer to the (non-existent) control in the Detail
section results in an error.

If you control you are trying to refer to is in the Form Header or Form
Footer section, you can see the control, but Access still pukes in the way
you describe. There is a more detailed description of this issue in this
article:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

One way around the problem is to change the form's AllowAdditions property
to Yes, so it can show the new record when there are no existing ones. To
block the entry of new records, cancel the form's BeforeInsert event. If the
form is based on a non-updatable recordset, this workaround does not apply.

Unrelated to this issue, it's a good idea to explicitly save any edits
before doing anything that requires an implicit save (such as changing
RecordSource.) Also, the Text property in Access is different from the Text
property in pure Visual Basic, i.e. you can set the Value of a control in
Access without having to SetFocus. (You may already know those things, but
they are common issues.)

 

[Ken Snell \(MVP\)]

Another workaround is to set the form's RecordSource property to an empty
string (assumes that the form has code to reassign the correct RecordSource
string when it's needed).

 

[Jeremy Gollehon]

Thanks for the replies guys. The controls are in the form header (which is
why I couldn't understand why the code wasn't working) but reading that it's
a known problem at least stops me from going crazy.

Ken. Setting the RecordSource to an empty string didn't work. Has this
worked for you in the past?

Thanks again,
-Jeremy

 

[Ken Snell \(MVP\)]

Yep, I use this technique in numerous forms. Be sure that the SetFocus step
is after the step that sets the RecordSource to an empty string.

 

[Ken Snell \(MVP\)]

By the way, this workaround assumes that the controls in the Header are
unbound. If they're bound, it won't work.

 

[Jeremy Gollehon]

Ken,
I appreciate your help and it seems to work for the most part. There's
still one small problem.

So we're on the same page, I'm using Access 2003.

The controls on my form header are all Unbound.
After setting Me.RecordSource="" I can .SetFocus to any of the controls
without issue.
The .SelStart property works without problem as well.

Triggering the change of RecordSource from a TextBox (with code in the
keydown event that traps vbKeyEnter) and setting the .Text property of
another unbound textbox on the form header is no problem. However, if a
selection from a ComboBox triggers the change to the RecordSource and I try
to set the .Text property of the same exact TextBox I get error 2115: "The
macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field."

The weird thing is that the text shows up properly in the textbox. So if I
do an On Error Resume Next, set the .Text property, then turn error handling
back on everything "works" properly. Is this something you've run into or
am I treading in uncharted territory at this point?

 

[Ken Snell \(MVP\)]

When you set the RecordSource of a form, it triggers the form's Current
event and that can cause refocus changes on the form -- which triggers
BeforeUpdate and AfterUpdate events too -- which will prevent you changing
the value in the textbox via code. Using the Change event of the control is
probably not the best choice; can you use the AfterUpdate event instead?
Also, using .Text property means that the focus must be on the control whose
..Text property is being references; try using the .Value property instead.

 

[Jeremy Gollehon]

The trigger to update the RecordSource is already in the AfterUpdate event
of the ComboBox. Though I believe your explanation still applies. I had
actually changed to using .Value when Allen suggested it (which worked).
The reason for the continued questioning is my desire to understand some of
the intricacies of Access.

Thanks a lot for your help Ken.