Error Attempting to set Default Value of Table Field through VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charles in Iraq - 16 Sep 2006 11:46 GMT

Greetings.

I manage an event database for foot races.

I have written VBA code to allow users to set the default
length of the race. But everytime I attempt to execute the
code, I get the following error:

Run-time error '3420'
Object invalid or no longer set.

Here is the VBA code that executes:

Private Sub Length_AfterUpdate()
Dim RunTab As DAO.TableDef
Dim LgthFld As Field
Set RunTab = CurrentDb.TableDefs("runners")
Set LgthFld = RunTab("Length") <-- ERROR OCCURS HERE
LgthFld.DefaultValue = Me.Length
End Sub

Can somebody tell me why I'm getting this error and what
I can do to fix it?

Respectfully,

Charles

Reply to this Message

Douglas J. Steele - 16 Sep 2006 12:55 GMT

It's because you're using CurrentDb when defining RunTab, rather than a
specific instance of the database. You could solve this by

Private Sub Length_AfterUpdate()
Dim RunDb As DAO.Database
Dim RunTab As DAO.TableDef
Dim LgthFld As Field
Set RunDb = CurrentDb
Set RunTab = RunDb.TableDefs("runners")
Set LgthFld = RunTab("Length")
LgthFld.DefaultValue = Me.Length
End Sub

However, there are still another possible error that may occur if you're
using Access 2000 or newer, and still have the default reference to ADO set,
your declaration of LgthFld is going to result in an ADODB Field, not a DAO
field as you require. That's easy to fix: simply use Dim LgthFld As
DAO.Field.

Realistically, though, you don't need that elaborate code. The following
will work just as well:

Private Sub Length_AfterUpdate()
CurrentDb.TableDefs("runners").Fields("Length").DefaultValue = Me.Length
End Sub

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Greetings.
>
[quoted text clipped - 23 lines]
>
> Charles

Reply to this Message

Charles in Iraq - 16 Sep 2006 17:24 GMT

Doug:

I tried your suggestion and now see the following error:

Run-timer error '3320':
Syntax error (missing operator) in table level validation expression

Do you have any ideas what could cause this error? The Length
field in table runners is a simple text field. The Me.Length field
is text too.

> Realistically, though, you don't need that elaborate code. The following
> will work just as well:
>
> Private Sub Length_AfterUpdate()
> CurrentDb.TableDefs("runners").Fields("Length").DefaultValue = Me.Length
> End Sub

Reply to this Message

Douglas J. Steele - 16 Sep 2006 19:13 GMT

You didn't mention that Length was a text field.

Try putting quotes around it:

CurrentDb.TableDefs("runners").Fields("Length").DefaultValue = Chr$(34) &
Me.Length & Chr$(34)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug:
>
[quoted text clipped - 14 lines]
>> Me.Length
>> End Sub

Reply to this Message

Charles in Iraq - 17 Sep 2006 05:43 GMT

Right. This worked. Thanks for your help.

> You didn't mention that Length was a text field.
>
[quoted text clipped - 21 lines]
> >> Me.Length
> >> End Sub

Reply to this Message