Property not found

[Mark A. Sam]

Hello,

In the following BeforeUpdate event of a textbox, I am getting a Property
Not Found error without a number.

Private Sub TripStartLoc_BeforeUpdate(Cancel As Integer)
On Error Resume Next

If InStr(Me.ActiveControl, ",") = 0 Then
MsgBox "The format for this field is City,St using state code like
Dunkirk,NY or Miami,Fl"
Cancel = True
[TripStartLoc].Undo
End If


End Sub


I can run this from the AfterUpdate event, but just curious about the error.
I am using Access2007.

Thank you and God Bless,

Mark A. Sam

 

[Douglas J. Steele]

That should be Screen.ActiveControl, not Me.ActiveControl

 

[Dirk Goldgar]

That should be Screen.ActiveControl, not Me.ActiveControl

A form does have an ActiveControl property, and it may be different from
Screen.ActiveControl. It will be the same as Screen.ActiveControl if the
form has the focus.

 

[Dirk Goldgar]

Hello,

In the following BeforeUpdate event of a textbox, I am getting a Property
Not Found error without a number.

Private Sub TripStartLoc_BeforeUpdate(Cancel As Integer)
On Error Resume Next

If InStr(Me.ActiveControl, ",") = 0 Then
MsgBox "The format for this field is City,St using state code like
Dunkirk,NY or Miami,Fl"
Cancel = True
[TripStartLoc].Undo
End If


End Sub


I can run this from the AfterUpdate event, but just curious about the
error. I am using Access2007.

I don't know exactly why you're using ActiveControl. since you know that
TripStartLoc has the focus.

I don't see why you're getting *any* error message, since you have "On Error
Resume Next" in effect.

Is TripStartLoc a bound control? If not, you won't be able to undo it.

 

[Mark A. Sam]

That should be Screen.ActiveControl, not Me.ActiveControl

Doug,

That didn't clear it up, besides, the the code doesn't crash on that line.
If completes the procedure then the message comes up. If I step though it
the code, I get a No Current Record message instead.

 

[Mark A. Sam]

I don't know exactly why you're using ActiveControl. since you know that
TripStartLoc has the focus.

I don't understand what you mean. Do you mean in lieu of the field name?

I don't see why you're getting *any* error message, since you have "On
Error Resume Next" in effect.

I don't either. I put On Error Resume Next to try to suppress the error.

Is TripStartLoc a bound control? If not, you won't be able to undo it.

It is a bound control. I use that method, becuase on the BeforeUpdate event
the other undo methods don't work.

I want to use the BeforeUpdate event becuase I want the focus to stay on the
control rather then moving to the next textbox.

God Bless,

Mark

 

[Dirk Goldgar]

I don't understand what you mean. Do you mean in lieu of the field name?

Yes, that's what I meant. I would just use the name of the control in
question -- i.e., TripStartLoc -- since I know that's the control I'm
concerned with. This has nothing to do with the error you're getting,
though.

I don't either. I put On Error Resume Next to try to suppress the error.

I don't think this block of code is where your error is coming from.
Suppose you set a breakpoint here and raise the event by updating the
control, then step through the code from there. What happens?

 

[Mark A. Sam]

Yes, that's what I meant. I would just use the name of the control in
question -- i.e., TripStartLoc -- since I know that's the control I'm
concerned with. This has nothing to do with the error you're getting,
though.

I usually use the field name. I was just lazy and found it easier to type
Me.A and let Access fill the rest.

I don't think this block of code is where your error is coming from.
Suppose you set a breakpoint here and raise the event by updating the
control, then step through the code from there. What happens?

I did that and got a different message, 'No Current Record'.

 

[Dirk Goldgar]

I did that and got a different message, 'No Current Record'.

But *where* -- on what line of code -- did you get that message? We're
trying to find out where the problem is; from that, we can probably figure
out *what* it is.

 

[Mark A. Sam]

Dirk,

I was developing with Access2007, but realized it is an .mdb file so I
opened it with Access2002 and the procedure works fine. The problem has to
do with Access2007. I'll see if there are any new updates.

