Object doesn't support this property or method

[Guest]

Run-time error '438'

Here's my code (memo field):

Private Sub Comments_Exit(Cancel As Integer)
If Check67 = -1 Then
DoCmd.GoToRecord , , acNewRec
Me!Broker.SetFocus
Else
End If
End Sub

The new record is working.
The error arrow is pointing to the Me!Broker.SetFocus line.

I've tried a few variations, including brackets, but I can't get this to
work. What am I missing?

tia

 

[Guest]

Is that textbox locked? Are you sure the name is correct. When you type it
in MSA should automatically dropdown with the options that are available
assuming that you are using Me.Broker.SetFocus.

 

[Guest]

I am receiving this same error and my field is not locked either. Why is
this happening? When I use this line of code "me.[submitted by:]." in the
drop down all I see is "value" ....."setfocus" is not an option so what do I
need to do in order to have that as an option?

thank you,
MN

 

[Dirk Goldgar]

I am receiving this same error and my field is not locked either.
Why is this happening? When I use this line of code "me.[submitted
by:]." in the drop down all I see is "value" ....."setfocus" is not
an option so what do I need to do in order to have that as an option?

For some reason, I don't see the original thread you're replying to.

Do you actually have a control on your form named "submitted by:"? Are
you sure that it's not just a field in the form's recordsource, without
being represented by a control on the form?

 

[Guest]

"Submitted By:" is a text box on my form....

I am receiving this same error and my field is not locked either.
Why is this happening? When I use this line of code "me.[submitted
by:]." in the drop down all I see is "value" ....."setfocus" is not
an option so what do I need to do in order to have that as an option?

For some reason, I don't see the original thread you're replying to.

Do you actually have a control on your form named "submitted by:"? Are
you sure that it's not just a field in the form's recordsource, without
being represented by a control on the form?

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

"Submitted By:" is a text box on my form....

I can't reproduce your problem. Using Access 2002, I created a table
with a field named "Submitted by:", used the autoform wizard to create a
form from the table (which created a text box named "Submitted by:"
bound to that field), and added a button with code that manipulated
various properties of the button, referring to it using the syntax

Me.[Submitted by:]

It all worked as I'd expect.

Questions:

1. What version of Access are you using?

2. Are you absolutely sure that the text box is *named* "Submitted By:"?
Are you sure that's not just its caption?

3. Do you have the Name AutoCorrect option turned on or off? (Tools ->
Options... -> General tab, "Perform name AutoCorrect"). If it's on,
what happens if you turn it off?

Observations:

"Submitted By:" is a lousy name for a field or control, as it contains
non-standard characters. I recommend that you rename both the field and
the text box to "SubmittedBy", and avoid all this hassle.

 

[Guest]

First off,

I agree with you about the field name....as I did not create that. I am
merely maintaining this application that was created by another party.

Just for a dummy check, I checked the fieldname and the fieldname was NOT
submitted by: it was "Text342". I was looking at the control source. Thank
you VERY MUCH for your help!!!!

My next question is.....this code is on the "onclose" event of the form. I
have it that if this field is null, I don't want them to close the form but
set focus to that field. How do I prevent the form from closing?

"Submitted By:" is a text box on my form....

I can't reproduce your problem. Using Access 2002, I created a table
with a field named "Submitted by:", used the autoform wizard to create a
form from the table (which created a text box named "Submitted by:"
bound to that field), and added a button with code that manipulated
various properties of the button, referring to it using the syntax

Me.[Submitted by:]

It all worked as I'd expect.

Questions:

1. What version of Access are you using?

2. Are you absolutely sure that the text box is *named* "Submitted By:"?
Are you sure that's not just its caption?

3. Do you have the Name AutoCorrect option turned on or off? (Tools ->
Options... -> General tab, "Perform name AutoCorrect"). If it's on,
what happens if you turn it off?

Observations:

"Submitted By:" is a lousy name for a field or control, as it contains
non-standard characters. I recommend that you rename both the field and
the text box to "SubmittedBy", and avoid all this hassle.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

First off,

I agree with you about the field name....as I did not create that. I
am merely maintaining this application that was created by another
party.

I know what that's like.

Just for a dummy check, I checked the fieldname and the fieldname was
NOT submitted by: it was "Text342". I was looking at the control
source. Thank you VERY MUCH for your help!!!!

You're welcome.

My next question is.....this code is on the "onclose" event of the
form. I have it that if this field is null, I don't want them to
close the form but set focus to that field. How do I prevent the
form from closing?

The Close event is too late -- it can't be cancelled, and besides, at
that point the values of the bound controls will have been lost (I
think). If you want to keep the form from closing, you have to use the
Unload event. The Unload event can be cancelled by setting the Cancel
argument of its event procedure to True.

But ... if this is a bound form, then by the time you get to the Unload
event, the record will already have been saved. If you want to keep the
record from even being saved with that field empty, you probably ought
to use the BeforeUpdate event to check for it. That event, too, can be
cancelled, and by cancelling it you prevent the record from being saved.

 

[Guest]

I guess what I want to do is prevent the user from closing the form until
that field is filled out. What would be my best option? Using the form's
BeforeUpdate event to check for the null value? That does work, however,
only if I have the required property set to true for that field in its table
and when that is the case , i get that annoying message talking about its
null value which would mean NOTHING to the user and just confuse them.

 

[Dirk Goldgar]

I guess what I want to do is prevent the user from closing the form
until that field is filled out. What would be my best option? Using
the form's BeforeUpdate event to check for the null value? That does
work, however, only if I have the required property set to true for
that field in its table and when that is the case , i get that
annoying message talking about its null value which would mean
NOTHING to the user and just confuse them.

