Select all when control gets focus

[BFish via AccessMonster.com]

Hello all,

I have two unbound comboboxes for record search on my form, each separate of
the other. One has formatting the other not. On focus of either one of
these controls I would like the control text highlighted as if the F2 key is
pressed to overwrite the previous search for easier user operation.

I believe I have exhausted everything I have been able to find in this
newsgroup, which has been absolutely wonderful for reference throughout this
build.

I have tried:

Me.Combo48.SelStart = 0
Me.Combo48.SelLength = Len(Me.Combo48.Text & vbNullString)

and many variations.

On each of the "got focus", "enter" and "click" events with no luck.

I have even tried the dreaded

SendKeys "{F2}", True

statement.

I thought possibly the issue was an unbound control, so I tested in a bound
combobox with same results.

The closest I have been is to use:

Me.Combo48 = ""

on the click event. But that of course clears the control right after the
update (which I would like the user to be able to see their entry if it is
not on the list) and with the formatted combobox if the user clicks into it,
the cursor will remain where it was clicked.

Is there a setting in Access I may have inadvertently turned off (as seems
others are able to make use of the first code) or am I just missing something
quite easy.

Maybe I just have to make do with the zero length string.

Thanks in advance,

Bill Fischer

 

[strive4peace]

Hi Bill,

it just seems to me that you should do

me.combo_controlname = Null
as soon as whatever should happen with the information is successful.

Here is some generic code I use to find records:

Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[BFish via AccessMonster.com]

Thank you for your reply Crystal,

You are correct for my band aid fix. It is better to set the control to null
rather than a ZLS.

Nice piece of work on the General code to be called from any unbound control
for ID lookup.

So with your reply I gather trying to highlight the control data when
entering the control by clicking, tabbing or enter key is not possible?

Here is the main reason why I'm trying to accomplish this endeavor. The
formatted and unbound combobox is an ID lookup on a text Phone field. Data
is stored as digit characters only, i.e. without the parentheses, hyphen or
spaces. This so that the users can enter the relevant data quickly and have
a bit more structured data, but mostly for the speed and is formatted so they
can easily see what they have entered. I have code for on the change event
that replaces the rowsource to match as if the auto expand property is
functioning.

To give the users piece of mind after a successful lookup or a "not in the
list" I would like to retain the typed information from the combobox so they
can verify (even after the message box for "not in the list" comes up with
their data) that that was what they typed. Also for the next lookup entry
(this form stays open until all customer invoices for the prior day have been
entered) a F2 type highlight of the combobox as soon as it has focus would be
ideal to get around the phone mask, particularly when clicked into and would
solve both issues.

An easy answer would be to teach (and I have tried) the users just to use the
F2 key but unfortunately I have one old dog that will not learn new tricks.

Hopefully the above information is discernable,

Thanks again and Woof Woof,

Bill

 

[strive4peace]

FindRecord_N, choosing Light Colors
---

Hi Bill,

"Nice piece of work on the General code to be called from any unbound
control for ID lookup."

Thanks, Bill. I can't help myself ... this is one better -- it can be
put into a general module -- but you have to specify more to use it (at
the least, just one parameter -- not FULLY tested)...

'used by Find combos on forms

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FindRecord_N
' find record given a numeric fieldname and
Function FindRecord_N( _
pKeyFieldname As String _
, Optional pRecordID = 0 _
, Optional pF _
)

'PARAMETERS
'pKeyFieldname = name of numeric key field
'pRecordID = numeric value of key field to look up
'pF --> form reference (if missing, uses active form)

'set up Error Handler
On Error GoTo Proc_Err

Dim f As Form

If IsMissing(pF) Then
Set f = Screen.ActiveForm
Else
Set f = pF
End If

If pRecordID = 0 Then
'if nothing is picked in the active control, exit
If IsNull(f.ActiveControl) Then Exit Function
'set value to look up by what is selected
pRecordID = f.ActiveControl
'clear the choice to find
f.ActiveControl = Null
End If

'save current record if changes were made
If f.Dirty Then f.Dirty = False

'find the first value that matches
f.RecordsetClone.FindFirst pKeyFieldname _
& "= " _
& pRecordID

'if a matching record was found, then move to it
If Not f.RecordsetClone.NoMatch Then
f.Bookmark = f.RecordsetClone.Bookmark
End If


Proc_Exit:
On Error Resume Next
Set f = Nothing
'close and release object variables
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " ProcedureName"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function
'~~~~~~~~~~~~

So with your reply I gather trying to highlight the control data when
entering the control by clicking, tabbing or enter key is not possible?

no, not true:

from the design view of the form:

set BackColor to the highlight color
then, set the BackColor to be Transparent

set the ForeColor to something that will show up with either BackColor

when the user tabs into the control, the BackColor set before the
'Transparent' command will take effect -- maybe something like white or
light yellow?

*** choosing Light Colors ***

Even though you do not have light (faded) colors to pick from on the
color list, here is what I do:

1. pick a fill color from the icon drop-down

2. then click on the builder button (...) for the Color Property (ie:
BackColor, ForeColor, Bordercolor -- Format tab of the property sheet)