Thanks for your help and God Bless,

Mark

 

[Dirk Goldgar]

Dirk,

I was developing with Access2007, but realized it is an .mdb file so I
opened it with Access2002 and the procedure works fine. The problem has
to do with Access2007. I'll see if there are any new updates.

Bear in mind that in its default settings, Access 2007 won't even let any
VBA code run. You have to explicitly trust the database or else put it in a
trusted location. So it's possible that your code is not even runnning.
Have you checked that?

 

[Mark A. Sam]

Bear in mind that in its default settings, Access 2007 won't even let any
VBA code run. You have to explicitly trust the database or else put it in
a trusted location. So it's possible that your code is not even runnning.
Have you checked that?

My database is in a trusted location and the code is running. If it wasn't,
how would I have gotten the error?

 

[Dirk Goldgar]

My database is in a trusted location and the code is running. If it
wasn't, how would I have gotten the error?

Access's internal code can raise errors, as can macros. However, if you are
sure the code is actually running, I'm happy to take your word for it. I'm
just trying to cover all the possibilities, and one of them that sometimes
arises when databases are converted to Access 2007 format is the disabling
of VBA code.

 

[Mark A. Sam]

The code is running. It is doing what I want it to do, however, I am not
clear on how errors could be raised if it is not running. How could I have
stepped through the code if it wasn't running?

 

[Dirk Goldgar]

The code is running. It is doing what I want it to do, however, I am not
clear on how errors could be raised if it is not running. How could I
have stepped through the code if it wasn't running?

You couldn't have. But since you never said what line of code raised the
error, I wasn't sure you had actually stepped through the code. We might
have been misunderstanding each other.

 

[Mark A. Sam]

You couldn't have. But since you never said what line of code raised the
error, I wasn't sure you had actually stepped through the code. We might
have been misunderstanding each other.

I think so. Just for anyone interested, there was no line of code that
bombed. The error came as the module was exited, either by an Exit Sub or
at the End Sub. This is an A2007 problem. I can use A2002, not from my
Desktop but form a Server, which is inconvenient, so I am interested in a
solution anyone may have found.

 

[Dirk Goldgar]

I think so. Just for anyone interested, there was no line of code that
bombed. The error came as the module was exited, either by an Exit Sub or
at the End Sub. This is an A2007 problem. I can use A2002, not from my
Desktop but form a Server, which is inconvenient, so I am interested in a
solution anyone may have found.

So you get the error as you exit the procedure you posted, but before any
other line of code is executed? It does sound like some sort of corruption.
I assume you've done a compact/repair, to no effect. Have you tried
decompiling the database? And if that doesn't work, how about if you create
a brand new database (in the .accdb format) and import all the objects from
this one into it?

 

[Mark A. Sam]

So you get the error as you exit the procedure you posted, but before any
other line of code is executed?

It was after all lines had been executed. I had an Exit Sub statement and
it bombed after that. When I removed the Exit Sub, the error came after the
End Sub. I really think this is an A2007 issue.

I was using two Datasheet subforms, one to enter data and another to display
a diagram which was constructed in code. I turned the Diagram Subform into
a continuous form. After that I got an error about a string expression. I
don't recall the wording, then an overflow message. I opened the DB in
A2002 and didn't get the string expression message, but the Overflow. I
stepped through the code and noticed that recordset fields weren't receiving
assigned values, like

rst![somefield] = [somevalue]

When I stepped past the line and checked the value of rst![somefield] the
value was null even though [somevalue] had a numeric value. I ran the code
again, then it was fine. I'm using A2007 becuase I don't have A2002
installed on my local machine and kind of like 2007. I'm even getting use
to the ribbon.

I can't use an .accdb format becuase mine and another machine are the only
computers with 2007. All others have 2002 and 2003. So I need to keep it
at that level.

Thanks for your help Dirk. I always appreciate it.

God Bless,

Mark

It does sound like some sort of corruption.