Test for Field Type

I have the following code trying to only allow the spell checking to text or
memo fields. But since the Screen.PreviousControl does not contain the
Fieldtype property this code fails. Any ideas how to test for the field type
property to eliminate error indicating object not supported?

Private Sub Command19_Click()
On Error Resume Next
Select Case Screen.PreviousControl.FieldType
Case dbText Or dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End Sub

Thank you,
Robin

"Robin" wrote in message
...
I have the following code trying to only allow the spell checking to text
or
memo fields. But since the Screen.PreviousControl does not contain the
Fieldtype property this code fails. Any ideas how to test for the field
type
property to eliminate error indicating object not supported?

Private Sub Command19_Click()
On Error Resume Next
Select Case Screen.PreviousControl.FieldType
Case dbText Or dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End Sub

Thank you,
Robin


Something like this might work:

With Screen.PreviousControl
If .ControlType = acTextBox Then
If Len(.ControlSource) 0 _
And Left(.ControlSource, 1) "=" _
Then
Select Case Me.Recordset.Fields(.ControlSource).Type
Case dbText, dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End If
End If
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Worked perfectly, thank you for your help.

So if I understand what's going on he Because I used a command button
(which has no controlsource) I had to use the screen.previouscontrol to refer
to the text/memo control. The controlsource tests are simply to assure there
is text in the field to test. Once the With screen.previouscontrol loop was
initiated I can then refer to the recordset using the Me.... references as I
would have intially expected. This gets cooler by the minute.

Thanks so much.
Robin
"Dirk Goldgar" wrote:

"Robin" wrote in message
...
I have the following code trying to only allow the spell checking to text
or
memo fields. But since the Screen.PreviousControl does not contain the
Fieldtype property this code fails. Any ideas how to test for the field
type
property to eliminate error indicating object not supported?

Private Sub Command19_Click()
On Error Resume Next
Select Case Screen.PreviousControl.FieldType
Case dbText Or dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End Sub

Thank you,
Robin


Something like this might work:

With Screen.PreviousControl
If .ControlType = acTextBox Then
If Len(.ControlSource) 0 _
And Left(.ControlSource, 1) "=" _
Then
Select Case Me.Recordset.Fields(.ControlSource).Type
Case dbText, dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End If
End If
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Robin" 写入消�
...
I have the following code trying to only allow the spell checking to text
or
memo fields. But since the Screen.PreviousControl does not contain the
Fieldtype property this code fails. Any ideas how to test for the field
type
property to eliminate error indicating object not supported?

Private Sub Command19_Click()
On Error Resume Next
Select Case Screen.PreviousControl.FieldType
Case dbText Or dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"
End Select
End Sub

Thank you,
Robin

æ±—ï¼�ï¼�用ä¸*æ–‡å�–ï¼�ï¼�ï¼�

"Robin" wrote in message
...
Worked perfectly, thank you for your help.

So if I understand what's going on he Because I used a command button
(which has no controlsource) I had to use the screen.previouscontrol to
refer
to the text/memo control.

Pretty much. When you click the command button, it becomes the current or
active control, so you have to go to Screen.PreviousControl to get access
with the one you want to work with.

The controlsource tests are simply to assure there
is text in the field to test.

Those lines of code aren't testing whether there's actually any text in the
control; they're testing first whether this is a bound control -- that is,
it's neither unbound (empty controlsource) nor a calculated field
(controlsource expression beginning with "="). If it's not a bound control,
we're going to ignore it. That seemed to me to be implied by your saying
you only want to check text or memo fields. Since field types only exist in
a table (or a query of a table), we need to be able to look at the field to
which the control is bound.

Once the With screen.previouscontrol loop was
initiated I can then refer to the recordset using the Me.... references as
I
would have intially expected.

Well, that's not exactly accurate. "Me.Recordset" can be referred to any
time on a bound form. The With block just serves to shortcut multiple
references to Screen.PreviousControl. This line:

Select Case Me.Recordset.Fields(.ControlSource).Type

.... is saying, "Examine the Type property of the field in this form's
recordset that whose name is the ControlSource of Screen.PreviousControl."

Note that I am assuming that Screen.PreviousControl is a control on the same
form as the command button. That isn't necessarily a safe assumption. It
would be possible to code around that restriction, following the Parent
chain up from Screen.PreviousControl to get to the form that contains it.
Hmm ... now that I think about it, why don't we rewrite this now to
eliminate that problem. Here's a new function to put in a standard module:

'----- start of utility function code -----
Function fncBoundFieldType(ctl As Access.Control) As Integer

' Returns the data type of the field to which the
' argument ctl is bound. If ctl is not bound,
' the function returns 0.