3. click on the "Define Custom Colors >>" button

4. drag the triangle up the slider for a lighter shade

***

"...text Phone field..."

aha! I assume you are using an InputMask?

I have had troubles with this exact type of data ...

How I resolved it was to restructure the data for Phone because it was a
difficult solution to implement -- it can be done -- and one way is to
use another control -- another is to keep looking up what is found on
the Change event (very intense). The simplest solution, to me, was to
store duplicate numbers and recognize that "after the fact" -- not the
most efficient -- but it depends on your terms



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[BFish via AccessMonster.com]

Hi again Crystal,

Function FindRecord_N( _
pKeyFieldname As String _
, Optional pRecordID = 0 _
, Optional pF _
)

'PARAMETERS
'pKeyFieldname = name of numeric key field
'pRecordID = numeric value of key field to look up
'pF --> form reference (if missing, uses active form)

Your module code is even more outstanding. Obviously if the form is based a
query with multiple ID fields (one business ID but also many phone ID for
that same business) your code will allow one combo lookup on a business name
and another for phone by just passing the parameters in after update event in
the applicable combo.

I'm all about simple and clean!

no, not true:

command will take effect -- maybe something like white or
light yellow?

*** choosing Light Colors ***

I'm afraid I did correctly pose my question. Is there any way via code to on
a controls focus to select all the data in that control so that when the user
types new data all the old data is overwritten? Much like using the F2 key
after the control has focus and new data is then entered.

"...text Phone field..."

aha! I assume you are using an InputMask?

You are correct I did fail to mention of the Input Mask. And yes I have used
the "on change" event to update as typed with this code:

********************

Private Sub Combo30_Change()

Combo30.RowSource = "SELECT Format([Phone],'(@@@) @@@-@@@@') " & _
"AS NewPhone FROM tblPhone WHERE " & _
"(((Format([Phone],'(@@@) @@@-@@@@')) Like " & _
"[Forms]![frmInvoiceEntry]![Combo30].[Text] & '*')) " & _
"ORDER BY tblPhone.Phone;"

Dim ctl As Access.Control
Dim blnMyFieldHasFocus As Boolean

On Error Resume Next
Set ctl = Me.ActiveControl

If Not (ctl Is Nothing) Then
If ctl Is Me!Combo30 Then
blnMyFieldHasFocus = True
End If
End If

If blnMyFieldHasFocus Then
Me.Combo30.Dropdown
End If
End Sub

***** And on the Forms Current event ******

Private Sub Form_Current()

Combo30.RowSource = "SELECT Format([Phone],'(@@@) @@@-@@@@') AS NewPhone " &
_
"FROM tblPhone " & _
"ORDER BY Format([Phone],'(@@@) @@@-@@@@')
;"
End Sub

*********************

Thanks again and if you have any insight on a "F2 Key" type code please let
me know.

Bill

 

[strive4peace]

Select Whole Control on click
---

Hi Bill,

"Your module code is even more outstanding."

thank you

Thanks again and if you have any insight on a "F2 Key" type code

please let me know.

Short of
SendKeys "{F2}"
.... for selecting the whole phone number when you click in ... how about
something like this:

'~~~~~~~~~~~~~~
Private Sub Phone_Click()
Me.Phone.SelStart = 1
Me.Phone.SelLength = 14
End Sub
'~~~~~~~~~~~~~~~

since you are not storing the symbols, I hard-coded numbers in -- since
the length in the control is > len(fieldname) ... you will need to test
what is there and make appropriate adjustments...