There seems to be some misunderstanding. You can certainly use the
form's BeforeUpdate event to enforce the "non-Null-ness" of a field,
even if the field's Required propery is set to False in the table
design. For example,

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me!txtSubmittedBy & vbNullString) = 0 Then
Cancel = True
MsgBox "You must enter something for 'Submitted By:'."
Me!txtSubmittedBy.SetFocus
End If

End Sub

If this is a serious requirement, I would probably *also* set the
field's Required property to True, just to make double-sure. But in
case the user takes the trouble to enter something in the field and the
delete it, I'd also have this procedure for the *control's* BeforeUpdate
event:

Private Sub txtSubmittedBy_BeforeUpdate(Cancel As Integer)

If IsNull(Me!txtSubmittedBy) Then
MsgBox "This field is required."
Cancel = True
End If

End Sub

Between them, those two event procedures will keep the user from ever
getting the system default message for a Null value in a required field.

 

[Guest]

Dirk,

Thank you for the advice. I have put that code in my form and the only
problem i am having with it is that the first field on the form is a drop
down. I select a number from the dropdown and after that value is selected
there is a "me.refresh" statement in the afterupdate event of that field
because it fills in other fields automatically based on that selection.
After selecting the value, I receive the message "You must enter something
for 'Submitted By:'." from your code down below and then i get an error
message saying "no current record" so the fields that are to be automatically
filled in based on the selection in that drop down do not appear.

Any ideas?

Thanks again!

 

[Dirk Goldgar]

Dirk,

Thank you for the advice. I have put that code in my form and the
only problem i am having with it is that the first field on the form
is a drop down. I select a number from the dropdown and after that
value is selected there is a "me.refresh" statement in the
afterupdate event of that field because it fills in other fields
automatically based on that selection. After selecting the value, I
receive the message "You must enter something for 'Submitted By:'."
from your code down below and then i get an error message saying "no
current record" so the fields that are to be automatically filled in
based on the selection in that drop down do not appear.

Hmm, we're getting in deeper and deeper. Executing Me.Refresh forces
the current record to be saved, so naturally the form's BeforeUpdate
event procedure fires, causing the problem. If you really have to do
that Refresh there, you're going to have to remove the Null test from
the BeforeUpdate event, set the field's Required property back to No,
and put the Null test into the Unload event instead. But that will
allow -- force, in fact -- a record to be stored with a Null value in
that field, which is what I thought you wanted to avoid.

So the question is, do you really have to Refresh the form in that combo
box's AfterUdate event? Can you explain to me in more detail why? Are
you using code in that event to do anything besided saving the record?
What causes these "other fields" to fill in? If your form is based on
an autolookup query, I wouldn't expect you to have to save the record to
get the autolookup to happen. Have you tried it without the Refresh
statement? Is it enough to do a Recalc instead?

What's the recordsource of the form, and what are the fields that are
being looked up when you refresh the record?

 

[Guest]

Dirk,

The me.refresh statement was put in there on the afterupdate event of the
drop down box of the first field, Business Unit Number. After a business unit
number is selected, a refresh is done so all the location information
(address, city, state) information appears in a subform. Can this be done
without using a refresh statement?

 

[Dirk Goldgar]

Dirk,

The me.refresh statement was put in there on the afterupdate event of
the drop down box of the first field, Business Unit Number. After a
business unit number is selected, a refresh is done so all the
location information (address, city, state) information appears in a
subform. Can this be done without using a refresh statement?

From just the information given, I don't see why not. If [Business Unit
Number] is the Link Master field of the subform, any time you change
[Business Unit Number], the subform should change to reflect it
immediately, whether you save the record or not.

 

[Guest]

Dirk,

Well, unfortunately that information does not appear after a business unit
is selected without the me.refresh code in there. So, instead I put the
"me.recalc" in the afterupdate event of that business unit field and that now
shows all that info in the subform....and does not therefore save the
record!! I get a message however, after selecting a business unit and then
trying to close the form without entering anything into "submitted by" saying
that it can't find the object to close which again, means nothign to the user
-- but the fact is is that it works -- thanks to your help!!!

Thanks very much for sticking with me!
MN

Dirk,

The me.refresh statement was put in there on the afterupdate event of
the drop down box of the first field, Business Unit Number. After a
business unit number is selected, a refresh is done so all the
location information (address, city, state) information appears in a
subform. Can this be done without using a refresh statement?

From just the information given, I don't see why not. If [Business Unit
Number] is the Link Master field of the subform, any time you change
[Business Unit Number], the subform should change to reflect it
immediately, whether you save the record or not.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Well, unfortunately that information does not appear after a business
unit is selected without the me.refresh code in there.

That's odd. There's something going on with that form that you haven't
told me.

So, instead I
put the "me.recalc" in the afterupdate event of that business unit
field and that now shows all that info in the subform....and does not
therefore save the record!!

I suspect that the combo box may not actually be the Link Master Field
of the subform. I dunno.

I get a message however, after selecting
a business unit and then trying to close the form without entering
anything into "submitted by" saying that it can't find the object to
close which again, means nothign to the user -- but the fact is is
that it works -- thanks to your help!!!

You're welcome, but ... that sounds like an odd and confusing message,
and I don't think you need to be getting it. Would you care to post the
exact text of the message, and the error number, if it displays one?

 

[Guest]

No worries -- it was just an error message in the onclose event of the "close
form" button on the form. I just commented it out in the code.

Thank you very much again for your assistance!!

MN