Dim objParent As Object

With ctl

If Len(.ControlSource) 0 _
And Left(.ControlSource, 1) "=" _
Then

' Get a reference to the form containing this control.
Set objParent = .Parent
Do Until TypeOf objParent Is Access.Form
Set objParent = objParent.Parent
Loop

fncBoundFieldType = _
objParent.Recordset.Fields(.ControlSource).Type

Set objParent = Nothing

End If

End With

End Function
'----- end of utility function code -----


Now, with that function available for use, your command button's code
becomes much simpler:

'----- start of revised button code -----
Private Sub Command19_Click()

Select Case fncBoundFieldType(Screen.PreviousControl)

Case dbText, dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"

End Select

End Sub
'----- end of revised button code -----

I like that better.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

For clarification of my initial intent. I'm trying to adapt the new open
source spell checker provided by pcesoft
http://www.pcesoft.com/Access-Spell-...urce-Code.html to my
project. I am attempting to expand the capabilities to check the contents of
the "current field" the user chooses rather than having to create a button
for each field, as the their supplied example would require. The function
"we" (seems like way too much credit to me for asking a question, but thank
you) wrote has accomplished this.

The fact that I received an error message when trying to spell check
comboboxes indicating that the object wasn't supported was what caused me to
limit my endeavors to the Text/Memo fields. But it appears that the code you
provided should work with the comboboxes as well, since they are not
calculated and possess as control source.

So I will try to see if I can modify the spell check code to accomodate the
comboboxes as well. I think it is the initial test for .TextFormat property,
which takes into account the new RTF capabilities in Access 2007, in the
code that has created the error. Now that I know better how to refer to the
properties I'll try to put a switch in there to allow other fields perhaps to
succeed through the process. There may be other hurdles to jump but the
information you provided certainly cleared many incorrect lines of thinking
for me.

I would prefer that the spell checker enumerate through the form and check
all of the eligible fields but at the moment that is beyond my capabilities.
But I'll keep working at it

Thank you,
Robin


"Dirk Goldgar" wrote:

"Robin" wrote in message
...
Worked perfectly, thank you for your help.

So if I understand what's going on he Because I used a command button
(which has no controlsource) I had to use the screen.previouscontrol to
refer
to the text/memo control.

Pretty much. When you click the command button, it becomes the current or
active control, so you have to go to Screen.PreviousControl to get access
with the one you want to work with.

The controlsource tests are simply to assure there
is text in the field to test.

Those lines of code aren't testing whether there's actually any text in the
control; they're testing first whether this is a bound control -- that is,
it's neither unbound (empty controlsource) nor a calculated field
(controlsource expression beginning with "="). If it's not a bound control,
we're going to ignore it. That seemed to me to be implied by your saying
you only want to check text or memo fields. Since field types only exist in
a table (or a query of a table), we need to be able to look at the field to
which the control is bound.

Once the With screen.previouscontrol loop was
initiated I can then refer to the recordset using the Me.... references as
I
would have intially expected.

Well, that's not exactly accurate. "Me.Recordset" can be referred to any
time on a bound form. The With block just serves to shortcut multiple
references to Screen.PreviousControl. This line:

Select Case Me.Recordset.Fields(.ControlSource).Type

.... is saying, "Examine the Type property of the field in this form's
recordset that whose name is the ControlSource of Screen.PreviousControl."

Note that I am assuming that Screen.PreviousControl is a control on the same
form as the command button. That isn't necessarily a safe assumption. It
would be possible to code around that restriction, following the Parent
chain up from Screen.PreviousControl to get to the form that contains it.
Hmm ... now that I think about it, why don't we rewrite this now to
eliminate that problem. Here's a new function to put in a standard module:

'----- start of utility function code -----
Function fncBoundFieldType(ctl As Access.Control) As Integer

' Returns the data type of the field to which the
' argument ctl is bound. If ctl is not bound,
' the function returns 0.

Dim objParent As Object

With ctl

If Len(.ControlSource) 0 _
And Left(.ControlSource, 1) "=" _
Then

' Get a reference to the form containing this control.
Set objParent = .Parent
Do Until TypeOf objParent Is Access.Form
Set objParent = objParent.Parent
Loop

fncBoundFieldType = _
objParent.Recordset.Fields(.ControlSource).Type

Set objParent = Nothing

End If

End With

End Function
'----- end of utility function code -----


Now, with that function available for use, your command button's code
becomes much simpler:

'----- start of revised button code -----
Private Sub Command19_Click()

Select Case fncBoundFieldType(Screen.PreviousControl)

Case dbText, dbMemo
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "pupDictionary"

End Select

End Sub
'----- end of revised button code -----

I like that better.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)