this is annoying in that each time they click, it will keep selecting
everything (so they won't be able to click a digit and change it --
testing to see if they have left the field since their last click
(global variable) might be a good idea - set on GotFocus, clear on LostFocus

another alternative is to make a command button for them to click on

caption --> Select Phone
click [Event Procedure] --> me.phone.setfocus



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi again Crystal,

Function FindRecord_N( _
pKeyFieldname As String _
, Optional pRecordID = 0 _
, Optional pF _
)

'PARAMETERS
'pKeyFieldname = name of numeric key field
'pRecordID = numeric value of key field to look up
'pF --> form reference (if missing, uses active form)

Your module code is even more outstanding. Obviously if the form is based a
query with multiple ID fields (one business ID but also many phone ID for
that same business) your code will allow one combo lookup on a business name
and another for phone by just passing the parameters in after update event in
the applicable combo.

I'm all about simple and clean!

no, not true:

command will take effect -- maybe something like white or
light yellow?

*** choosing Light Colors ***

I'm afraid I did correctly pose my question. Is there any way via code to on
a controls focus to select all the data in that control so that when the user
types new data all the old data is overwritten? Much like using the F2 key
after the control has focus and new data is then entered.

"...text Phone field..."

aha! I assume you are using an InputMask?

You are correct I did fail to mention of the Input Mask. And yes I have used
the "on change" event to update as typed with this code:

********************

Private Sub Combo30_Change()

Combo30.RowSource = "SELECT Format([Phone],'(@@@) @@@-@@@@') " & _
"AS NewPhone FROM tblPhone WHERE " & _
"(((Format([Phone],'(@@@) @@@-@@@@')) Like " & _
"[Forms]![frmInvoiceEntry]![Combo30].[Text] & '*')) " & _
"ORDER BY tblPhone.Phone;"

Dim ctl As Access.Control
Dim blnMyFieldHasFocus As Boolean

On Error Resume Next
Set ctl = Me.ActiveControl

If Not (ctl Is Nothing) Then
If ctl Is Me!Combo30 Then
blnMyFieldHasFocus = True
End If
End If

If blnMyFieldHasFocus Then
Me.Combo30.Dropdown
End If
End Sub

***** And on the Forms Current event ******

Private Sub Form_Current()

Combo30.RowSource = "SELECT Format([Phone],'(@@@) @@@-@@@@') AS NewPhone " &
_
"FROM tblPhone " & _
"ORDER BY Format([Phone],'(@@@) @@@-@@@@')
;"
End Sub

*********************

Thanks again and if you have any insight on a "F2 Key" type code please let
me know.

Bill

 

[BFish via AccessMonster.com]

Thanks Crystal,

You have helped in a big way! See below.

Short of
SendKeys "{F2}"
... for selecting the whole phone number when you click in ... how about
something like this:

'~~~~~~~~~~~~~~
Private Sub Phone_Click()
Me.Phone.SelStart = 1
Me.Phone.SelLength = 14
End Sub
'~~~~~~~~~~~~~~~

since you are not storing the symbols, I hard-coded numbers in -- since
the length in the control is > len(fieldname) ... you will need to test
what is there and make appropriate adjustments...

this is annoying in that each time they click, it will keep selecting
everything (so they won't be able to click a digit and change it --
testing to see if they have left the field since their last click
(global variable) might be a good idea - set on GotFocus, clear on LostFocus

Private Sub Phone_Click()
Me.Phone.SelStart = 1
Me.Phone.SelLength = 14
End Sub


I have tried that code on click event. A combination of the control being a
combobox and click event only updating after an update (research from this
board) the it will select the text but by default will the Microsoft
Programmed click event will leave the cursor at the click point and that
being the final move.

another alternative is to make a command button for them to click on

caption --> Select Phone
click [Event Procedure] --> me.phone.setfocus

This code on a command jarred my memory from probably a year ago on a post I
had stored in the grey matter.

After searching again I finally found some code from Marshall Barton from
back in 2004.

"The only way I can think of to overcome the standard mouse
processing is to place a transparent command button on top
of the combo box. Then use the buttons GotFocus event to
swith the focus to the combo box:

Me.MyCombobox.SetFocus
Me.MyCombobox.SelStart = 0
Me.MyCombobox.SelLength = Len(Me.MyCombobox.Text)"

Works absolutely great and also overides the issue of not being able to edit
an entry due to focus moving to the intended control after the first click,
2nd click will place cursor at the click point.

Thanks to this board, People like you Crystal and Marshall Barton and others
posting back solutions.
This site has been a great reference for many questions that many times I
have not had to post but just perform a search on a subject.

GREAT SITE, GREAT PEOPLE!

Thanks again,

Bill Fischer

 

[strive4peace]

you're welcome, Bill happy to help


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks Crystal,

You have helped in a big way! See below.

Short of
SendKeys "{F2}"
... for selecting the whole phone number when you click in ... how about
something like this:

'~~~~~~~~~~~~~~
Private Sub Phone_Click()
Me.Phone.SelStart = 1
Me.Phone.SelLength = 14
End Sub
'~~~~~~~~~~~~~~~

since you are not storing the symbols, I hard-coded numbers in -- since
the length in the control is > len(fieldname) ... you will need to test
what is there and make appropriate adjustments...

this is annoying in that each time they click, it will keep selecting
everything (so they won't be able to click a digit and change it --
testing to see if they have left the field since their last click
(global variable) might be a good idea - set on GotFocus, clear on LostFocus

Private Sub Phone_Click()
Me.Phone.SelStart = 1
Me.Phone.SelLength = 14
End Sub


I have tried that code on click event. A combination of the control being a
combobox and click event only updating after an update (research from this
board) the it will select the text but by default will the Microsoft
Programmed click event will leave the cursor at the click point and that
being the final move.

another alternative is to make a command button for them to click on

caption --> Select Phone
click [Event Procedure] --> me.phone.setfocus

This code on a command jarred my memory from probably a year ago on a post I
had stored in the grey matter.

After searching again I finally found some code from Marshall Barton from
back in 2004.

"The only way I can think of to overcome the standard mouse
processing is to place a transparent command button on top
of the combo box. Then use the buttons GotFocus event to
swith the focus to the combo box:

Me.MyCombobox.SetFocus
Me.MyCombobox.SelStart = 0
Me.MyCombobox.SelLength = Len(Me.MyCombobox.Text)"

Works absolutely great and also overides the issue of not being able to edit
an entry due to focus moving to the intended control after the first click,
2nd click will place cursor at the click point.

Thanks to this board, People like you Crystal and Marshall Barton and others
posting back solutions.
This site has been a great reference for many questions that many times I
have not had to post but just perform a search on a subject.

GREAT SITE, GREAT PEOPLE!

Thanks again,

Bill Fischer