Help Needed to SetFocus on Unbound Text Box in Form Header Section

[dlhappy]

In the header section of a form is an unbound box to type in either LastName or FirstName, depending which field radio button was selected (I always have it set to LastName). I would like the cursor to be on this unbound box when the form is opened. Right now the cursor blinks on the first record in the membershipID field, in the data section.

I do not know VBA and have had help in the past from forum members. This particular form I think was given to me from someone here, and when a record is selected it opens the main posting form/with subform, which I did design.

The issue is that I want the cursor to blink on the unbound (txtfinder) box (the blank box above the "Find Next" when the form is opened so all I have to do is type in the box, rather than first mouse-click that box.

I tried this code based on researching – but it does not work.

Private Sub txtFinder_BeforeUpdate(Cancel As Integer)
Dim hit As Control
Set hit = Me(frmMemberFinderNext)
hit.SetFocus
End Sub

This is the form in Form View (with fictitious member names – I copied the database and deleted all except five records and changed the names, etc for those five). Following the Form View is the Design View for the form.

Any help with getting the focus on the unbound TxtFinder box when the form opens would be appreciated.

Thanks.

 

[June7]

"Does not work" means what - error message, wrong result, nothing happens?

No need to declare control object. Try simply:

Me.hit.SetFocus

But will need to use AfterUpdate event.

 

[Deleted member 147267]

I would like the cursor to be on this unbound box when the form is opened

Then the code needs to be in the form Open or Load event, not any update event?
Nor do I think you can refer to a control on a form like that: Me(frmMemberFinderNext) as opposed to Me.Controls("frmMemberFinderNext")
Try Me.frmMemberFinderNext.SetFocus in the Open event.

 

[dlhappy]

Thanks June & Micron.

June, What I had meant by "does not work" was that nothing happened,

I played around with both of your suggestions and finally, with a little modification, I got it to work.

This is where (Form/Open) and the coding used to make it work.
-----------------------------------------------------
FORM OPEN

Private Sub Form_Open(Cancel As Integer)

Me.txtFinder.SetFocus

End Sub
----------------------------------------------

That was it and it worked!!!

Thanks again, June & Micron for your replies and help.

 

[Deleted member 147267]

Glad we could help you figure it out.

 

[June7]

Glad you got it working. I did test with AfterUpdate event and it did work.

 

[Galaxiom]

Note that due to a long standing bug in Access, it is not possible to address some properties of an unbound control on a form if the form's recordset has no records. For example, when applying a filter with no matches.

You can SetFocus, then ask Form.ActiveControl and the response will be correct. But you still cannot address any of the properties that require the focues such as Text, SelStart etc.

The workaround is to put a subform in the header and use a textbox in the that subform.

 

[isladogs]

There is another method of setting the focus which requires no code. Change the tab order

In design view, click the Tab order button on the form Design ribbon. Then drag the txtFinder item to the top of the list.
When the form opens, the cursor will be in that control

 

[Deleted member 147267]

you still cannot address any of the properties that require the focues such as Text, SelStart etc.

Using what method in what version? Here's 2 methods of doing that in Access 2016, both of which seem to work.

forms!form1.text49.setfocus
?forms!form1.text49.text
45po
?forms!form1.text49.sellength
4
?forms!form1.activecontrol.Name
Text49
?forms!form1.activecontrol.text
45po
?forms!form1.activecontrol.sellength
4

 

[dlhappy]

There is another method of setting the focus which requires no code. Change the tab order

In design view, click the Tab order button on the form Design ribbon. Then drag the txtFinder item to the top of the list.
When the form opens, the cursor will be in that control

Thanks isladogs. Although I did get it to work wtih code based on prior discussion in this thread, I copied the sample datrabase to yet another copy of it so as to try your suggestion, if nothing else for future reference.

When I deleted the code for the focus to be on txtFinder in the header, the focus reverted to the first record's MembershipID field (an Access generated AutoNumber. As a test, I used the non-code Arrange/Tab Order and it worked when I tested to change the order to the LastName field instead of MembershipID in the detail section of the form. However, I could not get it to work so as to initially, upon form open, go to, that is set focus on the txtFinder in the header section.

The point is moot, for this database, as I got the focus on txtFinder in the header section via the code, mentioned in a post, earlier in this thread.

Thanks, agian, for your reply.

 

[dlhappy]

Note that due to a long standing bug in Access, it is not possible to address some properties of an unbound control on a form if the form's recordset has no records. For example, when applying a filter with no matches.

You can SetFocus, then ask Form.ActiveControl and the response will be correct. But you still cannot address any of the properties that require the focues such as Text, SelStart etc.

The workaround is to put a subform in the header and use a textbox in the that subform.

Thanks.

It works now, but I think that what I have is something like that, although I'm not sure if the header section is actually a subform, because there are only text boxes, options, labels, etc., but no actual linked table in that header section.

 

[Galaxiom]

Using what method in what version? Here's 2 methods of doing that in Access 2016, both of which seem to work.

forms!form1.text49.setfocus
?forms!form1.text49.text
45po
?forms!form1.text49.sellength
4
?forms!form1.activecontrol.Name
Text49
?forms!form1.activecontrol.text
45po
?forms!form1.activecontrol.sellength
4

Access 2010 is the latest I have tried it in. Maybe they fixed it.

Did you do something first like apply a filter so the RecordCount on the form was zero?

 

[isladogs]

@dlhappy
The focus will always go to the first control in the tab order list for each section UNLESS you have startup code that sets it to another location

 

[Deleted member 147267]

Did you do something first like apply a filter so the RecordCount on the form was zero?

No, does the form have to have a recordset? It was an unbound control on an unbound form. I didn't think it would matter if the form had no recordset if the control in question is unbound anyway.

 

[Galaxiom]

No, does the form have to have a recordset? It was an unbound control on an unbound form. I didn't think it would matter if the form had no recordset if the control in question is unbound anyway.

It seems that the code overlooks the possibility of unbound controls and checks if the recordset has any records. The recordset would need to be one of the read only types so the NewRecord is not included too.

I encountered the problem with disconnected ADODB recordsets. Maybe that is a factor too.

 

[Deleted member 147267]

Not exactly sure what the first part of that means so I tried it with a bound form with data entry set to yes. The recordset count is 0 (tested). After setting the focus to an unbound control I was able to retrieve the control contents. If you had an issue that led you to believe your statement was true, I'd venture to say it was either fixed since then, or it was due to some other factor.

 

[Galaxiom]

Not exactly sure what the first part of that means so I tried it with a bound form with data entry set to yes.

DataEntry = Yes would have the new record available so I expect the problem not to be manifested.

 

[Deleted member 147267]

EDIT- as another test, I allowed the form to have a snapshot recordset (count is 48) so that New Record would not be a factor (it occurred to me that a form in add mode might be a factor you were pointing out) I was able to get the unbound control content so I'm not sure if that matters.