Error Attempting to set Default Value of Table Field through VBA

[Charles in Iraq]

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

 

[Douglas J. Steele]

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

 

[Charles in Iraq]

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.

 

[Douglas J. Steele]

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)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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.

 

[Charles in Iraq]

Right. This worked. Thanks for your help.

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)